Oracle數(shù)據(jù)庫性能優(yōu)化實(shí)務(wù) 閂鎖及閂鎖優(yōu)化

1、Oracle數(shù)據(jù)庫性能優(yōu)化實(shí)務(wù)第四講：閂鎖及閂鎖優(yōu)化主講人：白鱔華章培訓(xùn)網(wǎng)、華章培訓(xùn)網(wǎng)版權(quán)所有華章培訓(xùn)華章培訓(xùn)Oracle的鎖第2頁應(yīng)用級(jí)鎖：應(yīng)用中對(duì)表等資源進(jìn)行鎖定，保證業(yè)應(yīng)用級(jí)鎖：應(yīng)用中對(duì)表等資源進(jìn)行鎖定，保證業(yè)務(wù)邏輯正確性務(wù)邏輯正確性數(shù)據(jù)字典鎖：數(shù)據(jù)字典鎖：Oracle RDBMSOracle RDBMS內(nèi)核程序員使用的用內(nèi)核程序員使用的用來保證數(shù)據(jù)字典訪問邏輯正確性的鎖來保證數(shù)據(jù)字典訪問邏輯正確性的鎖內(nèi)存控制鎖：用來保護(hù)內(nèi)存控制鎖：用來保護(hù)OracleOracle內(nèi)部數(shù)據(jù)結(jié)構(gòu)的鎖內(nèi)部數(shù)據(jù)結(jié)構(gòu)的鎖（LATCH,MUTEXLATCH,MUTEX）華章培訓(xùn)華章培訓(xùn)應(yīng)用程序的鎖第3頁應(yīng)用程序鎖

2、的優(yōu)化取決于應(yīng)用軟件編寫人員應(yīng)用程序鎖的優(yōu)化取決于應(yīng)用軟件編寫人員使用使用v$lockv$lock可以觀察鎖的情況（可以觀察鎖的情況（TM,TXTM,TX）?/rdbms/admin/catblock.sql ?/rdbms/admin/catblock.sql 使用dba_waiters,dba_blockers來查找阻塞華章培訓(xùn)華章培訓(xùn)數(shù)據(jù)字典的鎖第4頁Oracle RDBMSOracle RDBMS也是一個(gè)也是一個(gè)“程序程序”，需要通過鎖機(jī)，需要通過鎖機(jī)制來保證程序的邏輯制來保證程序的邏輯比如創(chuàng)建一張表要插入比如創(chuàng)建一張表要插入sys.tab$sys.tab$和和sys.obj$sys.

3、obj$等數(shù)等數(shù)據(jù)字典表據(jù)字典表類似這些操作也會(huì)在類似這些操作也會(huì)在v$lockv$lock看到看到鎖的名字是特殊的鎖的名字是特殊的華章培訓(xùn)華章培訓(xùn)管理共享內(nèi)存的鎖OracleOracle的內(nèi)存結(jié)構(gòu)也必須進(jìn)行同步互斥保護(hù)的內(nèi)存結(jié)構(gòu)也必須進(jìn)行同步互斥保護(hù)OracleOracle通過對(duì)訪問核心內(nèi)存結(jié)構(gòu)的代碼的執(zhí)行進(jìn)通過對(duì)訪問核心內(nèi)存結(jié)構(gòu)的代碼的執(zhí)行進(jìn)行控制來達(dá)到間接的保護(hù)核心內(nèi)存的目的行控制來達(dá)到間接的保護(hù)核心內(nèi)存的目的每個(gè)每個(gè)LATCHLATCH都涉及到特定的內(nèi)核代碼都涉及到特定的內(nèi)核代碼要執(zhí)行某個(gè)代碼，必須先獲得相應(yīng)的要執(zhí)行某個(gè)代碼，必須先獲得相應(yīng)的LATCHLATCHOracleOracle

