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

 

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 

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));
 
 

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