首页 » ORACLE » Estimate of the duration of a rollback operation(估算rollback事务回滚剩余时间)

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 session—especially 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

打赏

对不起,这篇文章暂时关闭评论。