Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

September 3, 2008

On Extended Transaction Monitoring Statistics After 10g

Filed under: Oracle 10g New Features,Oracle Performance — H.Tonguç Yılmaz @ 5:13 am

After 10g when a long-running transaction is rolling back and if the process takes more than six seconds this event is recorded in the view V$SESSION_LONGOPS.


-- to estimate when the monitored rollback process will finish
SELECT TIME_REMAINING, SOFAR / TOTALWORK * 100 PCT
  FROM V$SESSION_LONGOPS
 WHERE SID = :sid
   AND OPNAME = 'Transaction Rollback' ;

Also after 10g addition to the user issued rollback statements, transactions recovered by SMON and parallel instance recovery sessions are monitored. You can view historical information about transaction recovery and transaction rollback and you can calculate average rollback duration to estimate better transaction recovery time and set the FAST_START_PARALLEL_ROLLBACK initialization parameter more appropriately to optimize system performance. Historical information is kept in V$FAST_START_TRANSACTIONS until the next instance shutdown.

V$FAST_START_TRANSACTIONS contains information both for transactions that the Oracle server is recovering(the STATE is RECOVERING) and for transactions that the Oracle server has recovered(the STATE is RECOVERED). New columns are added to this view as;

• XID: Transaction ID of this transaction
• PXID: Transaction ID of the parent transaction
• RCVSERVERS: Number of servers working on this transaction including the coordinator server(It can be 1 if only SMON is doing the recovery).

For example this statement can be used to track transaction recovery after instance startup;


SELECT state, undoblocksdone, undoblockstotal, cputime
  FROM v$fast_start_transactions;

STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL CPUTIME
---------- -------------- --------------- ---------
RECOVERING 574 1945 16

SQL> /

STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL CPUTIME
---------- -------------- --------------- ---------
RECOVERING 1300 1945 34

SQL> /

STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL CPUTIME
---------- -------------- --------------- ---------
RECOVERED 1945 1945 65

SQL> SELECT message FROM v$session_longops;

MESSAGE
---------------------------------------------
Transaction Rollback: xid:0x0001.00a.00000812 : 1945 out of 1945 Blocks done

For more reading on this topic please visit the article Oracle Database 10g: The Top 20 Features for DBAs by Arup Nanda on OTN; Week 2 – How Much Longer?: Rollback Monitoring

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: