undo retention的思考(一)

最近有个网友咨询我一个问题,是关于undo_retention的,对于这个参数没有过多关注,只是知道需要设置undo_retention搭配使用undotablespace retention guarantee

通过邮件的操作记录可以看出这个网友还是很严谨的,每一个步骤都很详细的列了出来,这位网友在测试11.2.0.1.0的环境中发现undo retention没有像期望值那样来达到预期的效果。

自己在本地测试了多次,虽然结果还是不够理想,不过基本思路已经有了,继续努力。

我所在的环境是11.2.0.4.0,但是问题的效果是一样的,所以可以断定这个现象在新版本的库中也应该存在。

SQL> select * from v$version where rownum=1;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

查看undo retention的默认值为900,即是900秒

SQL> show parameter undo_retention

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

undo_retention integer 900

为了测试期间,设置为60秒

SQL> alter system set undo_retention=60;

System altered.

然后使用一个测试用户来创建临时表。

SQL> conn test/test

Connected.

SQL> create table test_tab as select *from all_objects;

Table created.

SQL> select *from cat;

TABLE_NAME TABLE_TYPE

------------------------------ -----------

TEST_TAB TABLE

基本的测试数据就准备完了,开始测试。

SQL> conn test/test

Connected.

首先更新一行。

SQL> update test_tab set object_id=2 where rownum=1;

1 row updated.

然后在事务视图中查看

SQL> select xidusn,status from v$transaction where addr=(select taddr from v$session where sid=(select sid from v$mystat where rownum=1));

XIDUSN STATUS

---------- ----------------

3 ACTIVE

找到对应的回滚段。

SQL> select name from v$rollname where usn=3;
NAME
------------------------------
_SYSSMU3_1723003836$
SQL> set linesize 200
select  owner,segment_name,tablespace_name,extent_id,file_id,block_id,blocks,status  from dba_undo_extents where segment_name='_SYSSMU3_1723003836$';
OWN SEGMENT_NAME                   TABLESPACE_NAME                 EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS STATUS
--- ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------
SYS _SYSSMU3_1723003836$           UNDOTBS1                                0          3        160          8 EXPIRED
SYS _SYSSMU3_1723003836$           UNDOTBS1                                1          3        264          8 EXPIRED
SYS _SYSSMU3_1723003836$           UNDOTBS1                                2          3       3200        128 ACTIVE

这个时候存在active的回滚信息,一般状态分为三种。EXPIRED,UNEXPIRED,ACTIVE。ACTIVE表示目前活跃的事务回滚信 息,UNEXPIRED表示虽然事务已经结束但回滚信息的保留时间仍未超过实例参数UNDO_RETENTION所设定的值,EXPIRED表示回滚信息 保留时间已超过UNDO_RETENTION所设定的值。

然后短暂等待后,提交。

SQL> commit;

Commit complete.

提交后查看会存在EXPIRED和UNEXPIRED的回滚信息

SQL> select  owner,segment_name,tablespace_name,extent_id,file_id,block_id,blocks,status  from dba_undo_extents where segment_name='_SYSSMU3_1723003836$';
OWN SEGMENT_NAME                   TABLESPACE_NAME                 EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS STATUS
--- ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------
SYS _SYSSMU3_1723003836$           UNDOTBS1                                0          3        160          8 EXPIRED
SYS _SYSSMU3_1723003836$           UNDOTBS1                                1          3        264          8 EXPIRED
SYS _SYSSMU3_1723003836$           UNDOTBS1                                2          3       3200        128 UNEXPIRED

这个时候如果在同一个session中查看原来的回滚段就无从查起了,因为事务已经终结。

SQL> select xidusn,status from v$transaction where addr=(select taddr from v$session where sid=(select sid from v$mystat where rownum=1));

no rows selected

那么我们还是根据之前的回滚信息来看。

SQL> select  owner,segment_name,tablespace_name,extent_id,file_id,block_id,blocks,status  from dba_undo_extents where segment_name='_SYSSMU3_1723003836$';
OWN SEGMENT_NAME                   TABLESPACE_NAME                 EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS STATUS
--- ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------
SYS _SYSSMU3_1723003836$           UNDOTBS1                                0          3        160          8 EXPIRED
SYS _SYSSMU3_1723003836$           UNDOTBS1                                1          3        264          8 EXPIRED
SYS _SYSSMU3_1723003836$           UNDOTBS1                                2          3       3200        128 UNEXPIRED

