ORA-01555和延迟块清除

01555, 00000, "snapshot too old: rollback segment number %s
with name \"%s\" too small"
// *Cause: rollback records needed by a reader for consistent read
//         are overwritten by other writers
// *Action: If in Automatic Undo Management mode, increase
//  undo_retention setting. Otherwise, use larger rollback segments
出现ORA-01555直接的原因是一致读所需的undo records被覆盖, 一致读失败有两种情况:
1. 数据块中ITL结构对应的undo block被覆盖, 无法构造一致读.
2. transaction table in undo segment header: 延迟块清除发生时, 如果Oracle需要回滚对应的transaction table, 找到事务确切提交的时间. 而且所需的undo record被覆盖, ORA-01555也会发生.
这篇文章不讨论一致读和延迟块清除的实现机制, 有兴趣的朋友可以参考Oracle Core第三章Transactions and Consistency. 这里只讨论因为延迟块清除而发生的ORA-01555.
下面是模拟延迟块清除触发ORA-01555的思路, 准备两个session.
1. session 1: 创建表T1, 插入500条记录分布在500个块上. 更新表T1的500条记录, 把500个脏块刷出缓存.
2. session 1: 提交. 这时commit cleanout会失败, Oracle把清理T1磁盘上500个”脏”块的任务留给下一个读这些块的session.
3. session 1: 设置查询的开始时间点: set transaction read only. 在这个事务结束之前, 显式地提交或者回滚, 接下来的查询以这个时间点为基准, 模拟现实中长时间运行的SQL.
4. session 2: 提交大量与表T1无关的事务. 发生transaction table consistent read rollbacks有两个原因. 首先, transaction table slot至少被覆盖一次, session 1之后访问T1″脏”块做延迟块清除时, 需要回滚transaction table. 其次, 如果我们产生足够多的undo, session 1回滚transaction table所需的undo block被覆盖, 无法对transaction table一致读, 就会触发ORA-01555.
我的环境中, undotbs1有26个segment, 每个transaction table有34个slot(10g版本是48个slot), 如果我们把每个slot覆盖50遍的需要执行44200(=26*34*50)个transaction. 在Automatic Undo Management模式下, 如果undo表空间用满, 最早commit的空间会被覆盖重新利用. undotbs1大小1G, 只要session 2产生超过1G的undo, 用以回滚transaction table的undo block就会被覆盖. 为了产生1G的undo, 执行44200个transaction, 一个transaction大约需要25K的undo. 保险起见确保每个transaction产生30k左右的undo.
5. session 1: 全表扫描T1, 因为延迟块清除, 我们可以观察到transaction tables consistent read rollbacks和transaction tables consistent reads – undo records applied.
环境: Oracle 11.2.0.2 Linux 32 bit, Automatic Undo Management(AUM), undotbs1表空间大小是1G, 包含26个回滚段.
sys@CS11GR2> @pd2 undo_management
NAME              VALUE  DESCRIPTION
----------------- ------ ---------------------------------------------------
undo_management   AUTO   instance runs in SMU mode if TRUE, else in RBU mode
sys@CS11GR2> @pd2 undo_tablespace
NAME             VALUE     DESCRIPTION
---------------- --------- -----------------------------
undo_tablespace  UNDOTBS1  use/switch undo tablespace
sys@CS11GR2> @df UNDOTBS1
TABLESPACE_NAME         TotalMB     UsedMB     FreeMB % Used
-------------------- ---------- ---------- ---------- ------
UNDOTBS1                   1024       1024          0   100%
sys@CS11GR2> select
  2          count(*)
  3  from
  4          dba_rollback_segs
  5  where
  6          tablespace_name = 'UNDOTBS1'
  7  /
  COUNT(*)
----------
        26
观察延迟块清除
1. session 1: 准备表T1和T2. 更新T1之后把500个块刷出缓存.
sid@CS11GR2> create table t1 (
  2          id              number,
  3          small_no        number(5,2),
  4          small_vc        varchar2(10),
  5          padding         varchar2(1000),
  6          constraint t1_pk primary key (id)
  7  )
  8  pctfree 90
  9  pctused 10
 10  ;
Table created.
sid@CS11GR2>
sid@CS11GR2> insert into t1
  2  select
  3          rownum,
  4          1+ trunc(rownum/10),
  5          lpad(rownum,10),
  6          rpad('x',1000)
  7  from
  8          all_objects
  9  where
 10          rownum <= 500
 11  ;
500 rows created.
sid@CS11GR2>
sid@CS11GR2> create table t2 (n1 number, v1 varchar2(1000));
Table created.
sid@CS11GR2> insert into t2 values (0, lpad(0,1000,'0'));
1 row created.
sid@CS11GR2> commit;
Commit complete.
-- gather statistics on T1 and T2
sid@CS11GR2> update
  2          /*+ index(t1) */
  3          t1
  4  set
  5          small_vc = small_vc + 1
  6  ;
500 rows updated.
sid@CS11GR2> alter system checkpoint;
System altered.
sid@CS11GR2> alter system flush buffer_cache;
System altered.
2. session 1: 提交. 虽然有500个块被修改了, Oracle尝试100次commit cleanout都失败之后选择放弃. 500个脏块的redo record在刷出缓存之前已经被写到redo log, 所以commit的时候只产生一条164 bytes的redo record.
sid@CS11GR2> commit;
Commit complete.
Name                                   Value
----                                   -----
commit cleanout failures: block lost     100
commit cleanouts                         100
redo entries                               1
redo size                                164
3. session 1: 记录当前的SCN, set transaction read only.
sid@CS11GR2> select
  2      sys.dbms_flashback.get_system_change_number post_commit_scn
  3  from
  4      dual
  5  ;