4、通過上述過程，可以保證核心內(nèi)存訪問的通過上述過程，可以保證核心內(nèi)存訪問的高效性和一致性高效性和一致性華章培訓(xùn)華章培訓(xùn)什么是閂鎖LatchLatch可以保證可以保證OracleOracle串行訪問核心內(nèi)存串行訪問核心內(nèi)存LatchLatch必須十分高效必須十分高效LatchLatch通過簡單的底層的技術(shù)實(shí)現(xiàn)，盡可能使用操通過簡單的底層的技術(shù)實(shí)現(xiàn)，盡可能使用操作系統(tǒng)的底層技術(shù)作系統(tǒng)的底層技術(shù)LatchLatch的申請(qǐng)不通過隊(duì)列機(jī)制的申請(qǐng)不通過隊(duì)列機(jī)制華章培訓(xùn)華章培訓(xùn)閂鎖的使用If get_latch(If get_latch(latch namelatch name, mode) , mode)

5、- -執(zhí)行某段內(nèi)核代碼執(zhí)行某段內(nèi)核代碼 release(release(latch namelatch name);); 華章培訓(xùn)華章培訓(xùn)MUTLI-LATCHOracleOracle對(duì)特定的內(nèi)核內(nèi)存只使用一個(gè)對(duì)特定的內(nèi)核內(nèi)存只使用一個(gè)LATCHLATCH來保護(hù)來保護(hù)如果某些內(nèi)核內(nèi)存太大，如果某些內(nèi)核內(nèi)存太大，OracleOracle會(huì)分為一些子區(qū)域會(huì)分為一些子區(qū)域，每個(gè)子區(qū)域由一個(gè)，每個(gè)子區(qū)域由一個(gè)LATCHLATCH來管理，比如：來管理，比如：多個(gè)相同功能的LRU LATCH用來保護(hù)多條LRU鏈多個(gè)HASH LATCH來保護(hù)多條HASH 鏈一個(gè)一個(gè)LATCHLATCH可以保護(hù)多個(gè)內(nèi)核內(nèi)存區(qū)

6、域，但是一個(gè)可以保護(hù)多個(gè)內(nèi)核內(nèi)存區(qū)域，但是一個(gè)內(nèi)核內(nèi)存區(qū)域只有一個(gè)內(nèi)核內(nèi)存區(qū)域只有一個(gè)LATCHLATCH華章培訓(xùn)華章培訓(xùn)LATCH的模擬算法Function Get_Latch(latch_name,mode)Function Get_Latch(latch_name,mode) If Mode eq immediate If Mode eq immediate If Fast_Get(latch_name) If Fast_Get(latch_name) return TRUE return TRUE Else Else return FALSE return FALSE Else Els

7、e If Fast_Get(latch_name) If Fast_Get(latch_name) return TRUE return TRUE Else Else while ( 1 = 1 ) while ( 1 = 1 ) if Spin_Get(latch_name) if Spin_Get(latch_name) return TRUE return TRUE Else Else Register_Event(“l(fā)atch: Register_Event(“l(fā)atch: $latch”)$latch”) Sleep(try+) Sleep(try+) Function Fast_G

8、et(latch_name)Function Fast_Get(latch_name) If try_to_get_latch(latch_name) If try_to_get_latch(latch_name) return TRUE return TRUE Else Else return FALSE return FALSE Function Spin_Get(latch_name)Function Spin_Get(latch_name) for i = 1 to _spin_count for i = 1 to _spin_count If Fast_Get(latch_name)

9、 If Fast_Get(latch_name) return TRUE return TRUE Function Sleep(try)Function Sleep(try) sleeptime = sleeptime = decode(try,0,0,1,10,2,20,3,40,4,80,.2000) decode(try,0,0,1,10,2,20,3,40,4,80,.2000) sleep(sleeptime) sleep(sleeptime) 華章培訓(xùn)華章培訓(xùn)LATCH相關(guān)的時(shí)間開銷三個(gè)方面消耗的時(shí)間：三個(gè)方面消耗的時(shí)間：獲取LATCH的時(shí)間(SPIN:CPU時(shí)間,SLEEP等)持

