SMON的作用還包括清理死事務:Recover Dead transaction。當服務進程在提交事務(commit)前就意外終止的話會形成死事務(dead transaction)，PMON進程負責輪詢Oracle進程，找出這類意外終止的死進程(dead process)，通知SMON將與該dead process相關的dead transaction回滾清理，并且PMON還負責恢復dead process原本持有的鎖和latch。 我們來具體了解dead transaction的恢復過程: SQL> select * from v$version;BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS for Linux: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - ProductionSQL> select * from global_name;GLOBAL_NAME -------------------------------------------------------------------------------- www.oracledatabase12g.comSQL>alter system set fast_start_parallel_rollback=false; System altered.設置10500，10046事件以跟蹤SMON進程的行為SQL> alter system set events '10500 trace name context forever,level 8'; System altered.SQL> oradebug setospid 4424 Oracle pid: 8, Unix process pid: 4424, image: oracle@rh2.oracle.com (SMON)SQL> oradebug event 10046 trace name context forever,level 8; Statement processed.在一個新的terminal中執行大批量的刪除語句，在執行一段時間后使用操作系統命令將執行該刪除操作的 服務進程kill掉，模擬一個大的dead transaction的場景SQL> delete large_rb; delete large_rb[oracle@rh2 bdump]$ kill -9 4535等待幾秒后pmon進程會找出dead process: [claim lock for dead process][lp 0x7000003c70ceff0][p 0x7000003ca63dad8.1290666][hist x9a514951]在x$ktube內部視圖中出現ktuxecfl(Transaction flags)標記為DEAD的記錄:SQL> select sum(distinct(ktuxesiz)) from x$ktuxe where ktuxecfl = 'DEAD';SUM(DISTINCT(KTUXESIZ)) -----------------------29386SQL> /SUM(DISTINCT(KTUXESIZ)) -----------------------28816以上KTUXESIZ代表事務所使用的undo塊總數(number of undo blocks used by the transaction)==================smon trace content================== SMON: system monitor process posted WAIT #0: nam='log file switch completion' ela= 0 p1=0 p2=0 p3=0 obj#=1 tim=1278243332801935 WAIT #0: nam='log file switch completion' ela= 0 p1=0 p2=0 p3=0 obj#=1 tim=1278243332815568 WAIT #0: nam='latch: row cache objects' ela= 95 address=2979418792 number=200 tries=1 obj#=1 tim=1278243333332734 WAIT #0: nam='latch: row cache objects' ela= 83 address=2979418792 number=200 tries=1 obj#=1 tim=1278243333356173 WAIT #0: nam='latch: undo global data' ela= 104 address=3066991984 number=187 tries=1 obj#=1 tim=1278243347987705 WAIT #0: nam='latch: object queue header operation' ela= 89 address=3094817048 number=131 tries=0 obj#=1 tim=1278243362468042 WAIT #0: nam='log file switch (checkpoint incomplete)' ela= 0 p1=0 p2=0 p3=0 obj#=1 tim=1278243419588202 Dead transaction 0x00c2.008.0000006d recovered by SMON ===================== PARSING IN CURSOR #3 len=358 dep=1 uid=0 oct=3 lid=0 tim=1278243423594568 hv=3186851936 ad='ae82c1b8' select smontabv.cnt,smontab.time_mp,smontab.scn,smontab.num_mappings,smontab.tim_scn_map,smontab.orig_threadfrom smon_scn_time smontab,(select max(scn) scnmax,count(*) + sum(NVL2(TIM_SCN_MAP, NUM_MAPPINGS, 0)) cntfrom smon_scn_timewhere thread = 0) smontabvwhere smontab.scn = smontabv.scnmaxand thread = 0END OF STMT PARSE #3:c=0,e=1354526,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1278243423594556 EXEC #3:c=0,e=106,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1278243423603269 FETCH #3:c=0,e=47065,p=0,cr=319,cu=0,mis=0,r=1,dep=1,og=4,tim=1278243423650375 *** 2011-06-24 21:19:25.899 WAIT #0: nam='smon timer' ela= 299999999 sleep time=300 failed=0 p3=0 obj#=1 tim=1278243716699171 kglScanDependencyHandles4Unpin():cumscan=3 cumupin=4 time=776 upinned=0 以上SMON回滾清理Dead transaction的過程從"system monitor process posted"開始到"Dead transaction 0x00c2.008.0000006d recovered by SMON"結束。另外可以看到在恢復過程中SMON先后請求了'latch: row cache objects'、'latch: undo global data'、'latch: object queue header operation'三種不同類型的latch。 現象 fast_start_parallel_rollback參數決定了SMON在回滾事務時使用的并行度，若將該參數設置為false那么并行回滾將被禁用，若設置為Low(默認值)那么會以2*CPU_COUNT數目的并行度回滾，當設置為High則4*CPU_COUNT數目的回滾進程將參與進來。當我們通過以下查詢發現系統中存在大的dead tranacation需要回滾時我們可以通過設置fast_start_parallel_rollback為HIGH來加速恢復: select sum(distinct(ktuxesiz)) from x$ktuxe where ktuxecfl = 'DEAD';==============parallel transaction recovery===============*** 2011-06-24 20:31:01.765 SMON: system monitor process posted msgflag:0x0000 (-/-/-/-/-/-/-)*** 2011-06-24 20:31:01.765 SMON: process sort segment requests begin*** 2011-06-24 20:31:01.765 SMON: process sort segment requests end*** 2011-06-24 20:31:01.765 SMON: parallel transaction recovery begin WAIT #0: nam='DFS lock handle' ela= 504 type|mode=1413545989 id1=3 id2=11 obj#=2 tim=1308918661765715 WAIT #0: nam='DFS lock handle' ela= 346 type|mode=1413545989 id1=3 id2=12 obj#=2 tim=1308918661766135 WAIT #0: nam='DFS lock handle' ela= 565 type|mode=1413545989 id1=3 id2=13 obj#=2 tim=1308918661766758 WAIT #0: nam='DFS lock handle' ela= 409 type|mode=1413545989 id1=3 id2=14 obj#=2 tim=1308918661767221 WAIT #0: nam='DFS lock handle' ela= 332 type|mode=1413545989 id1=3 id2=15 obj#=2 tim=1308918661767746 WAIT #0: nam='DFS lock handle' ela= 316 type|mode=1413545989 id1=3 id2=16 obj#=2 tim=1308918661768146 WAIT #0: nam='DFS lock handle' ela= 349 type|mode=1413545989 id1=3 id2=17 obj#=2 tim=1308918661768549 WAIT #0: nam='DFS lock handle' ela= 258 type|mode=1413545989 id1=3 id2=18 obj#=2 tim=1308918661768858 WAIT #0: nam='DFS lock handle' ela= 310 type|mode=1413545989 id1=3 id2=19 obj#=2 tim=1308918661769224 WAIT #0: nam='DFS lock handle' ela= 281 type|mode=1413545989 id1=3 id2=20 obj#=2 tim=1308918661769555*** 2011-06-24 20:31:01.769 SMON: parallel transaction recovery end 但是在real world的實踐中可以發現當fast_start_parallel_rollback= Low/High，即啟用并行回滾時常有并行進程因為各種資源互相阻塞導致回滾工作停滯的例子，當遭遇到這種問題時將fast_start_parallel_rollback設置為FALSE一般可以保證恢復工作以串行形式在較長時間內完成。 如何禁止SMON Recover Dead transaction 可以設置10513事件來臨時禁止SMON恢復死事務，這在我們做某些異常恢復的時候顯得異常有效，當然不建議在一個正常的生產環境中設置這個事件: SQL> alter system set events '10513 trace name context forever, level 2';System altered.10531 -- event disables transaction recovery which was initiated by SMONSQL> select ktuxeusn,2 to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') "Time",3 ktuxesiz,4 ktuxesta5 from x$ktuxe6 where ktuxecfl = 'DEAD';KTUXEUSN Time KTUXESIZ KTUXESTA ---------- -------------------------- ---------- ----------------17 24-JUN-2011 22:03:10 0 INACTIVE66 24-JUN-2011 22:03:10 0 INACTIVE105 24-JUN-2011 22:03:10 0 INACTIVE193 24-JUN-2011 22:03:10 33361 ACTIVE194 24-JUN-2011 22:03:10 0 INACTIVE194 24-JUN-2011 22:03:10 0 INACTIVE197 24-JUN-2011 22:03:10 20171 ACTIVE7 rows selected.SQL> /KTUXEUSN Time KTUXESIZ KTUXESTA ---------- -------------------------- ---------- ----------------17 24-JUN-2011 22:03:10 0 INACTIVE66 24-JUN-2011 22:03:10 0 INACTIVE105 24-JUN-2011 22:03:10 0 INACTIVE193 24-JUN-2011 22:03:10 33361 ACTIVE194 24-JUN-2011 22:03:10 0 INACTIVE194 24-JUN-2011 22:03:10 0 INACTIVE197 24-JUN-2011 22:03:10 20171 ACTIVE7 rows selected.================smon disabled trans recover trace==================SMON: system monitor process posted *** 2011-06-24 22:02:57.980 SMON: Event 10513 is level 2, trans recovery disabled.

總結

以上是生活随笔為你收集整理的了解你所不知道的SMON功能(五):Recover Dead transaction的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。