Check the statements of Oracle dead transactions

Check the statements of Oracle dead transactions

SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */     2  KTUXESTA Status,    KTUXECFL Flags ,KTUXESIZ   3  FROM x$ktuxe  4  WHERE ktuxesta!='INACTIVE';  KTUXEUSN   KTUXESLT   KTUXESQN STATUS           FLAGS                      KTUXESIZ---------- ---------- ---------- ---------------- ------------------------ ----------        13          5      47447 ACTIVE           DEAD                        2819919        39          0        502 ACTIVE           NONE                              1        43         45        480 ACTIVE           NONE                              0

SQL>/KTUXEUSN KTUXESLT KTUXESQN STATUS FLAGS KTUXESIZ ---------- ---------------- ------------------------ ---------- 13 5 47447 ACTIVE DEAD 2819919 <---- the value is not reduced. 39 0 502 active none 1 43 45 480 active none 0

Query v $ px_session and v $ fast_start_servers. It shows that many parallel processes are in rollback. Based on previous engineering experience:

So change

SQL>alter system set fast_start_parallel_rollback=false scope=both;

Then run again

SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN,/* Transaction ID */2 KTUXESTA Status, KTUXECFL Flags, KTUXESIZ 3 FROM x $ ktuxe 4 WHERE ktuxesta! = 'Inactivity '; ktuxeusn ktuxeslt release status flags running ---------- release failed ---------- 13 5 47447 active dead 2033516 35 29 502 active none 1 SQL>/release KTUXESLT release STATUS FLAGS running ------------ release failed ---------- 13 5 47447 active dead 2033433 <--- the value keeps decreasing. 35 29 502 active none 1

Run the following script to check the Estimated rollback time (in days ):

SQL> set serveroutput onSQL> declare 2   l_start number; 3   l_end number; 4   begin 5   select ktuxesiz into l_startfrom x$ktuxe where KTUXEUSN=13 and KTUXESLT=5; 6   dbms_lock.sleep(60); 7   select ktuxesiz into l_endfrom x$ktuxe where KTUXEUSN=13 and KTUXESLT=5; 8   dbms_output.put_line('time estDay:'|| round(l_end/(l_start -l_end)/60/24,2));  9   end; 10   /time est Day:.21

24*0.21 = 5.04 hours. That is, the rollback is expected to be completed within 5.04 hours.

Note: in other environments, replace KTUXEUSN with KTUXESLT = 5.