oracle怎么事务和会话关联,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; /