ANBOB™

After killing a large running transaction, either by killing the process or aborting the database, the database may appear to hang and/or the SMON background process is taking all the available cpu.Usually dead transaction is by PMON process cleanup, SMON instead of doing transaction recovery is a hidden parameter ‘_cleanup_rollback_entries’ effected, Basically SMON is in the progress of rolling back this terminated large transaction, which can be time consuming. As a result of this users often try to restart the database, but because a shutdown immediate will hang, the database is often aborted. Please note that stopping the database will actually make things worse and will not decrease the amount of work SMON needs to perfom to complete the rollback.

_cleanup_rollback_entries

This is an integer parameter that defaults to 100. The parameter is used to specify the maximum number of undo (rollback) records to apply when performing transaction
rollback under PMON.
If the transaction rollback hits the limit then PMON aborts the recovery and signals SMON so that it can perform the recovery instead.If you need to recovery the dead transaction record of more than 100(_cleanup_rollback_entries parameter value), PMON  only responsible for cleaning up dead session, which is responsible for the SMON recovery dead transaction.

It also looks like SMON may be influenced by this parameter too, if it is asked to do a quick pass (KTUREC_QP) recovery of a transaction.
In the Oracle 8.0 Server Reference, the non-hidden version of this parameter is described as follows:
CLEANUP_ROLLBACK_ENTRIES specifies the number of undo records processed at one time when rolling back a transaction. Prevents long transactions

_CLEANUP_ROLLBACK_ENTRIES

Parameter type:integer
Parameter class:static
Default value:20

CLEANUP_ROLLBACK_ENTRIES specifies the number of undo records processed at one time when rolling back a transaction. Prevents long transactions from freezing out shorter transactions that also need to be rolled back. Normally this parameter will not need modification.

How to know trasaction is rollback?

undocument v$transaction.flag can be identifies that the current transaction is rolling back.

It’s a bitmap, so, look at the two numbers in binary:
7811 = 0001 1110 1000 0011 = active transaction, no rollback
7683 = 0001 1110 0000 0011 = rollback in progress

Note that the numbers only differ by one bit. Looking at the numbers in decimal, it’s not obvious, but in binary it sure is.

So, Starting with the right, counting left, and starting with the first bit on the right is bit 0, we can see that bit 7 is the bit that identifies a transaction that’s being rolled back. So, to check if bit 7 is set, you can write:
“where bitand(flag,power(2,7))<>0”

select s.sid,machine,s.sql_id,start_time, username, r.name,  ubafil, ubablk, t.status, (used_ublk*p.value)/1024 blk, used_urec,decode(bitand(t.flag,power(2,7)),0, 'Running','Rolling back') tx_state
  from v$transaction t, v$rollname r, v$session s, v$parameter p
  where xidusn=usn
  and s.saddr=t.ses_addr
  and p.name='db_block_size'order by 1; 

How to check the rollback progress?

The following queries are available to monitor the progress of the transaction recovery:

  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;

Run the above query several times in a row, this will give you a good idea on how SMON is progressing.

more Estimate of the duration of a rollback operation(估算rollback事务回滚剩余时间)

1,There are cases where parallel transaction recovery is not as fast as serial transaction recovery, because the pq slaves are interfering with each other. To check the Parallel Recovery processes and their state, run the following query:

select * from v$fast_start_servers;

Column STATE shows the state of the server being IDLE or RECOVERING, if only 1 process is in state RECOVERING while the other processes are in state IDLE, then you should disable Parallel Transaction Recovery. How to do this is outlined in the following note:

note 238507.1: How to Disable Parallel Transaction Recovery When Parallel Txn Recovery is Active

How to Speed up rollback?

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. defalut value is ‘low’, that will create parallel servers as much as 2 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 in the current situation, but there is basically no alternative.
If the database is is going to be restarted, then you best also specify

fast_start_parallel_rollback=false

3. As a last resort, if the above has been verified, we can disable SMON from performing the recovery at this time. This will allow the database to operate as normal, and you can choose to let the recovery take place at a later time when it is more convenient. Because the clean-up of the large transaction is actually put on hold it is important, and I cannot stress this enough, to disable this event again to let SMON finish its work.

init.ora:
event=”10513 trace name context forever, level 2″

You can also dynamically prevent SMON to perform the transaction recovery, for instance when you want to cancel a long running update without doing a rollback (at that time).
However, please note that the rollback has still to be done at a later time to assure a consistent state of the database.

You can postpone the transaction recovery to a more convenient time as below:

— Determine the SMON process id:

SQL> select pid, program from v$process where program like ‘%SMON%’;

PID PROGRAM
———- ————————————————
6 oracle@stsun7 (SMON)

Disable SMON transaction cleanup:

SQL> oradebug setorapid <SMON's Oracle PID>
SQL> oradebug event 10513 trace name context forever, level 2

To enable the transaction recovery again:

SQL> oradebug setorapid <SMON's Oracle PID>
SQL> oradebug event 10513 trace name context off

— Applies to 8i – 11g r2 —

References note 414242.1

打赏

微信扫一扫，打赏作者吧～