10、有LATCH的時(shí)間(內(nèi)核代碼:cpu時(shí)間,OS調(diào)用,鎖等待)LATCH釋放的時(shí)間(內(nèi)核代碼:cpu時(shí)間)注意的要點(diǎn)：注意的要點(diǎn)：spin消耗CPU資源，因此提高_(dá)spin_count會(huì)加大CPU開銷spin不產(chǎn)生等待事件sleep不消耗cpu時(shí)間，會(huì)記錄latch free等待華章培訓(xùn)華章培訓(xùn)調(diào)整_SPIN_COUNT調(diào)整調(diào)整_SPIN_COUNT_SPIN_COUNT是十分危險(xiǎn)的動(dòng)作是十分危險(xiǎn)的動(dòng)作Oracle 9i R2Oracle 9i R2開始支持按照開始支持按照CLASSCLASS分類設(shè)置分類設(shè)置_spin_count_spin_count找到閂鎖：select latch# fro

11、m v$latchname where name=cache buffers chains; alter system set _latch_classes=98:0 scope=spfile;alter system set _latch_class_0=1800 scope=spfile;華章培訓(xùn)華章培訓(xùn)閂鎖使用的例子操作操作閂鎖等待閂鎖等待其他等待其他等待CPU 時(shí)間時(shí)間說明說明讀取（讀?。?0/2512)get_latch(cache buffer chains):spin1獲得閂鎖以便于查找數(shù)據(jù)獲得閂鎖以便于查找數(shù)據(jù)搜索搜索 buffer chain5查找所需數(shù)據(jù)查找所需數(shù)據(jù)db_f

12、ile_sequential_read等待等待5正常的正常的IO時(shí)間時(shí)間get_latch(cache buffer lru chains):spin10獲取閂鎖獲取閂鎖get_latch(cache buffer lru chains):sleep10獲取不到，休眠獲取不到，休眠get_latch(cache buffer lru chains):spin10繼續(xù)獲取繼續(xù)獲取get_latch(cache buffer lru chains):sleep20再次休眠再次休眠get_latch(cache buffer lru chains):spin5獲取到閂鎖獲取到閂鎖查找可用查找可用DB

13、 BUFFER3寫入數(shù)據(jù)寫入數(shù)據(jù)1get_latch(cache buffer chains):spin2獲取閂鎖以便將獲取閂鎖以便將cache鏈入鏈入30537華章培訓(xùn)華章培訓(xùn)如何發(fā)現(xiàn)閂鎖等待v$session_wait,v$latch,v$latch_childrenv$session_wait,v$latch,v$latch_children等視等視圖圖StatspackStatspack報(bào)告報(bào)告/AWR/AWR報(bào)告：最好的工具報(bào)告：最好的工具OEM performance managerOEM performance managerdb console/ADDMdb console/A

14、DDM第三方腳本或工具第三方腳本或工具華章培訓(xùn)華章培訓(xùn)LATCH分析的主要思路理解理解LATCHLATCH的基本原理和算法的基本原理和算法發(fā)現(xiàn)發(fā)現(xiàn)LATCH FREELATCH FREE問題問題找出存在嚴(yán)重沖突的找出存在嚴(yán)重沖突的LATCHLATCH找出存在問題的找出存在問題的LATCHLATCH相關(guān)的內(nèi)核對(duì)象相關(guān)的內(nèi)核對(duì)象分析為什么該閂鎖請(qǐng)求那么高，為什么等待時(shí)間分析為什么該閂鎖請(qǐng)求那么高，為什么等待時(shí)間那么長那么長綜合系統(tǒng)和應(yīng)用情況提出優(yōu)化建議綜合系統(tǒng)和應(yīng)用情況提出優(yōu)化建議華章培訓(xùn)華章培訓(xùn)一種特殊的閂鎖-MUTEX從從Oracle 10.2Oracle 10.2開始使用開始使用MutexM

