Oracle事务回滚时间估算

数据库研究版本为

引用

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

通过关联$session和v$transaction可以看到Oracle中会话使用undo block的情况
可以这样理解，当Oracle处于open 状态，当Oracle回滚事务的时候，可以从used_urec，used_ublk数值可以初步估计Oracle回滚事务的速度。

引用

SQL> select a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk
  2  from v$session a, v$transaction b
  3  where a.saddr=b.ses_addr;

       SID USERNAME                           XIDUSN  USED_UREC  USED_UBLK
---------- ------------------------------ ---------- ---------- ----------
        16 TEST                                    8      31536        862

当Oracle非正常关闭（如shutdown abort）时，处于业务繁忙期，再次open时，v$transaction重置，smon进程事务回滚，有以下方法可以估算smon恢复进度
1、查询视图V$FAST_START_TRANSACTIONS中字段UNDOBLOCKSDONE，UNDOBLOCKSTOTAL估算smon恢复进度,这里涉及到参数FAST_START_PARALLEL_ROLLBACK的设置，设置方法可以查看Oracle文档。需要注意的是Oracle在回滚大事务并行回滚参数设置存在bug，这时候你可以查询视图v$fast_start_servers中字段STATE ，如果只有一进城处于RECOVERING，其他进程处于IDLE，则可考虑将FAST_START_PARALLEL_ROLLBACK设置为false，关闭并行恢复。如果所有进程都处于RECOVERING状态，则可以考虑加大恢复进程，将其设置为high。

引用

SQL>set linesize 100
SQL>alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
SQL>select usn, state, undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone "ToDo",decode(cputime,0,'unknown',sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400))
"Estimated time to complete"
from v$fast_start_transactions;

2、dump undo segment head，查看跟踪文件

引用

SQL> select segment_id, file_id,block_id from DBA_ROLLBACK_SEGS;

SEGMENT_ID    FILE_ID   BLOCK_ID
---------- ---------- ----------
         0          1          9
         1          2          9
         2          2         25
         3          2         41
         4          2         57
         5          2         73
         6          2         89
         7          2        105
         8          2        121
         9          2        137
        10          2        153

11 rows selected.

SQL>  alter system dump datafile 2 block 121;

System altered.

显示部分跟踪文件，从state为10可以看出该slot有未提交的事务，占用的block数为0x0000035e，转化为10进制为862个，这和v$transaction中used_ublk字段数值吻合。

引用

  index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num
  ------------------------------------------------------------------------------------------------
   0x00    9    0x00  0x4058  0xffff  0x0000.01143cae  0x00000000  0x0000.000.00000000  0x00000000   0x00000000
   0x01    9    0x00  0x4057  0x0003  0x0000.01143a3b  0x00000000  0x0000.000.00000000  0x00000000   0x00000000
   0x02   10    0x80  0x4058  0x0008  0x0000.01143fa5  0x00800c8c  0x0000.000.00000000  0x0000035e   0x00000000

3、观察Oracle内部表x$ktuxe [k]ernel layer [t]ransaction layer [u]ndo transaction [e]ntry

引用

SQL> desc  x$ktuxe
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ADDR                                               RAW(4)
INDX                                               NUMBER
INST_ID                                            NUMBER
KTUXEUSN                                           NUMBER
KTUXESLT                                           NUMBER
KTUXESQN                                           NUMBER
KTUXERDBF                                          NUMBER
KTUXERDBB                                          NUMBER
KTUXESCNB                                          NUMBER
KTUXESCNW                                          NUMBER
KTUXESTA                                           VARCHAR2(16)
KTUXECFL                                           VARCHAR2(24)
KTUXEUEL                                           NUMBER
KTUXEDDBF                                          NUMBER
KTUXEDDBB                                          NUMBER
KTUXEPUSN                                          NUMBER
KTUXEPSLT                                          NUMBER
KTUXEPSQN                                          NUMBER
KTUXESIZ                                           NUMBER


SQL> select distinct ktuxesiz from x$ktuxe where KTUXESTA='ACTIVE';

  KTUXESIZ
----------
       862

进一步利用该内部表可以查看死事务的恢复进度

引用

select * from x$ktuxe where ktuxecfl = 'DEAD' and ktuxesta = 'ACTIVE'；

初步估算事务恢复进度，注意KTUXEUSN，KTUXESLT为变量

引用

declare
l_start number;
l_end    number;
begin
  select ktuxesiz into l_start from x$ktuxe where  KTUXEUSN=12 and KTUXESLT=40;
  dbms_lock.sleep(60);
  select ktuxesiz into l_end from x$ktuxe where  KTUXEUSN=12 and KTUXESLT=40;
  dbms_output.put_line('time est Day:'|| round(l_end/(l_start -l_end)/60/24,2));
end;
/