Day 2: How long does it take to roll back monitor? : Rollback monitoring
Provide users with an accurate assessment of rollback operation time
Are we still here? How long does it take?
Is it familiar with it? These issues may be on the way to the children's favorite theme park, which is raised from the lattice, and is often continuously, more and more frequently proposed. Don't you want to tell them how long does it really need? Or easier, do you know the answer yourself?
Similarly, when returning to long-term operation, some users often ask for the same problem. 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 perform a query
SELECT USED_UREC
From V $ Transaction;
This statement returns the number of redo records used by the current transaction, and if the statement is repeatedly executed, the continuous reduced value will be displayed because the rollback process releases the recording during its processing. You can then calculate the rate at a snapshot of a interval, then infer the result of the evaluation end time.
Although there is a column named Start_Time in the view V $ Transaction, the column shows only the start time of the entire transaction (that is, before rollback execution). Therefore, in addition to the inferior, you have no way to know how time is actually executed.
Transaction rollback extension statistics
In Oracle Database 10g, this operation is simple. When the transaction rolls back, the event is recorded in the view V $ session_longops, which shows long running transactions. Used to roll back, if the process takes more than six seconds, the record appears in this view. After rollback, you may hide the viewed monitor screen and do the following query:
SELECT TIME_REMAINING
From v $ session_longops
WHERE SID =
Since you realize the importance of this view V $ session_longops, let us see other information it must provide. This view is provided in the preview of Oracle Database 10g, but does not capture information about rollback transactions. In order to display all columns in an easy-to-read manner, we will use the print_table function described by Tom Kyte in AskTom.com. This procedure is simply displayed in a table mode rather than a commonly used line.
SQL> SET ServerOutput on size 999999
SQL> EXEC Print_Table ('SELECT * from V $ session_longops where sid = 9')
SID: 9
Serial #: 68
Opname: Transaction Rollback
Target:
Target_Desc: XID: 0x000E.01C.00000067
Sofar: 20554
Totalwork: 10234
Units: blocks
Start_time: 07-DEC-2003 21:20:07
Last_Update_time: 07-DEC-2003 21:21:24
Time_remaining: 77ELAPSED_SECONDS: 77
CONTEXT: 0
Message: Transaction Rollback: XID: 0x000E.01C.00000067:
10234 Out of 20554 Blocks Done
Username: SYS
SQL_ADDRESS: 00000003B719ED08
SQL_HASH_VALUE: 1430203031
SQL_ID: 306W9C5AMYANR
QCSID: 0
Note that all changes to the rows are displayed, even if it is deleted and re-inserted. The Version_Operation column displays the operation that is executed on the row (INSERT / UPDATE / DELETE). Complete these operations does not require historical tables or additional columns.
Let us carefully check each column in these columns. There may be more than a long run action in the session - especially because the view contains the history of all long-term operations in the previous session. Column OPNAME displays the record for "transaction rollback", which points to us correctly. Column Time_Remaining Displays the number of remaining time seconds, which has been described above, and column ELAPSED_SECONDS displays the time consumed so far.
So how do this table provide an assessment of the remaining time? Clues can be found in column TotalWork, which shows the total amount of "work" to be completed, and Sofar shows how much work has been completed so far. The unit of work is displayed in column Units. In this example, 10,234 data blocks have been summarized in 20,554 data blocks from 20,554 data blocks from 20,554 data blocks. This operation has consumed 77 seconds so far. Therefore, the remaining data block will consume:
77 * (10234 / (20554-10234)) ~ 77 seconds
But you don't have to take advantage of this method to get this value, it has been clearly displayed. Finally, column LAST_UPDATE_TIME displays the time about the current view content, which will be used to enhance your explanation of the results.
SQL statement
Another part of important new information is an identifier of the SQL statement being rolled back. At earlier, SQL_ADDRESS and SQL_HASH_VALUE are used to get the SQL statement that is being rolled back. The new column SQL_ID corresponds to the view V $ SQL SQL_ID as shown below:
SELECT SQL_TEXT
From v $ SQL
WHERE SQL_ID =
The query returns the rollback statement, so additional checks 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. In parallel back roll events, this information is often used frequently during the recovery of instance recovery and subsequent fault transaction recovery.
For example, it is assumed that the instance is abnormally turned off during a large update. When an instance is started, a failed transaction is rolled back. If an initialization parameter value for parallel recovery is enabled, roll back in parallel instead of serialization, just like it happens in a regular transaction rollback. The next task is to evaluate the completion time of the rollback process.
View v $ found_start_transactions is displayed as a transaction generated by the rollback fault. Similar views V $ found_start_servers Displays the number of parallel query servers for rollback. Both views are provided in previous versions, but the new column XID that shows the transaction identifier makes the connection more convenient. In Oracle9i Database and lower versions, you must connect the view by three columns (USN - heavy segment number, SLT - redemption segment). Its parent is displayed in ParentUSN, PARENTSLT, and PARENTSEQ. In Oracle Database 10g, you only need to connect it to the XID column, and its parent XID is represented by an intuitive name: PXID. The most useful information section comes from the column RCVServers in the V $ FAST_START_TRANSACTIONS view. If it is rolled back, the number of parallel query servers is displayed in this column. You can view this column and learn how much parallel query process started:
SELECT RCVSERVERS from V $ FAST_START_TRANSACTIONS;
If the output is 1, the transaction is being serially rolled back by the SMON process - apparently this is an insufficient way to complete the work. You can change the value of the initialization parameter Recovery_PARALLISM to a value other than 0 or 1, and restart the instance and roll back. You can then execute ALTER System Set Fast_Start_ParalLel_rollback = high, and create a parallel server 4 times in the number of CPUs.
If the output display of the above query is not 1, it is ongoing and rolling. You can query the same view (V $ fast_start_transactions "to get the parent transaction and submit (parent transaction ID - pxid, while submit ID - XID). XID can also be used to join this view with V $ FAST_START_SERVERS to get other details.
in conclusion
In short, when you run a long-term running transaction in Oracle Database 10g - Whether it's a parallel instance recovery session or a user-executive rollback statement - everything you need is to look at the view V $ session_longops and how much time you need to evaluate.
Now, if you can predict the time of the theme park!
