Oracle Database Smon recovery -- Disable , enable and Tuning Rollback

 

Most of time we face performance issues , we miss to  check if there  is rollback ongoing . However we come across many situations where rollback is ongoing which impacts database performance .

We can speed up and slowdown rollback seeing business hours .

 If Undo tablespace is used up and rollback is slow , we can add new undo tablespace and change default undo tablespace 

If smon recovery is causing performance issues we may opt to disable smon recovery temporarily and re-enable back after business hours .  Similarly we can speed up and reduce speed of smon recovery 

The message ‘Waiting for SMON to disable tx recovery’ will be posted in the alert log as well.

After killing the shadow process or aborting the database the v$transaction entry is lost, so you cannot estimate by examining v$transaction.used_ublk how the rollback procedure proceeds.

How to check Smon recovery : 

set linesize 100

alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

  select ktuxeusn, to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "Time", ktuxesiz, ktuxesta
   from x$ktuxe
   where ktuxecfl = 'DEAD';
  
 
select sql_text
from v$sqlarea
where address = (select sql_address from v$session where sid in (select SID 
from v$transaction,dba_rollback_segs,v$session        
where SADDR=SES_ADDR and
      XIDUSN=SEGMENT_ID and
      flag=7811));
 
 

 set linesize 100 

  alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'; 

  select usn, state, undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone "ToDo", 

             decode(cputime,0,'unknown',sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) 

              "Estimated time to complete" 

   from v$fast_start_transactions; 

 alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

 SELECT usn, state, undoblockstotal “Total”, undoblocksdone “Done”,

undoblockstotal-undoblocksdone “ToDo”,

DECODE(cputime,0,’unknown’,SYSDATE+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) “Finish at”

FROM v$fast_start_transactions;

SELECT r.NAME “RB Segment Name”, dba_seg.size_mb,

DECODE(TRUNC(SYSDATE – LOGON_TIME), 0, NULL, TRUNC(SYSDATE – LOGON_TIME) || ‘ Days’ || ‘ + ‘) ||

TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), ‘SSSSS’), ‘HH24:MI:SS’) LOGON,

v$session.SID, v$session.SERIAL#, p.SPID, v$session.process,

v$session.USERNAME, v$session.STATUS, v$session.OSUSER, v$session.MACHINE,

v$session.PROGRAM, v$session.module, action

FROM v$lock l, v$process p, v$rollname r, v$session,

(SELECT segment_name, ROUND(bytes/(1024*1024),2) size_mb FROM dba_segments

WHERE segment_type = ‘TYPE2 UNDO’ ORDER BY bytes DESC) dba_seg

WHERE l.SID = p.pid(+) AND

v$session.SID = l.SID AND

TRUNC (l.id1(+)/65536)=r.usn AND

l.TYPE(+) = ‘TX’ AND

l.lmode(+) = 6

AND r.NAME = dba_seg.segment_name

–AND v$session.username = ‘SYSTEM’

–AND status = ‘INACTIVE’

ORDER BY size_mb DESC;

SELECT TO_CHAR(s.sid)||’,’||TO_CHAR(s.serial#) sid_serial,

NVL(s.username, ‘None’) orauser,

s.program,

r.name undoseg,

t.used_ublk * TO_NUMBER(x.value)/1024||’K’ “Undo”

FROM sys.v_$rollname r,

sys.v_$session s,

sys.v_$transaction t,

sys.v_$parameter x

WHERE s.taddr = t.addr

AND r.usn = t.xidusn(+)

AND x.name = ‘db_block_size’;

SELECT state,

       UNDOBLOCKSDONE,

       UNDOBLOCKSTOTAL,

       UNDOBLOCKSDONE/UNDOBLOCKSTOTAL*100

FROM gv$fast_start_transactions;

ALTER SESSION

SET nls_date_format='dd-mon-yyyy hh24:mi:ss';

SELECT usn,

       state,

       undoblockstotal "Total",

       undoblocksdone "Done",

       undoblockstotal-undoblocksdone "ToDo",

       decode(cputime, 0, 'unknown', sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) "Estimated time to complete"

FROM v$fast_start_transactions;

SELECT a.sid,

       a.username,

       b.xidusn,

       b.used_urec,

       b.used_ublk

FROM v$session a,

     v$transaction b

WHERE a.saddr=b.ses_addr

ORDER BY 5 DESC;

Disable parallel rollback / smon recovery
 

-- to check if parallel smon recovery is in progress
select * from v$fast_start_servers;  
select pid, spid from v$process where pid in ( select pid from v$fast_start_servers);
 

-- set PID of smon

- not be killed . main smon  
select pid, program from v$process where program like '%SMON%'; -

 -- disable SMON transaction rollback/recovery
 oradebug setorapid 10  

oradebug event 10513 trace name context forever, level 2

-- kill parallel  smon processes if its exists
select 'alter system kill session '''||s.sid||','||s.serial#||''' immediate;', 'kill -9 '||spid
from v$session s, (select * from v$process
 where pid in (select pid from v$fast_start_servers)) p
 where s.paddr=p.addr;
 

-- disable parallel smon
alter system set fast_start_parallel_rollback=false;  
select * from v$fast_start_servers;
select pid from v$fast_start_servers ;
show parameter fast
 

-- enable  SMON transaction rollback/recovery
oradebug event 10513 trace name context off ; 
 

 
Speed up smon recovery
 
1)
select * from v$fast_start_servers;
If all the processes are in state RECOVERING, then you can benefit from adding more processes:
  
SQL> alter system set fast_start_parallel_rollback = high;
 
This will create parallel servers as much as 4 times the number of CPUs.
 
 
2)
Increase the parameter ‘_cleanup_rollback_entries’ to 400. The default is 100.
This parameter cannot be changed dynamically, so in order to change this the database will need to be restarted. As outlined above this is not advisable  and basically needs to be done when no alternative. This needs to be done only on suggestion of  Oracle support 

 

Rollback used by session :

Before  killing session we can check  undo  blocks  hold  by session  to   get estimation of  rollback 

select value rlbk from v$sysstat where name='user rollbacks';

 select sql_text

from v$sqlarea

where address = (select sql_address from v$session where sid in (select SID

from v$transaction,dba_rollback_segs,v$session

where SADDR=SES_ADDR and

XIDUSN=SEGMENT_ID and

flag=7811));

select sysdate,sql_text

from v$sqlarea

where address in (select sql_address

from v$session where sid in (select SID

from v$transaction,dba_rollback_segs,v$session

where SADDR=SES_ADDR and

XIDUSN=SEGMENT_ID and

bitand(flag,power(2,7))<>0));

SELECT username, terminal, osuser,

       t.start_time, r.name, t.used_ublk "ROLLB BLKS",

       DECODE(t.SPACE, 'YES', 'SPACE TX',

          DECODE(t.recursive, 'YES', 'RECURSIVE TX',

             DECODE(t.noundo, 'YES', 'NO UNDO TX', t.status)

       )) status

FROM sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s

WHERE t.xidusn = r.usn

  AND t.ses_addr = s.saddr

/

References :

 Note 238507.1: How to Disable Parallel Transaction Recovery When Parallel Txn Re very is Active 

Note 144332.1   Parallel Rollback may hang database, Parallel query servers get 100% cpu