Oracle事务回滚时间估算

数据库研究版本为
[quote]
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[/quote]

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


当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。
[quote]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;[/quote]
2、dump undo segment head，查看跟踪文件
[quote]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.[/quote]

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

[quote] 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 [color=red]10[/color] 0x80 0x4058 0x0008 0x0000.01143fa5 0x00800c8c 0x0000.000.00000000 [color=red]0x0000035e[/color] 0x00000000[/quote]


3、观察Oracle内部表x$ktuxe [k]ernel layer [t]ransaction layer [u]ndo transaction [e]ntry
[quote]
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[/quote]

进一步利用该内部表可以查看死事务的恢复进度
[quote]select * from x$ktuxe where ktuxecfl = 'DEAD' and ktuxesta = 'ACTIVE'；[/quote]

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

[quote]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;
/[/quote]