資料庫abort-restart或事務被異常終止時，SMON需要回滾未提交的事務，如果事務過大會導致SMON消耗過多CPU甚至crash；

fast_start_parallel_rollback用於控制SMON恢復操作的並行度

FALSE

Parallel rollback is disabled

LOW

Limits the maximum degree of parallelism to 2 * CPU_COUNT

HIGH

Limits the maximum degree of parallelism to 4 * CPU_COUNT

監控指令碼

1

instance重啟前可查詢V$TRANSACTION.USED_UBLK估算rollback時間，重啟後可執行如下查詢

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;

某些情況下此檢視不起作用，此時可查詢內部表

select ktuxeusn, to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "Time", ktuxesiz, ktuxesta

from x$ktuxe

where ktuxecfl = 'DEAD';

2

檢視dead transaction的大小

select ktuxeusn USN, ktuxeslt Slot, ktuxesqn Seq, ktuxesta State, ktuxesiz Undo

from x$ktuxe

where ktuxesta <> 'INACTIVE'

and ktuxecfl like '%DEAD%'

order by ktuxesiz asc;

===========

ktuxeusn – Undo Segment Number

ktuxeslt – Slot number

ktuxesqn – Sequence

ktuxesta – State

ktuxesiz – Undo Blocks Remaining

ktuxecfl – Flag

===========

USN SLOT SEQ STATE UNDO

------- ---------- ------- ---------- ----------

9 9 335 ACTIVE 10337

3

檢視包含dead transaction的undo segment

select useg.segment_name, useg.segment_id, useg.tablespace_name, useg.status

from dba_rollback_segs useg

where useg.segment_id in (select unique ktuxeusn

from x$ktuxe

where ktuxesta <> 'INACTIVE'

and ktuxecfl like '%DEAD%');

SEGMENT_NAME SEGMENT_ID TABLESPACE_NAME STATUS

-------------------- ----------------- --------------------------- ------------

_SYSSMU9$ 9 UNDOTBS1 ONLINE

4

估算dead transaction剩餘處理時間

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;

USN STATE Total Done ToDo Estimated time to Complete

-------- ---------------- -------- -------- -------- ---------------------------------

5 RECOVERING 16207 14693 1514 11-may-2012 08:05:40

4 RECOVERED 7453 7453 0 11-may-2012 08:05:16

9 RECOVERED 10337 10337 0 11-may-2012 08:05:16

5

檢視涉及到的session

select decode(px.qcinst_id,NULL,username,' - '||lower(substr(s.program,length(s.program)-4,4) ) ) "Username",

decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,

to_char( px.server_set) "Slave Set",

to_char(s.sid) "SID",

decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",

px.req_degree "Requested DOP",

px.degree "Actual DOP"

from gv$px_session px, gv$session s

where px.sid=s.sid (+)

and px.serial#=s.serial#

order by 5 , 1 desc ;

ORA User QC/Slav Slave Set SID QC SID Requested DOP Actual DOP

---------- ----------- ---------- ------ -------- ------------------- -----------------

QC 159 159

- p007 (Slave) 1 137 159 8 8

- p006 (Slave) 1 138 159 8 8

- p005 (Slave) 1 135 159 8 8

- p004 (Slave) 1 132 159 8 8

- p003 (Slave) 1 136 159 8 8

- p002 (Slave) 1 130 159 8 8

- p001 (Slave) 1 133 159 8 8

- p000 (Slave) 1 145 159 8 8

禁用並行恢復

並行恢復有時不見得更快，PQ之間的互動開銷可能抵過並行帶來的便利

select * from v$fast_start_servers;

如果只有一條記錄state=recovering其餘全為idle，則可禁止並行恢復，此操作可線上進行，但oracle不推薦在沒有官方support下使用10513事件

1

暫停SMON的事務清除

SQL> oradebug setorapid 'SMON's Oracle PID';

SQL> oradebug event 10513 trace name context forever, level 2

2

kill所有相關子程式

select SPID from V$PROCESS where PID in (select PID from V$FAST_START_SERVERS);

kill -9 n

3

重設fast_start_parallel_rollback引數

alter system set fast_start_parallel_rollback=false;

4

恢復SMON

SQL> oradebug setorapid 'SMON's Oracle PID';

SQL> oradebug event 10513 trace name context off;

How to Disable Parallel Transaction Recovery When Parallel Txn Recovery is Active [ID 238507.1]

fast_start_parallel_rollback和並行rollback

相關文章