Oracle Estimate of the duration of a rollback operation (估算rollback事务回滚剩余时间)
when a long, running transaction has been rolled back, How Much Longer completion time of the rollback process? The questions are justified, because the transaction holds the locks and normal processing often suffers as the rollback progresses.
1. V$TRANSACTION
In Oracle 9i Database and below, you can issue the query
SELECT USED_UREC,used_ublk FROM V$TRANSACTION;
which returns the number of undo records used by the current transaction, and if executed repeatedly, will show continuously reduced values because the rollback process will release the undo records as it progresses. You can then calculate the rate by taking snapshots for an interval and then extrapolate the result to estimate the finishing time.
If the number of rollback segment blocks in use by a transaction (as seen in V$TRANSACTION.USED_UBLK) is decreasing, then that transaction is rolling back. For large transactions, it may be helpful to be able to estimate when the transaction will finish rolling back. This can be done by monitoring the rate at which the number of used rollback segment blocks is decreasing, and then extrapolating based on the assumption of a constant rate of roll back.
—for active transactions
/* Formatted on 2013-05-08 13:29:35 (QP5 v5.185.11230.41888) */
SET SERVEROUT ON
DECLARE
l_start NUMBER;
l_end NUMBER;
l_interval NUMBER := 30;
l_sid NUMBER;
BEGIN
L_SID := &sid;
SELECT a.used_ublk
INTO l_start
FROM v$transaction a, v$session b
WHERE a.addr = b.taddr AND sid = L_SID;
DBMS_LOCK.sleep (l_interval);
SELECT a.used_ublk
INTO l_end
FROM v$transaction a, v$session b
WHERE a.addr = b.taddr AND sid = L_SID;
IF NVL (l_start, 0) <> NVL (l_end, 0)
THEN
IF NVL (l_start, 0) > NVL (l_end, 0)
THEN
DBMS_OUTPUT.put_line (
'Rolling back ! Time est Second:'
|| ROUND (l_end / (l_start - l_end) * l_interval, 2));
ELSIF NVL (l_start, 0) < NVL (l_end, 0)
THEN
DBMS_OUTPUT.put_line ('Performing DMLs');
END IF;
ELSE
DBMS_OUTPUT.put_line (
'Session is waiting for commit or rollback! Can not estimate finish time ');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('No active transaction');
END;
/
2. v$session_longops
In Oracle Database 10g , this exercise is trivial. When a transaction rolls back, the event is recorded in the view V$SESSION_LONGOPS, which shows long running transactions. For rollback purpose, if the process takes more than six seconds, the record appears in the view. After the rollback is issued, you would probably conceal your monitor screen from prying eyes and issue the following query:
select time_remaining from v$session_longops
Let’s examine each of these columns carefully. There may be more than one long running operation in the sessionespecially because the view contains the history of all long running operations in previous sessions. The column OPNAME shows that this record is for “Transaction Rollback,” which points us in the right direction. The column TIME_REMAINING shows the estimated remaining time in seconds, described earlier and the column ELAPSED_SECONDS shows the time consumed so far.
3. V$FAST_START_TRANSACTIONS
Parallel Instance Recovery
If the DML operation was a parallel operation, the column QCSID shows the SID of the parallel query server sessions. In the event of a parallel rollback, such as during instance recovery and subsequent recovery of a failed transaction, this information often comes in handy.
For example, suppose that during a large update the instance shuts down abnormally. When the instance comes up, the failed transaction is rolled back. If the value of the initialization parameter for parallel recovery is enabled, the rollback occurs in parallel instead of serially, as it occurs in regular transaction rollback. The next task is to estimate the completion time of the rollback process.
The view V$FAST_START_TRANSACTIONS shows the transaction(s) occurring to roll-back the failed ones. A similar view, V$FAST_START_SERVERS, shows the number of parallel query servers working on the rollback. These two views were available in previous versions, but the new column XID, which indicates transaction identifier, makes the joining easier. In Oracle9i Database and below, you would have had to join the views on three columns (USN – Undo Segment Number, SLT – the Slot Number within the Undo Segment, and SEQ – the sequence number). The parent sets were shown in PARENTUSN, PARENTSLT, and PARENTSEQ. In Oracle Database 10g, you only need to join it on the XID column and the parent XID is indicated by an intuitive name: PXID.
select undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone
"ToDo",decode(cputime,0,'unknown',to_char(sysdate+(((undoblockstotal-undoblocksdone)
/ (undoblocksdone / cputime)) / 86400),'yyyy-mm-dd hh24:mi:ss'))
"Estimated time to complete",to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
from v$fast_start_transactions;
4. X$KTUXE
SQL> select * from v$type_size where component='KTU'; COMPONEN TYPE DESCRIPTION TYPE_SIZE -------- -------- -------------------------------- ---------- KTU KTUBH UNDO HEADER 16 KTU KTUXE UNDO TRANSACTION ENTRY 40 KTU KTUXC UNDO TRANSACTION CONTROL 104
Subject: VIEW: X$KTUXE – Transaction Entry (table) – (7.3 – 8.1)
Doc ID: Note:43653.1 Type: REFERENCE
Last Revision Date: 27-APR-2001 Status: PUBLISHED
View: X$KTUXE
[K]ernel [T]ransaction [U]ndo Transa[x]tion [E]ntry (table)
This view is very useful as it gives an indication of the state
of the transaction tables in the rollback segment headers.
The information here can be used to see the state of transactions
requiring transaction recovery do not show in <View:V$TRANSACTION>
<Event:10013> may be useful to trace transaction recovery.
–for dead transactions \instance recovery
-------------------------------------------------------------------------------
--
-- Script: rolling_back.sql
-- Purpose: to predict when transactions will finish rolling back
-- For: 9.0+
--
-- Copyright: (c) Ixora Pty Ltd
-- Author: Steve Adams
--
-------------------------------------------------------------------------------
@save_sqlplus_settings
set serveroutput on
set feedback off
prompt
prompt Looking for transactions that are rolling back ...
prompt
DECLARE
CURSOR tx
IS
SELECT s.username,
t.xidusn,
t.xidslot,
t.xidsqn,
x.ktuxesiz
FROM sys.x_$ktuxe x, sys.v_$transaction t, sys.v_$session s
WHERE x.inst_id = USERENV ('Instance')
AND x.ktuxesta = 'ACTIVE'
AND x.ktuxesiz = 1
AND t.xidusn = x.ktuxeusn
AND t.xidslot = x.ktuxeslt
AND t.xidsqn = x.ktuxesqn
AND s.saddr = t.ses_addr;
user_name VARCHAR2 (30);
xid_usn NUMBER;
xid_slot NUMBER;
xid_sqn NUMBER;
used_ublk1 NUMBER;
used_ublk2 NUMBER;
BEGIN
OPEN tx;
LOOP
FETCH tx
INTO user_name, xid_usn, xid_slot, xid_sqn, used_ublk1;
EXIT WHEN tx%NOTFOUND;
IF tx%ROWCOUNT = 1
THEN
sys.DBMS_LOCK.sleep (10);
END IF;
SELECT SUM (ktuxesiz)
INTO used_ublk2
FROM sys.x_$ktuxe
WHERE inst_id = USERENV ('Instance')
AND ktuxeusn = xid_usn
AND ktuxeslt = xid_slot
AND ktuxesqn = xid_sqn
AND ktuxesta = 'ACTIVE';
IF used_ublk2 = used_ublk1
THEN
sys.DBMS_OUTPUT.put_line (
user_name
|| '''s transaction '
|| xid_usn
|| '.'
|| xid_slot
|| '.'
|| xid_sqn
|| ' will finish rolling back at approximately '
|| TO_CHAR (
SYSDATE
+ used_ublk2 / (used_ublk1 - used_ublk2) / 6 / 60 / 24,
'HH24:MI:SS DD-MON-YYYY'));
END IF;
END LOOP;
IF user_name IS NULL
THEN
sys.DBMS_OUTPUT.put_line ('No transactions appear to be rolling back.');
END IF;
END;
/
prompt
@restore_sqlplus_settings
OR
declare
l_start number;
l_end number;
begin
select sum(ktuxesiz) into l_start from x$ktuxe where KTUXECFL ='DEAD';
dbms_lock.sleep(60);
select sum(ktuxesiz) into l_end from x$ktuxe where KTUXECFL ='DEAD';
dbms_output.put_line('time est Day:'|| round(l_end/(l_start -l_end)/60/24,2));
end;
/
references
Steve Adams ‘s article
Arup Nanda ‘s article
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。