如果再次更新,等待超过60秒,效果也还是一样,还是存在UNEXPIRED的回滚信息。

OWN SEGMENT_NAME                   TABLESPACE_NAME                 EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS STATUS
--- ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------
SYS _SYSSMU3_1723003836$           UNDOTBS1                                0          3        160          8 EXPIRED
SYS _SYSSMU3_1723003836$           UNDOTBS1                                1          3        264          8 EXPIRED
SYS _SYSSMU3_1723003836$           UNDOTBS1                                2          3       3200        128 UNEXPIRED

网友认为这个时候,如果按照undo_retention的设置思想,应该为EXPIRED

这个时候有个视图可以参考 v$undostat

SQL> select max(maxquerylen),max(tuned_undoretention) from v$undostat;

MAX(MAXQUERYLEN) MAX(TUNED_UNDORETENTION)

---------------- ------------------------

1904 2804

可以看到数据库根据算法计算出来的最大TUNED_UNDORETENTION要比最大MAXQUERYLEN大不少,undo表空间中的数据经过TUNED_UNDORETENTION之后才会由UNEXPIRED变成 EXPIRED。TUNED_UNDORETENTION的优化,mos上也有几篇文章可以参考。Undo Remains Unexpired When Using Non-autoextensible Datafiles For The Undo Tablespace (文档 ID 1112431.1)

当然自己对于这个问题也是心生疑惑,发现两篇相关的文章。

Master Note: High Undo Space Usage (Doc ID 1578639.1)

Undo Remains Unexpired When Using Non-autoextensible Datafiles For The Undo Tablespace (Doc ID 1112431.1)

对于undo retention其实涉及的场景还是相对比较复杂的,一方面和undo数据文件的自动扩展与否有关,而且数据库级有一个设置为retention guarantee,同时还有一个隐含参数_undo_autotune

select a.ksppinm name, b.ksppstvl value, a.ksppdesc description

from x$ksppi a, x$ksppcv b

where a.indx = b.indx and a.ksppinm like '_undo_autotune'

这个参数是默认开启了undo的优化的。

NAME                 VALUE                DESCRIPTION
-------------------- -------------------- ----------------------------------------
_undo_autotune       TRUE                 enable auto tuning of undo_retention

当然也可以选择关掉这个部分,实际中还是需要考量。

SQL> alter system set "_undo_autotune"=false;

System altered.

不过修改后发现数据库计算出的最大TUNED_UNDORETENTION和最大MAXQUERYLEN还是没有任何变化。

SQL>  select max(maxquerylen),max(tuned_undoretention) from v$undostat;
MAX(MAXQUERYLEN) MAX(TUNED_UNDORETENTION)
---------------- ------------------------
            1904                     2804

对于回滚信息,还是显示为UNEXPIRED

SQL> select  owner,segment_name,tablespace_name,extent_id,file_id,block_id,blocks,status  from dba_undo_extents where segment_name='_SYSSMU3_1723003836$';
OWN SEGMENT_NAME                   TABLESPACE_NAME                 EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS STATUS
--- ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------
SYS _SYSSMU3_1723003836$           UNDOTBS1                                0          3        160          8 EXPIRED
SYS _SYSSMU3_1723003836$           UNDOTBS1                                1          3        264          8 EXPIRED
SYS _SYSSMU3_1723003836$           UNDOTBS1                                2          3       3200        128 UNEXPIRED

对于这个部分反复测试,有时候结果会有一些不同,所以后续继续跟进来看看哪里有细节的差别。

当然undo表空间设置为retention guarantee就会做相关的校验了。

SQL> alter tablespace undotbs1 retention guarantee;

Tablespace altered.

而这个部分的测试结果还是和之前没有差别。

SQL> select tablespace_name,contents,retention from dba_tablespaces where tablespace_name like '%UNDOTBS%';
TABLESPACE_NAME                CONTENTS  RETENTION
------------------------------ --------- -----------
UNDOTBS1                       UNDO      GUARANTEE

将undo表空间自动扩展属性取消