15、utex來實(shí)現(xiàn)部分內(nèi)存的保護(hù)來實(shí)現(xiàn)部分內(nèi)存的保護(hù)10.2.0.210.2.0.2開始開始CURSORCURSOR操作中替代操作中替代library cache,library library cache,library cach pincach pinMutexMutex用來保護(hù)內(nèi)存的訪問用來保護(hù)內(nèi)存的訪問, ,保證內(nèi)存訪問的串行性保證內(nèi)存訪問的串行性和和LATCHLATCH不同，一個(gè)不同，一個(gè)MutexMutex只保護(hù)一塊內(nèi)存只保護(hù)一塊內(nèi)存比比LATCHLATCH開銷更小開銷更小LATCH：150-200條指令MUTEX：35-40條指令對(duì)于需要保護(hù)一組內(nèi)存的情況，對(duì)于需要保護(hù)一組內(nèi)存的情況

16、，LATCHLATCH效率更高效率更高M(jìn)UTEXMUTEX也支持也支持OWIOWI在不支持在不支持CASCAS的平臺(tái)上慎用的平臺(tái)上慎用MUTEX LATCHMUTEX LATCH設(shè)置_kks_use_mutex_pin比如HP-UX PA-RISC華章培訓(xùn)華章培訓(xùn)STATSPACK/AWR報(bào)告Statspack/AWRStatspack/AWR報(bào)告是最好的報(bào)告是最好的LATCHLATCH問題分析工具問題分析工具Latch Activity for DBLatch Sleep breakdown for DBLatch Miss Sources for DBChild Latch Statist

17、ics DB華章培訓(xùn)華章培訓(xùn)閂鎖總體情況華章培訓(xùn)華章培訓(xùn)閂鎖休眠情況分解華章培訓(xùn)華章培訓(xùn)閂鎖問題源分析華章培訓(xùn)華章培訓(xùn)子閂鎖情況華章培訓(xùn)華章培訓(xùn)共享池相關(guān)的閂鎖共享池相關(guān)共享池相關(guān)LATCHLATCH一般和共享池不足或者分析過于一般和共享池不足或者分析過于頻繁有關(guān)頻繁有關(guān)如果共享池命中率不高或者共享池使用率接近于如果共享池命中率不高或者共享池使用率接近于100%100%使用，需要加大共享池使用，需要加大共享池共享池碎片問題也會(huì)加大閂鎖競爭共享池碎片問題也會(huì)加大閂鎖競爭shared poollibrary cachelibrary cache pinrow cache objectsrow ca

18、che enqueue latch華章培訓(xùn)華章培訓(xùn)DB CACHE相關(guān)閂鎖和和LRU CHAINSLRU CHAINS或者或者HASH CHAINSHASH CHAINS相關(guān)相關(guān)cache buffer handlescache buffers chainscache buffers lru chainmultiblock read objects華章培訓(xùn)華章培訓(xùn)REDO LOG相關(guān)閂鎖REDO LOGREDO LOG相關(guān)閂鎖競爭一般由于以下原因：相關(guān)閂鎖競爭一般由于以下原因：LOG BUFFER太小過于頻繁的COMMITREDO LOG的IO性能不佳LOG SWITCH過于頻繁歸檔出現(xiàn)問題或

19、者歸檔過慢主要閂鎖主要閂鎖Redo Copy Redo allocation ：9.2:LOG_PARALLELISM10G:_log_parallelism_maxRedo writingredo on-disk SCN 華章培訓(xùn)華章培訓(xùn)Simulator lru latch與BUGBUG 2452409/BUG 5918642BUG 2452409/BUG 5918642simulator lru latchsimulator lru latch閂鎖爭用十分高閂鎖爭用十分高CPUCPU使用率很高使用率很高DB CACHEDB CACHE較大，并且訪問負(fù)載較大較大，并且訪問負(fù)載較大影響多個(gè)版

