Oracle 10g Transaction Rollback Monitoring

Explanation
===========

Prior to Oracle 10g,one could monitor parallel transaction recovery with two views:
v$fast_start_servers and v$fast_start_transactions. However, one
could not monitor normal transaction rollback or transactions recovered by SMON.
Through enhancements to transaction rollback monitoring, one can now monitor in realtime
normal transaction rollback and transaction recovery by SMON. In addition, one can
view historical information about transaction recovery and transaction rollback. Given
historical information about transaction recovery, one can figure out average rollback
duration. When one have the current state of the recovery, one can determine how much
work has been done and how much work remains. Using these two pieces of information,
one can better estimate transaction recovery time and can set the
FAST_START_PARALLEL_ROLLBACK initialization parameter more appropriately to
optimize system performance.

Data Dictionary Changes
=======================

v$fast_start_transactions contains the information about the progress of the
transactions that the Oracle server is recovering. It also contains information about
transactions that the Oracle server has recovered. For transactions that the Oracle server
is recovering, the STATE is RECOVERING. For transactions that the Oracle server has
recovered, the STATE is RECOVERED. Only limited historical information is kept in this
view, and small (in terms of undo blocks) transactions are not tracked.

New columns added are:
• xid: The transaction ID of this transaction.
• parentxid: The transaction ID of the parent transaction.
• rcvservers: It is the number of servers working on this transaction (including
the coordinator server). It can be 1 if only SMON is doing the recovery.

v$fast_start_servers provides information about all the recovery servers
performing, or that have performed parallel transaction recovery. One additional column
is added to this view: XID which gives you the transaction ID of the transaction a
particular server is working on.

Example:

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

This statement can be used to track transaction recovery after instance startup.
As it can be seen, once the transaction is recovered, its statistics remain inside
the v$fast_start_transactions view, but its STATE is set to RECOVERED.
Historical information is kept in v$fast_start_transactions until the next instance shutdown.

More operations are added into v$session_longops. This allows you to monitor ROLLBACK
and ROLLBACK TO operations longer than six seconds.

SQL> SELECT message FROM v$session_longops;
MESSAGE
---------------------------------------------
Transaction Rollback: xid:0x0001.00a.00000812 : 4600
out of 4600 Blocks done
Transaction Rollback: xid:0x0001.007.00000812 : 4601
out of 4601 Blocks done
2 rows

© 2009 – 2010, www.oracledatabase12g.com. 版权所有.文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.

相关文章 | Related posts:

1. 10G Oracle Flashback Transaction Query Introduction and usage
2. Script:when transaction will finish rollback
3. Database Hangs Because SMON is taking 100% CPU doing transaction recovery
4. VIEW: X$KTUXE – Transaction Entry (table)
5. EVENT: 10013 “Instance Recovery”
6. Rollback Segment Utilization:Extent, Wrap and Shrink
7. Scripts for Oracle Streams Performance Tuning Best Practice: Oracle 10g Release 10.2
8. EVENT: 10015 "Write Trace for Undo Segment Recovery"
9. Cheat Sheet for UNDO/Rollback problems
10. 10G: Using the new UTL_COMPRESS Oracle Supplied Package