了解你所不知道的SMON功能(十二):Shrink UNDO(rollback) SEGMENT

2013/02/05 by admin Leave a Comment

SMON对于Undo(Rollback)segment的日常管理还不止于OFFLINE UNDO SEGMENT ，在AUM(automatic undo management或称SMU)模式下SMON还定期地收缩Shrink Rollback/undo segment。

触发场景

这种AUM下rollback/undo segment的undo extents被shrink的现象可能被多种条件触发：

当另一个回滚段的transaction table急需undo空间时
当SMON定期执行undo/rollback管理时(每12个小时一次):
- SMON会从空闲的undo segment中回收undo space，以便保证其他tranaction table需要空间时可用。另一个好处是undo datafile的身材不会急速膨胀导致用户要去resize
- 当处于undo space空间压力时，特别是在发生UNDO STEAL的条件下; SGA中会记录前台进程因为undo space压力而做的undo steal的次数(v$undostat UNXPSTEALCNT EXPSTEALCNT)；若这种UNDO STEAL的次数超过特定的阀值，则SMON会尝试shrink transaction table

若smon shrink rollback/undo真的发生时，会这样处理：

计算平均的undo retention大小，按照下列公式：

retention size=(undo_retention * undo_rate)/(#online_transaction_table_segment 在线回滚段的个数)

对于每一个undo segment

若是offline的undo segment，则回收其所有的已过期expired undo extents，保持最小2个extents的空间
若是online的undo segment，则回收其所有的已过期expired undo extents，但是保持其segment所占空间不小于平均retention对应的大小。

注意SMON的定期Shrink，每12个小时才发生一次，具体发生时可以参考SMON进程的TRACE。

若系统中存在大事务，则rollback/undo segment可能扩展到很大的尺寸；视乎事务的大小，则undo tablespace上的undo/rollback segment会呈现出不规则的空间占用分布。

SMON的定期清理undo/rollback segment就是要像一个大锤敲击钢铁那样，把这些大小不规则的online segment清理成大小统一的回滚段，以便今后使用。

当然这种定期的shrink也可能造成一些阻碍，毕竟在shrink过程中会将undo segment header锁住，则事务极低概率可能遇到ORA-1551错误:

[oracle@vmac1 ~]$ oerr ora 1551
01551, 00000, "extended rollback segment, pinned blocks released"
// *Cause: Doing recursive extent of rollback segment, trapped internally
//        by the system
// *Action: None

如何禁止SMON SHRINK UNDO SEGMENT?

可以通过设置诊断事件event=’10512 trace name context forever, level 1’来禁用SMON OFFLINE UNDO SEGS;

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmac.cn

SQL> alter system set events '10512 trace name context forever,level 1';

System altered.

相关BUG

这些BUG主要集中在9.2.0.8之前，10.2.0.3以后几乎绝迹了：

Bug 1955307 – SMON may self-deadlock (ORA-60) shrinking a rollback segment in SMU mode [ID 1955307.8]
Bug 3476871 : SMON ORA-60 ORA-474 ORA-601 AND DATABASE CRASHED
Bug 5902053 : SMON WAITING ON ‘UNDO SEGMENT TX SLOT’ HANGS DATABASE
Bug 6084112 : INSTANCE SLOW SHOW SEVERAL LONGTIME RUNNING WAIT EVENTS

Filed Under: Oracle, Oracle Internal Research内部原理研究 Tagged With: rollback, smon, space management, undo

Know more about commit

2011/07/13 by admin Leave a Comment

COMMIT操作是RDBMS中事务结束的标志，在Oracle中与commit紧密相关的是SCN(System Change Number)。

引入SCN的最根本目的在于：

为读一致性所用
为redolog中的记录排序，以及恢复

SCN由SCN Base和Scn Wrap组成，是一种6个字节的结构(structure)。其中SCN Base占用4个字节，而SCN wrap占用2个字节。但在实际存储时SCN-like的stucture常会占用8个字节。

 ub4 kscnbas
 ub2 kscnwrp

struct kcvfhcrs, 8 bytes                 @100                              Creation Checkpointed at scn
      ub4 kscnbas                        @100      0x000a8849
      ub2 kscnwrp                        @104      0x0000

在Oracle中一个事务的开始包含以下操作:

绑定一个可用的rollback segment
在事务表(transaction table)上分配一个必要的槽位
从rollback segment中分配undo block

注意system rollback segment是一种特殊的回滚段，在10g以后普通回滚段的类型都变成了”TYPE2 UNDO”，而唯有system rollback segment的类型仍为”ROLLBACK”，这是由其特殊性造就的:

SQL> col segment_name for a20
SQL> col rollback for a20
SQL> select segment_name,segment_type from dba_segments where segment_type='ROLLBACK';

SEGMENT_NAME         SEGMENT_TYPE
-------------------- ------------------
SYSTEM               ROLLBACK

System rollback segment面向的是SYSTEM表空间上数据字典对象相关事务的数据，以及由对用户数据产生的递归SQL调用所产生的数据。

Oracle不使用基于内存锁管理器的行锁，Oracle中的row lock是基于数据块的。数据块中的Interested Transaction List(ITL)是行锁的重要标志。
ITL的分配遵循以下的原则:

找出未被使用的ITL
找出最老的已经事务提交的ITL
做部分的块清理，直到有可用的ITL
扩展ITL区域，一条ITL占用24字节

当事务提交COMMIT时，需要完成以下步骤的操作:

得到一个SCN值
使用得到的SCN更新事务表中的槽位
在redo log buffer中创建一条commit记录
将redo log buffer刷新到磁盘上的在线日志文件
释放表和行上的锁(may cause delayed block cleanout)

Filed Under: Oracle, Oracle Internal Research内部原理研究 Tagged With: commit, ITL, Oracle Lock锁机制, redo, rollback, transaction, undo

监控一个大事务的回滚

2011/05/25 by admin 3 Comments

我们在大的事务失败时往往面临长时间的回滚，在回滚期间表会被加以TM-3 SX sub-exclusive锁，此时一般我们是无法针对表实施DDL操作的。长时间的大事务回滚可能耗尽我们的耐心，不过我们还是有办法预估何时回滚能够完成的，参考中的脚本<Script:when transaction will finish rollback>中的脚本，注意该脚本需要访问x$ktuxe内部视图，所以需要以sysdba身份方能执行。

SQL> select * from v$lock where type in ('TM','TX');

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
0ED0F30C 0ED0F33C          9 TM      13865          0          3          0       3757          0
2C3975FC 2C39763C          9 TX      65557        677          6          0       3757          0

SQL> select object_name,object_type from dba_objects where object_id=13865;

OBJECT_NAM OBJECT_TYPE
---------- -------------------
SAMPLE     TABLE

因为表上存在TM-3锁，所以此时是无法对表执行需要持有TM-6 exclusive排它锁的DDL操作的

SQL> drop table sample;
drop table sample
           *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

SQL> select * from sample where rownum=1 for update nowait;
select * from sample where rownum=1 for update nowait
              *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

rollback完成之前相关行上的row level lock不会被释放，因此dml操作会被block

SQL> select xidusn,xidslot,xidsqn,status from v$transaction;

    XIDUSN    XIDSLOT     XIDSQN STATUS
---------- ---------- ---------- ----------------
         1         21        677 ACTIVE

从v$tranasction视图中可以看到事务1.21.677处于active状态

使用set time on命令显示当前时间

SQL> set time on;

20:54:26 SQL>

set serveroutput on
set feedback off
prompt
prompt Looking for transactions that are rolling back ...
prompt

declare
  cursor tx is
    select
      s.username,
      t.xidusn,
      t.xidslot,
      t.xidsqn,
      x.ktuxesiz
    from
      sys.x$ktuxe  x,
      sys.v_$transaction  t,
      sys.v_$session  s
    where
      x.inst_id = userenv('Instance') and
      x.ktuxesta = 'ACTIVE' and
      x.ktuxesiz > 1 and
      t.xidusn = x.ktuxeusn and
      t.xidslot = x.ktuxeslt and
      t.xidsqn = x.ktuxesqn and
      s.saddr = t.ses_addr;
  user_name  varchar2(30);
  xid_usn    number;
  xid_slot   number;
  xid_sqn    number;
  used_ublk1 number;
  used_ublk2 number;
begin
  open tx;
  loop
    fetch tx into user_name, xid_usn, xid_slot, xid_sqn, used_ublk1;
    exit when tx%notfound;
    if tx%rowcount = 1
    then
      sys.dbms_lock.sleep(10);
    end if;
    select
      sum(ktuxesiz)
    into
      used_ublk2
    from
      sys.x$ktuxe
    where
      inst_id = userenv('Instance') and
      ktuxeusn = xid_usn and
      ktuxeslt = xid_slot and
      ktuxesqn = xid_sqn and
      ktuxesta = 'ACTIVE';
    if used_ublk2 < used_ublk1
    then
      sys.dbms_output.put_line(
        user_name ||
        '''s transaction ' ||
        xid_usn  || '.' ||
        xid_slot || '.' ||
        xid_sqn  ||
        ' will finish rolling back at approximately ' ||
        to_char(
          sysdate + used_ublk2 / (used_ublk1 - used_ublk2) / 6 / 60 / 24,
          'HH24:MI:SS DD-MON-YYYY'
        )
      );
    end if;
  end loop;
  if user_name is null
  then
    sys.dbms_output.put_line('No transactions appear to be rolling back.');
  end if;
end;
/

MACLEAN's transaction 1.21.677 will finish rolling back at approximately 21:46:45 25-MAY-2011

以上脚本给出了估计的1.21.677事务的回滚结束时间为21:46:45，即还有50分钟，
注意这只是根据之前单位时间内rollback的进度所得到的一个估算值，所以并不精确，但还是可以做为一个参考

再次运行以上脚本，会发现2次的结果不一定一致

SQL> /
MACLEAN's transaction 1.21.677 will finish rolling back at approximately 21:35:47 25-MAY-2011

在产品数据库中应当尽可能避免出现大事务回滚的现象，因为除去lock的影响外，large transaction rollback还会导致CPU使用率大幅上升并产生往往比事务本身所产生的更多的redo重做日志记录。

Filed Under: Oracle Tagged With: monitor, rollback, transaction

Script:when transaction will finish rollback

2007/04/19 by admin Leave a Comment

-------------------------------------------------------------------------------
--
-- Script:	rolling_back.sql
-- Purpose:	to predict when transactions will finish rolling back
-- For:		9.0+
--
-- Copyright:	(c) Ixora Pty Ltd
-- Author:	Steve Adams
--
-------------------------------------------------------------------------------
@save_sqlplus_settings

set serveroutput on
set feedback off
prompt
prompt Looking for transactions that are rolling back ...
prompt

declare
  cursor tx is
    select
      s.username,
      t.xidusn,
      t.xidslot,
      t.xidsqn,
      x.ktuxesiz
    from
      sys.x$ktuxe  x,
      sys.v_$transaction  t,
      sys.v_$session  s
    where
      x.inst_id = userenv('Instance') and
      x.ktuxesta = 'ACTIVE' and
      x.ktuxesiz > 1 and
      t.xidusn = x.ktuxeusn and
      t.xidslot = x.ktuxeslt and
      t.xidsqn = x.ktuxesqn and
      s.saddr = t.ses_addr;
  user_name  varchar2(30);
  xid_usn    number;
  xid_slot   number;
  xid_sqn    number;
  used_ublk1 number;
  used_ublk2 number;
begin
  open tx;
  loop
    fetch tx into user_name, xid_usn, xid_slot, xid_sqn, used_ublk1;
    exit when tx%notfound;
    if tx%rowcount = 1
    then
      sys.dbms_lock.sleep(10);
    end if;
    select
      sum(ktuxesiz)
    into
      used_ublk2
    from
      sys.x$ktuxe
    where
      inst_id = userenv('Instance') and
      ktuxeusn = xid_usn and
      ktuxeslt = xid_slot and
      ktuxesqn = xid_sqn and
      ktuxesta = 'ACTIVE';
    if used_ublk2 < used_ublk1
    then
      sys.dbms_output.put_line(
        user_name ||
        '''s transaction ' ||
        xid_usn  || '.' ||
        xid_slot || '.' ||
        xid_sqn  ||
        ' will finish rolling back at approximately ' ||
        to_char(
          sysdate + used_ublk2 / (used_ublk1 - used_ublk2) / 6 / 60 / 24,
          'HH24:MI:SS DD-MON-YYYY'
        )
      );
    end if;
  end loop;
  if user_name is null
  then
    sys.dbms_output.put_line('No transactions appear to be rolling back.');
  end if;
end;
/

prompt
@restore_sqlplus_settings

Filed Under: Oracle, Oracle Internal Research内部原理研究, Oracle脚本script Tagged With: Oracle脚本script, rollback, transaction, x$ktuxe

Rollback Segment Utilization:Extent, Wrap and Shrink

2005/07/10 by admin Leave a Comment

This practice will demonstrate the concept of extent, wrap and shrink in rollback segment utilization. You will:

Use the create rollback segment and alter rollback segment syntax.
Examine the V$ROLLSTAT view.
Determine what would be required to force an extent, a wrap and a shrink.

ASSUMPTIONS

The directory and filenames referenced in the commands in this practice reference the UNIX operating system. However, simply changing the directory and filename references to match the operating system you are using will allow all the commands to work properly on your operating system.
The database version must be Oracle8i release 2, or higher.
The database blocksize is 2048 bytes.
The output produced in these instructions is from a UNIX operating system. There may be some variance in your output data.

INSTRUCTIONS:

1. Create a rollback segment of initial 10k, next 10k and minextents of 2.

Ensure there is only one user rollback segment online so that all the transactions have to use this newly created rollback segment.

SQL> create rollback segment RBS4

2 storage (initial 10K next 10K minextents 2);

Rollback segment created.

SQL> alter rollback segment RBS4 online;

Rollback segment altered.

Note: Put all the other user RBSs offline

2. Create two user sessions that use the rollback segment RBS4. In Session 1 create TAB111 and insert a value. Do not commit. In Session 2, issue create table TAB112 as select * from sys.obj$ where 1=2;

Examine the statistics in V$ROLLSTAT and select the number of shrinks, wraps and extends. Check how many extents and blocks belong to this rollback segment. Determine what would be required to force an extent, a wrap and a shrink.

Session 1

SQL> create table TAB111 ( a number);

Table created.

SQL> insert into TAB111 values (1);

1 row created.

Note: This session does not commit. This means that the first extent cannot be reused.

Session 2

SQL> create table TAB112

2 as select *

3 from sys.obj$

4 where 1 = 2;

Table created.

SQL> select hwmsize, shrinks, wraps, extends

2 from v$rollstat

3 where usn = 5;

HWMSIZE SHRINKS WRAPS EXTENDS

———- ———- ———- ———-

129024 0 0 0

SQL> insert into TAB112 select * from sys.obj$;

3121 rows created.

SQL> select hwmsize, shrinks, wraps, extends

2 from v$rollstat

3 where usn = 5;

HWMSIZE SHRINKS WRAPS EXTENDS

———- ———- ———- ———-

260096 0 3 2

Note: Session 2 has run a long running transaction. Initially, the current extent is extent 0 (which is where the other transaction started running). Every new transaction gets allocated blocks in the current extent as long as they are available. When extent 0 is full, the transaction moves on to extent 1 (making it now the current extent). The number of wraps increases by one when moving from one extent to the next.

Again, new blocks are allocated from this extent until none is available. Then, we try to wrap back into extent 0 (remember, initially there are only two extents). However, this is not allowed as session 1 has an active transaction in extent 0. Every time the head of the extent list catches up with the tail, a new extent must be added. Extends is now increased and since we are moving to the newly allocated extent, wraps is also increased (now it would have the value 2).

This process is repeated one more time, and we end up with the solution displayed: wraps=3,

extends = 2.

3. Commit both active transactions and re-examine v$rollstat. Force RBS4 to shrink and re-examine v$rollstat to see the changes.

Session 1

SQL> commit;

Commit complete.

Session 2

SQL> commit;

Commit complete.

SQL> select hwmsize, shrinks, wraps, extends

2 from v$rollstat

3 where usn = 5;

HWMSIZE SHRINKS WRAPS EXTENDS

———- ———- ———- ———-

260096 0 3 2

SQL> alter rollback segment rbs4 shrink;

Rollback segment altered.

SQL> select hwmsize, shrinks, wraps, extends

2 from v$rollstat

3 where usn = 5;

HWMSIZE SHRINKS WRAPS EXTENDS

———- ———- ———- ———-

260096 1 3 2

SQL> select optsize, extents

2 from v$rollstat

3 where usn=5;

OPTSIZE EXTENTS

———- ———-

Note: When optimal is not set, the shrink command reduces the size of the rollback segment to 2.

4. To demonstrate clearly how the number of wraps increases every time a different extent becomes the current one, repeat the same exercise above but create the rollback segment with three extents to start with.

SQL> alter rollback segment RBS4 offline;Rollback segment altered.

SQL> drop rollback segment RBS4;

Rollback segment dropped.

SQL> create rollback segment RBS4

2 storage (initial 10K next 10K minextents 3);

Rollback segment created.

SQL> alter rollback segment RBS4 online;

Rollback segment altered.

Note: Put all the other user RBSs offline

5. Create two user sessions and examine the statistics in V$ROLLSTAT.

Session 1

SQL> insert into TAB111 values (1);

1 row created.

Note: This session does not commit. This means that the first extent cannot be reused.

Session 2

SQL> select hwmsize, shrinks, wraps, extends

2 from v$rollstat

3 where usn = 5;

HWMSIZE SHRINKS WRAPS EXTENDS

———- ———- ———- ———-

129024 0 0 0

SQL> insert into TAB112 select * from sys.obj$;

3121 rows created.

SQL> select hwmsize, shrinks, wraps, extends

2 from v$rollstat

3 where usn = 5;

HWMSIZE SHRINKS WRAPS EXTENDS

———- ———- ———- ———-

260096 0 3 1

Note: We need a total of four extents to perform both transactions. If the rollback segment has 2 extents to start with, there will be a need for an additional 2 (extends = 2). If minextents is 3, then only one additional extent is necessary (extends = 1).

However, the wraps occur when we move from extent 0 to extent 1, from 1 to 2 and from 2 to 3 (wraps = 3).

6. Re-execute the transaction for session 2, and examine V$ROLLSTAT.

Session 2

SQL> insert into TAB112 select * from sys.obj$;

3121 rows created.

SQL> select hwmsize, shrinks, wraps, extends

2 from v$rollstat

3 where usn = 5;

HWMSIZE SHRINKS WRAPS EXTENDS

———- ———- ———- ———-

456704 0 6 4

Note: Another run of the transaction forces the allocation of three more extents and the number of wraps continues to increase accordingly even though extent 0 has never been reused because the transaction in session 1 is preventing this.

7. Commit both transactions and re-execute the insert into TAB112.

Session 1

SQL> commit;

Commit complete.

Session 2

SQL> commit;

Commit complete.

SQL> insert into TAB112 select * from sys.obj$;

3121 rows created.

SQL> select hwmsize, shrinks, wraps, extends

2 from v$rollstat

3 where usn = 5;

HWMSIZE SHRINKS WRAPS EXTENDS

———- ———- ———- ———-

456704 0 10 4

Note: Both transactions have committed now, so there is no need to allocate new extents but as we continue to move from one extent to the next, the number of wraps increases.

8. Force RBS4 to shrink and re-examine V$ROLLSTAT.

SQL> alter rollback segment rbs4 shrink;

Rollback segment altered.

SQL> select hwmsize, shrinks, wraps, extends

2 from v$rollstat

3 where usn = 5;

HWMSIZE SHRINKS WRAPS EXTENDS

———- ———- ———- ———-

456704 1 10 4

SQL> select optsize, extents

2 from v$rollstat

3 where usn=5;

OPTSIZE EXTENTS

———- ———-

Note: When optimal is not set, the shrink reduces the size of the rollback segment to 2 not to minextents which in this case was set to 3.

9. The following exercises illustrate what happens when optimal is set. With optimal set, we first check whether we need to perform a shrink before crossing the extent boundary.

Create a rollback segment with minextents of 2 and optimal of 20k. Ensure all other rollback segments are offline.

SQL> alter rollback segment RBS4 offline;

Rollback segment altered.

SQL> drop rollback segment RBS4;

Rollback segment dropped.

SQL> create rollback segment RBS4

2 storage (initial 10K next 10K minextents 2 optimal 20k);

Rollback segment created.

SQL> alter rollback segment RBS4 online;

Rollback segment altered.

Note: Put all the other user RBSs offline

10. Create two user sessions and start a transaction in Session 1 by inserting a value. Do not commit this session.

In Session 2, examine V$ROLLSTAT for extents and wraps. Issue insert into TAB112 as select * from sys.obj$; Re-examine V$ROLLSTAT and note the changes.

Session 1

SQL> insert into TAB111 values (1);

1 row created.

Session 2

SQL> select hwmsize, shrinks, wraps, extends

2 from v$rollstat

3 where usn = 5;

HWMSIZE SHRINKS WRAPS EXTENDS

———- ———- ———- ———-

129024 0 0 0

SQL> insert into TAB112 select * from sys.obj$;

3121 rows created.

SQL> select hwmsize, shrinks, wraps, extends

2 from v$rollstat

3 where usn = 5;

HWMSIZE SHRINKS WRAPS EXTENDS

———- ———- ———- ———-

260096 0 3 2

Session 2

SQL> insert into TAB112 select * from sys.obj$;

3121 rows created.

SQL> select hwmsize, shrinks, wraps, extends

2 from v$rollstat

3 where usn = 5;

HWMSIZE SHRINKS WRAPS EXTENDS

———- ———- ———- ———-

456704 0 6 5

Commit both sessions. In Session 2, re-execute the insert from sys.obj$ and examine the shrinks, wraps and extends from V$ROLLSTAT.

Determine the optimal size from V$ROLLSTAT and explain the results.

Session 1

SQL> commit;

Commit complete.

Session 2

SQL> commit;

Commit complete.

SQL> insert into TAB112 select * from sys.obj$;

3121 rows created.

SQL> select hwmsize, shrinks, wraps, extends

2 from v$rollstat

3 where usn = 5;

HWMSIZE SHRINKS WRAPS EXTENDS

———- ———- ———- ———-

456704 1 10 8

SQL> select optsize, extents

2 from v$rollstat

3 where usn=5;

OPTSIZE EXTENTS

———- ———-

20480 5

Note: At the time of the shrink there were 7 extents in the rollback segment, the two we started with plus 5 extends. Optimal was set to 20kb = 2 extents. The current extent (number 7) cannot be deallocated and neither can the initial extent. The shrink brings the rollback segment size down to optimal. As the transaction runs, it required 3 more extents, hence extends is now 8 and the number of extents is back to 5.

Filed Under: Oracle Tagged With: rollback, shrink