SQL> select file_name,autoextensible,bytes/1024/1024  size_MB,tablespace_name from dba_data_files where tablespace_name like  'UNDO%';
FILE_NAME                                          AUT    SIZE_MB TABLESPACE_NAME
-------------------------------------------------- --- ---------- ------------------------------
/DATA/app/oracle/oradata/test11g/undotbs01.dbf     YES        275 UNDOTBS1
SQL> alter database datafile '/DATA/app/oracle/oradata/test11g/undotbs01.dbf' autoextend off;
Database altered.

恢复为默认值

SQL> show parameter _undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_undo_autotune                       boolean     FALSE
SQL> alter system set "_undo_autotune"=true;
System altered.

自己尝试循环小批量删除数据,在guarantee设置下,很快会出现ORA-30036错误:

SQL> select count(*)from test_tab;
  COUNT(*)
----------
   21858560
 begin
       for i in 1..1000
      loop
        delete from test_tab where rownum<1001;
         commit;
      end loop;
     end;
 /
begin
*
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'
ORA-06512: at line 4

而在修改了undo表空间retention属性后,删除可以顺利完成

PL/SQL procedure successfully completed.

因为时间还是太紧,发现测试还是不够到位,所以没有得出最终很肯定,让人信服的结论,后续继续努力。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2015-10-19

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

迁移式升级的测试(三)(r10笔记第36天)

还是继续昨天的任务。 前面的内容可以参见:迁移式升级的一点思考 (r10笔记第27天)、迁移式升级的新方案测试 (r10笔记第30天)、迁移式升级的测试(二)(...

3545
来自专栏沃趣科技

MVCC原理探究及MySQL源码实现分析

目录预览 数据库多版本读场景 MVCC实现原理 1、通过DB_ROLL_PT 回溯查找数据历史版本 2、通过read view判断行...

5808
来自专栏杨建荣的学习笔记

一条看似平常的报警邮件所做的分析(r8笔记第9天)

今天留意到一封报警邮件。内容如下: ZABBIX-监控系统: ------------------------------------ 报警内容: CPU u...

2554
来自专栏企鹅号快讯

带你认识一下mysql中数据库information

information_schema 大家在安装或使用MYSQL时,会发现除了自己安装的数据库以外,还有一个information_schema数据库。 inf...

2168
来自专栏杨建荣的学习笔记

system表空间不足的问题分析(二) (r8笔记第5天)

今天收到一条不太起眼的报警邮件,大体内容是某个表空间的空间有些紧张了。大体内容如下: Tablesapce: CMBI_SNZG_DATA: 92.2% [W...

3437
来自专栏企鹅号快讯

从商用到开源:15个维度,全面剖析DB2与MySQL数据库的差异

编辑手记 MySQL是目前最流行的开源数据库,由于其部署方便,运维简单,被广泛用于互联网的各个领域。随着整体IT架构的变更,传统的金融,电信业务,也逐渐走上从商...

2379
来自专栏沃趣科技

会话和锁信息查询视图 | 全方位认识 sys 系统库

在上一篇《等待事件统计视图 | 全方位认识 sys 系统库》中,我们介绍了sys 系统库中的等待事件统计视图,本期的内容先给大家介绍会话信息和锁等待信息查询视图...

1400
来自专栏顶级程序员

为什么忘记 commit 也会造成 select 查询的性能问题

作者:潇湘隐者 www.cnblogs.com/kerrycode/p/5836015.html 今天遇到一个很有意思的问题,一个开发人员反馈在测试服务器OR...

3068
来自专栏数据和云

从商用到开源:15个维度,全面剖析DB2与MySQL数据库的差异

编辑手记 MySQL是目前最流行的开源数据库,由于其部署方便,运维简单,被广泛用于互联网的各个领域。随着整体IT架构的变更,传统的金融,电信业务,也逐渐走上从商...

6177
来自专栏ASP.NET MVC5 后台权限管理系统

ASP.NET MVC5+EF6+EasyUI 后台管理系统(71)-微信公众平台开发-公众号管理

思维导图 下面我们来看一个思维导图,这样就可以更快了解所需要的功能: ? 上一节我们利用了一个简单的代码例子,完成了与微信公众号的对话(给公众号发一条信息...

4007

扫码关注云+社区

领取腾讯云代金券