ITPub博客

首页 > Linux操作系统 > Linux操作系统 > How Long Will My Rollback Take? Is my Transaction Doing Anything?

How Long Will My Rollback Take? Is my Transaction Doing Anything?

原创 Linux操作系统 作者:spider0283 时间:2012-02-26 14:57:04 0 删除 编辑
 [ID 169138.1]	 
Problem Description
-------------------


A large transaction is rolling back,  questions being asked are:  

 How long it will take to rollback ?
 Is the transaction hung or is it performing work ?.

Solution Description
--------------------
Determine if a transaction is doing work or rolling back by querying 
v$transaction.used_urec and watching for a change in the value.  If used_urec 
is increasing, the transaction is moving forward.  If used_urec 
is decreasing, the transaction is rolling back.

Oracle 8.x
-------------

In Oracle 8.x, if the database has not been shutdown and restarted, look 
at v$transaction.used_urec and v$transaction.used_ublk.  These fields are the 
number of undo records and undo blocks currently held by a transaction.  

By querying v$transaction over a time interval, the number of 
records/blocks rolled back in a given time period can be calculated.  From this
rate, use the following to figure how long it will take to rollback the entire transaction.

v$session can be joined to v$transaction on the following condition:

v$session.saddr=v$transaction.ses_addr.

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

      SID USERNAME         XIDUSN  USED_UREC  USED_UBLK
---------- ------------ ---------- ---------- ----------
        14 SCOTT             3      10000         82

The above shows that sid 14 is holding 10,000 undo records in rollback segment number 3.

If the database has been shutdown (abort) and restarted, the information in v$transaction
is reset and is not useful.  To find out how long the rollback will
take, dump the rollback segment header to find the number of undo blocks.
Take two segment header dumps, calculate the number of undo blocks
rolled back during the time interval, and then calculate how long to roll back 
the entire transaction.  
If the database has been restarted it will be difficult to tell which
rollback segment was being used so will need to dump all 
the rollback segment headers initially.  .  

To dump the file headers, first determine which block stores the file header.

>select segment_name, header_file, header_block from dba_segments 
>where segment_type='ROLLBACK';

SEGMENT_NAME    HEADER_FILE HEADER_BLOCK
--------------- ----------- ------------
SYSTEM                    1            2
RBS0                      2            2
RBS1                      2          514
RBS2                      2         1026
RBS3                      2         1538

Next, issue the following command in 8.x+ to dump the file header.

> alter system dump datafile 2 block 1026;

This will generate a dump file in user_dump_dest.  In the dump file  
look for the transaction table for the rollback segment.  There will be a 
column called nub which holds the number of undo blocks for the transaction.

-- Trace file snippet follows

  index  state cflags  wrap#    uel         scn            dba            parent-xid    nub
  ------------------------------------------------------------------------------------------------
   0x00   10    0xc0  0x1995  0x0007  0x0000.009dd0ac  0x00800193  0x0000.000.00000000  0x00000052
   0x01    9    0x80  0x1994  0x003c  0x0000.009dd007  0x00801f3f  0x0000.000.00000000  0x00000001

Notice that the first slot holds an uncommitted transaction (state=10) and the 
nub (number undo blocks) = 0x52 or decimal 82.

Solution Description - Oracle 9.x and above
-------------------------------------------------------
The 8.x method can be used for Oracle 9i and above,  if the database has not been shutdown and restarted.
Refer Note 304513.1  How To Determine Who Is Using Undo Tablespace And How Much?

If the database has been restarted , there is an easier way to determine
the number of undo blocks required for rollback.  That is by querying table 
x$ktuxe..ktuxesiz.

select distinct ktuxesiz from x$ktuxe;
 
  KTUXESIZ
----------
         0
         1
         2
         3
       107

Column ktuxesiz specifies the number of undo blocks used by the transaction.
In the above output,we can see one of the transaction is using 107 undo blocks.

You can use

select sum(distinct(ktuxesiz)) from x$ktuxe;

which will show the total number of blocks in the transaction that needs to be rolled back.

References
----------

Note:1021486.6 How to Tell When Rollback Finishes

返回页首返回页首

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/38267/viewspace-717169/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-03-29

  • 博文量
    194
  • 访问量
    611236