数据库研究版本为

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恢复进度
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数为0×0000035e,转化为10进制为862个,这和v$transaction中used_ublk字段数值吻合。

index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num
————————————————————————————————
0×00    9    0×00  0×4058  0xffff  0×0000.01143cae  0×00000000  0×0000.000.00000000  0×00000000   0×00000000
0×01    9    0×00  0×4057  0×0003  0×0000.01143a3b  0×00000000  0×0000.000.00000000  0×00000000   0×00000000
0×02   10    0×80  0×4058  0×0008  0×0000.01143fa5  0×00800c8c  0×0000.000.00000000  0×0000035e   0×00000000

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;
/




Related posts

coded by nessus
分享:  DeliciousGReader鲜果豆瓣CSDN网摘
Trackback

only 1 comment untill now

  1. persists@thiocyanate.hamiltonians” rel=”nofollow”>.…

    thanks for information….

Add your comment now

无觅相关文章插件