20、本影響多個(gè)版本9.2.0.510.1.0.210.2.0.311.1.0.6解決方案解決方案9.2,10.1:STATISTICS_LEVEL = BASIC10.2:db_cache_advice = OFF華章培訓(xùn)華章培訓(xùn)其他閂鎖archive controlarchive control：和歸檔目錄有關(guān)：和歸檔目錄有關(guān)process allocationprocess allocation：和進(jìn)程狀態(tài)修改有關(guān)，在短連接的：和進(jìn)程狀態(tài)修改有關(guān)，在短連接的系統(tǒng)中可能存在競爭系統(tǒng)中可能存在競爭session allocationsession allocation：和：和SESSIONSESS

21、ION信息修改有關(guān)信息修改有關(guān)sort extent poolsort extent pool：和硬盤排序有關(guān)：和硬盤排序有關(guān)child cursor hash tablechild cursor hash table：和：和SQLSQL分析以及分析以及CURSOR CURSOR VERSIONVERSION有關(guān)有關(guān)enqueue hash chainsenqueue hash chains和鎖的管理有關(guān)和鎖的管理有關(guān)modify parameters valuesmodify parameters values：動(dòng)態(tài)調(diào)整參數(shù)有關(guān)：動(dòng)態(tài)調(diào)整參數(shù)有關(guān)parallel query alloc bu

22、ffer,parallel query stats :parallel query alloc buffer,parallel query stats :和并行查詢有關(guān)和并行查詢有關(guān)GESGES* *：和全局鎖有關(guān)：和全局鎖有關(guān)GCSGCS* *：和全局：和全局CACHECACHE有關(guān)有關(guān)華章培訓(xùn)華章培訓(xùn)案例分析案例分析- -現(xiàn)象現(xiàn)象CPU使用率突然增加系統(tǒng)變得十分緩慢 procs memory page faults cpu procs memory page faults cpu r b w avm free re at pi po fr de sr in sy cs us sy id r

23、 b w avm free re at pi po fr de sr in sy cs us sy id 176 3 0 1438967 235527 87 13 0 0 0 0 0 8080 51050 6161 40 4 56 176 3 0 1438967 235527 87 13 0 0 0 0 0 8080 51050 6161 40 4 56 176 3 0 1438967 233571 210 25 0 0 0 0 0 10605 94632 13381 94 6 0 176 3 0 1438967 233571 210 25 0 0 0 0 0 10605 94632 1338

24、1 94 6 0 140 1 0 1453314 234363 153 20 0 0 0 0 0 10949 93765 13758 93 7 0 140 1 0 1453314 234363 153 20 0 0 0 0 0 10949 93765 13758 93 7 0 140 1 0 1453314 231984 107 9 0 0 0 0 0 12534 102372 15937 92 8 0 140 1 0 1453314 231984 107 9 0 0 0 0 0 12534 102372 15937 92 8 0 140 1 0 1453314 232006 55 4 0 0

25、 0 0 0 13046 105209 17122 93 7 0 140 1 0 1453314 232006 55 4 0 0 0 0 0 13046 105209 17122 93 7 0華章培訓(xùn)華章培訓(xùn)案例分析-TOP EVENTSTop 5 Timed EventsTop 5 Timed Events % Total % TotalEvent Waits Time (s) Ela TimeEvent Waits Time (s) Ela Time- - - - - - -latch free 951,180 467,356 73.70latch free 951,180 467,356

26、 73.70CPU time 53,559 8.45CPU time 53,559 8.45db file sequential read 9,592,369 53,012 8.36db file sequential read 9,592,369 53,012 8.36enqueue 10,751 29,580 4.66enqueue 10,751 29,580 4.66db file scattered read 2,154,819 18,001 2.84db file scattered read 2,154,819 18,001 2.84 - -平均每次閂鎖等待時(shí)間高達(dá)平均每次閂鎖等待時(shí)間高達(dá)481481毫秒毫秒華章培訓(xùn)華章培訓(xùn)案例分析：閂鎖情況 Pct Avg Wait Pct Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Get Get Slps Time NoWait NoWaitLatch Requests Miss /Miss