POST_COMMIT_SCN
---------------
     7780465327
sid@CS11GR2>
sid@CS11GR2> set transaction read only;
Transaction set.
4. session 2: 执行44200个小事务, 把每个transaction table slot覆盖50次. 只产生75M的redo, undo的大小是52M, 因为undotbs1有1G, 52M不足以覆盖之前的undo block, 没有ORA-01555的危险.
sid@CS11GR2> begin
  2          for i in 1..44200 loop
  3              update t2 set n1 = i;
  4              commit;
  5          end loop;
  6  end;
  7  /
PL/SQL procedure successfully completed.
Name                                       Value
----                                       -----
user commits                              44,200
redo entries                              88,407
redo size                             75,116,240
undo change vector size               52,863,176
5. session 1全表扫描T1, 调用ktugct(Kernel Transaction Undo Get Commit Time)500次, 做500次清除, 1次transaction tables consistent read rollbacks, 这次一致读需要apply 4,869条undo record, 本来我期望的undo record是1700(26*50), 这么大的差别是因为26个回滚段中, 只有10个状态是online的, 4869 接近 4420 (= 1700 * 2.6). 所有5,885(consistent gets)个逻辑读中, 花在undo上的逻辑读是5372(consistent gets – examination). 延迟块清除操作在实际中, 可能对性能有很大的影响, 见我之前一个例子: Transaction Tables Consistent Reads. 查询语句也可能产生redo, 延迟块清除产生500条redo record, 大小36k, 这500个数据块在缓存中被标记被dirty. 清除之后, 表T1的ora_rowscn取决于查询开始的SCN, 也就是第2步set transaction read only时的SCN.
sid@CS11GR2> select
  2      sys.dbms_flashback.get_system_change_number after_batch_scn
  3  from
  4      dual
  5  ;
AFTER_BATCH_SCN
---------------
     7780564259
sid@CS11GR2>
sid@CS11GR2> execute snap_my_stats.start_snap
PL/SQL procedure successfully completed.
sid@CS11GR2>
sid@CS11GR2> select
  2          /*+ full(t1) */
  3          count(*)
  4  from
  5          t1
  6  ;
  COUNT(*)
----------
       500
sid@CS11GR2> execute snap_my_stats.end_snap
Name                                                         Value
----                                                         -----
consistent gets                                              5,885
consistent gets - examination                                5,372
redo entries                                                   500
redo size                                                   36,044
transaction tables consistent reads - undo records applied   4,869
transaction tables consistent read rollbacks                     1
cleanouts only - consistent read gets                          500
immediate (CR) block cleanout applications                     500
commit txn count during cleanout                               500
cleanout - number of ktugct calls                              500
sid@CS11GR2> select
  2          ora_rowscn, count(*)
  3  from
  4          t1
  5  group by
  6          ora_rowscn
  7  order by
  8          count(*)
  9  ;
ORA_ROWSCN   COUNT(*)
---------- ----------
7780465326        500
触发ORA-01555
前面的3个步骤不变
4. session 2依然执行44200个事务, 每个事务执行update t2 set v1 = lowner(v1) 30次. 一共产生3.5G的redo, 其中undo change的大小是1.6G, 这样确保transaction table consistent read rollbacks所需的undo会被覆盖.
sid@CS11GR2> begin
  2          for i in 1..44200 loop
  3              for i in 1..30 loop
  4                  update t2 set v1 = lower(v1);
  5                  commit;
  6              end loop;
  7          end loop;
  8  end;
  9  /
PL/SQL procedure successfully completed.
Name                                 Value
----                                 -----
user commits                     1,326,000
redo entries                     2,691,795
redo size                    3,578,966,396
undo change vector size      1,668,229,604
5. session 1全表扫描T1, ORA-01555如期发生. ktugct只被调用一次, transaction table consistent read rollbacks没有改变, 说明对第一个读到的数据块做清除就失败了. session 1在apply了65,910条undo record之后, 发现回滚需要的下一个undo block已经被覆盖, 这时ORA-01555就发生了.
sid@CS11GR2> select
  2          /*+ full(t1) */
  3          count(*)
  4  from
  5          t1
  6  ;
    t1
    *
ERROR at line 5:
ORA-01555: snapshot too old: rollback segment number 10
with name "_SYSSMU10_3805322843$" too small
Name                                                        Value
----                                                        -----
consistent gets                                            65,926
consistent gets - examination                              65,915
transaction tables consistent reads - undo records applied 65,910
cleanout - number of ktugct calls                               1
ORA-01555常见的原因有undo表空间不够和SQL长时间执行, 如果是因为延迟块清除, 可以从会话统计信息中找到线索: transaction tables consistent read rollbacks和transaction tables consistent reads – undo records applied.
关于延迟块清除还有一点很有趣, 如果走direct path, 同样会做清除, 但不产生redo record, 在buffer pool中的数据块不会被修改, 接下来的查询需要继续做清除. 并行查询在11g已经不一定是direct path, 所以并行查询做清除时是否产生redo record, 取决于有没有走direct path.

参考至：http://dbsid.com/ora-01555_deplaye_block_cleanout/

如有错误，欢迎指正

邮箱:[e-mail]