Oracle10g accurate evaluation of rollback operation time

When you roll back a long-running transaction in Oracle Database 10g-whether it is a parallel instance to restore the session or a rollback statement executed by the user-all you need to do is to view the view V $ SESSION_LONGOPS evaluate the time required.

In Oracle 9i Database and earlier versions, you can query:

SELECT USED_URECFROM V$TRANSACTION;

This statement returns the number of redo records used by the current firm. If you execute this statement repeatedly, the number of records that are continuously reduced is displayed, because the rollback process releases the redo record during its processing. Then you can calculate the rate by taking snapshots of a period of time, and then deduce the evaluation end time result.

Although there is a column named START_TIME in the view V $ TRANSACTION, this column only displays the start time of the entire TRANSACTION, that is, before the rollback execution ). Therefore, you have no way to know the time at which the rollback was actually performed, except for inference.

Extended statistics of transaction rollback

In Oracle Database 10 Gb, this operation is very simple. When the transaction is rolled back, the event is recorded in the view V $ SESSION_LONGOPS, which shows long-running transactions. Used for rollback. if the process takes more than six seconds, the record appears in this view. After rollback, you may hide the monitoring screen and perform the following query:

select time_remainingfrom v$session_longopswhere sid = ;

Since you are aware of the importance of this view V $ SESSION_LONGOPS, let's look at other information it must provide. This view is provided in the Oracle Database 10g preview, but does not capture information about rollback transactions. To display all columns in a readable manner, we use the PRINT_TABLE function described by Tom Kyte in AskTom.com. In this process, columns are displayed in tables instead of common rows.

SQL> set serveroutput on size 999999SQL> exec print_table('select * from v$session_longops where sid = 9')SID : 9SERIAL#　 : 68OPNAME:Transaction RollbackTARGET:TARGET_DESC　 :xid:0x000e.01c.00000067SOFAR : 20554TOTALWORK : 10234UNITS :BlocksSTART_TIME:07-dec-2003 21:20:07LAST_UPDATE_TIME　:07-dec-2003 21:21:24TIME_REMAINING: 77ELAPSED_SECONDS　 : 77CONTEXT　 : 0MESSAGE　 :Transaction Rollback:xid:0x000e.01c.00000067 :10234 out of 20554 Blocks doneUSERNAME　：SYSSQL_ADDRESS　 :00000003B719ED08SQL_HASH_VALUE: 1430203031SQL_ID:306w9c5amyanrQCSID : 0

Note: All changes to the row are shown here, even if the row is deleted and inserted again. The VERSION_OPERATION column displays the operations performed on this row (Insert/Update/Delete ). To complete these operations, no historical tables or additional columns are required.

Let's carefully check each of these columns. There may be more than one long-term running operation in a session-especially because the view contains the history of all the long-term running operations in the previous session. The column OPNAME shows that this record is used for transaction rollback, which indicates the correct direction. The TIME_REMAINING column shows the remaining time seconds evaluated, which has been described earlier, and the ELAPSED_SECONDS column shows the time consumed so far.

How can this table evaluate the remaining time? You can find clues in the TOTALWORK column. This column shows the total number of jobs to be completed, and SOFAR shows how much work has been done so far. The unit of work is displayed in the column UNITS. In this example, data blocks are used. Therefore, a total of 20,554 data blocks have been rolled back so far. So far, this operation has consumed 77 seconds. Therefore, the remaining data blocks will consume:

77 * (10234/(20554-10234) interval 77 seconds

However, you do not need to use this method to obtain the value. It is clearly displayed. Finally, the LAST_UPDATE_TIME column displays the time for the current view content, which is used to enhance your interpretation of the results.

SQL statement

Another important new information is the identifier of the SQL statement being rolled back. Previously, SQL _ADDRESS and SQL _HASH_VALUE were used to obtain the SQL statement being rolled back. The new column SQL _ID corresponds to the SQL _ID of view V $ SQL, as shown below:

SELECT SQL_TEXTFROM V$SQLWHERE SQL_ID = ;

　

This query returns the rolled back statement, so it provides additional verification and the address and hash value of the SQL statement.

Parallel instance recovery

If the DML operation is a parallel operation, the column QCSID displays the SID of the parallel query Server session. This information is often used in parallel rollback events, such as instance recovery and subsequent fault transaction recovery.

For example, if an exception occurs during a large update, the instance is shut down. When the instance is started, the faulty transaction is rolled back. If the initialization parameter value for parallel recovery is enabled, the rollback occurs in parallel instead of in serial mode, just as it occurs in the general transaction rollback. The next task is to evaluate the completion time of the rollback process.

View V $ FAST_START_TRANSACTIONS shows the transactions generated by the faulty transaction rollback. Similar view V $ FAST_START_SERVERS shows the number of parallel query servers that process rollback. Both views are provided in previous versions, but the new column XID that displays the transaction identifier makes the join more convenient. In Oracle9i Database and earlier versions, you must use three columns of USN-Redo Field Numbers, storage area numbers in SLT-Redo segments, and SEQ-serial numbers to connect to the view. The parent set is displayed in PARENTUSN, PARENTSLT, and PARENTSEQ. In Oracle Database 10g, you only need to link it to the XID column, and its parent XID is expressed by an intuitive name: PXID.

The most useful information is the column RCVSERVERS in the V $ FAST_START_TRANSACTIONS view. If a parallel rollback occurs, the number of parallel query servers is displayed in this column. You can view this column to learn how many parallel query processes are started:

select rcvservers from v$fast_start_transactions;

If the output is 1, the transaction is performing a serial rollback by the SMON process-obviously this is an inadequate way to complete the work. You can change the value of the initialization parameter RECOVERY_PARALLELISM to a value other than 0 or 1 and restart the instance for parallel rollback. Then you can run alter system set FAST_START_PARALLEL_ROLLBACK = HIGH to create a parallel server four times the number of CPUs.

If the output of the preceding query is not 1, parallel rollback is in progress. You can query the same view (V $ FAST_START_TRANSACTIONS) to obtain the Parent and Child transaction parent transaction id-PXID, while the child transaction id-XID ). XID can also be used to join this view with V $ FAST_START_SERVERS for additional details.

Conclusion

In short, when you roll back a long-running transaction in Oracle Database 10g-whether it is a parallel instance to restore the session or a rollback statement executed by the user-all you need to do is to view the view V $ SESSION_LONGOPS evaluate the time required.

(