Oracle Database 10g: Rollback monitoring

Oracle
Provide users with an accurate assessment of rollback operation time

Are we still in this place? How much longer will it take?

Does it sound familiar? These questions may be raised on the way to the children's favorite theme park, from the backseat of the car, and are often raised constantly and frequently. Don't you want to tell them exactly how long it will take-or, more simply, do you know the answer yourself?

Similarly, when rolling back long-running transactions, some users are constantly asking the same questions. These issues are reasonable because the transaction is locked and normal processing is often affected by the rollback process.

In Oracle 9i Database and lower versions, you can execute queries
SELECT Used_urecfrom v$transaction;
The statement returns the number of redo records that are used by the current transaction, and if the statement is executed repeatedly, a continuously decreasing value is displayed, because the rollback process releases the redo record during its process. You can then calculate the rate by using a snapshot of the interval, and then infer the result of the estimate end time.

Although there is a column named Start_time in the View v$transaction, the column only shows the start time of the entire transaction (that is, before rollback execution). Therefore, in addition to inference, you have no way of knowing at what time the rollback actually was performed.

Extended Statistics for transaction rollback

In Oracle Database 10g, this is simple. When a transaction is rolled back, the event is recorded in the View V$session_longops, which displays long-running transactions. For rollback, if the process takes more than six seconds, the record appears in the view. After rollback execution, you may hide the monitor screen that you are viewing and execute the following query:
Select Time_remainingfrom v$session_longopswhere sid = <sid of the session doing the rollback>;
Now that you realize the importance of this view v$session_longops, let's look at the additional information it must provide. This view is provided in the preview version of Oracle Database 10g, but does not capture information about rolling back transactions. To display all the columns in an easy to read way, we will use the Print_table function described by Tom Kyte in asktom.com. This procedure simply displays the column in tabular form rather than in a commonly used row style.
Sql> set serveroutput on size 999999sql> exec print_table (' select * from v$session_longops where sid = 9 ') Sid:9ser ial#: 68opname:transaction Rollbacktarget:target_desc:xid:0x000e.01c.00000067sofar:20554totalwork:10234units:blo cksstart_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_addres s:00000003b719ed08sql_hash_value:1430203031sql_id:306w9c5amyanrqcsid:0
Note that all changes to the row are shown here, even if the row is deleted and reinserted. The Version_operation column shows the action performed on the row (Insert/update/delete). You do not need a history table or additional columns to complete these operations.

Let's examine each column in these columns carefully. There may be more than one long-running operation in the session-especially since the view contains history of all long-running operations in the previous session. The column Opname shows the record for "transaction rollback", which points us in the right direction. The column time_remaining shows the number of seconds remaining in the estimate, as described earlier, and the column elapsed_seconds shows the time it has consumed so far.

So how does the table provide an assessment of the rest of the time? You can find clues in the column totalwork, which shows the total amount of work to complete, and Sofar shows how much work has been done so far. The unit of work is displayed in the column UNITS. In this case, a block of data is used, so the total of 10,234 blocks in 20,554 blocks have been rolled back so far. This operation has consumed 77 seconds so far. Therefore, the remaining blocks of data will consume:

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

But you don't have to use this method to get the value, it's clearly shown. Finally, column Last_update_time displays the time for the contents of the current view, which is used to enhance your interpretation of the results.

SQL statement

Another important new piece of information is the identifier for the SQL statement that is being rolled back. Earlier, sql_address and Sql_hash_value were used to get the SQL statements that were being rolled back. The new column sql_id corresponds to the sql_id of the View V$sql, as follows:
SELECT sql_textfrom V$sqlwhere sql_id = <value of sql_id from v$session_longops>;
The query returns the statements that were rolled back, thus providing additional checksums and the address and hash values of the SQL statements.

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 failback of a transaction.

For example, suppose the instance shuts down abnormally during a large update. When the instance starts, the failed transaction is rolled back. If the initialization parameter value for parallel recovery is enabled, the rollback occurs in parallel rather than serially, as if it occurred in a regular transaction rollback. The next task is to evaluate the completion time of the rollback process.

View V$fast_start_transactions shows the transactions that are generated by the rollback failure transaction. A similar view v$fast_start_servers displays the number of parallel query servers that are processed for rollback. Both views are available in previous versions, but the new column XID that displays the transaction identifier makes the join more convenient. In Oracle9i Database and the lower version, you must join the view through three columns (the usn-number, slt-the storage area code in the Redo segment, seq-serial number). Its parent set is displayed in Parentusn, Parentslt, and Parentseq. In Oracle Database 10g, you only need to connect it to the XID column, whose parent XID is represented by an intuitive name: Pxid.

The most useful piece of information comes from the column rcvservers in the V$fast_start_transactions view. If a parallel rollback occurs, the column displays the number of parallel query servers. You can view the column to see how many parallel query processes have been started:
Select Rcvservers from V$fast_start_transactions;
If the output is 1, the transaction is being serially rolled back 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. You can then execute ALTER SYSTEM SET fast_start_parallel_rollback = high, creating a parallel server at 4 times times the number of CPUs.

If the output of the query above is not 1, a parallel rollback is in progress. You can query the same view (v$fast_start_transactions) to get the parent and child transactions (the parent transaction Id-pxid, and the child transaction Id-xid). XID can also be used to join this view and v$fast_start_servers for additional details.

Conclusion

In summary, when a long-running transaction is rolled back in Oracle Database 10g-whether it is a parallel instance recovery session or a ROLLBACK statement executed by a user-all you need to do is view the view v$session_longops and evaluate how much time it will take.

Now, if you can predict the time to reach the theme park!