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)第2頁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)第3頁應(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)第4頁數(shù)據(jù)字典的鎖第4頁Oracle RDBMSOracle RDBMS也是一個(gè)也是一個(gè)“程序程序”，需要通過鎖機(jī)，需要通過鎖機(jī)制來保證程序的邏輯制來保證程序的邏輯比如創(chuàng)建一張表要插入比如創(chuàng)建一張表要插入sys.tab$sys.tab$和和sys.obj$s

3、ys.obj$等數(shù)等數(shù)據(jù)字典表據(jù)字典表類似這些操作也會(huì)在類似這些操作也會(huì)在v$lockv$lock看到看到鎖的名字是特殊的鎖的名字是特殊的華章培訓(xùn)華章培訓(xùn)第5頁管理共享內(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)的LATCHLATCHOracle

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

5、de) , mode) - -執(zhí)行某段內(nèi)核代碼執(zhí)行某段內(nèi)核代碼 release(release(latch namelatch name);); 華章培訓(xùn)華章培訓(xùn)第8頁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è)LATCHL

6、ATCH可以保護(hù)多個(gè)內(nèi)核內(nèi)存區(qū)域，但是一可以保護(hù)多個(gè)內(nèi)核內(nèi)存區(qū)域，但是一個(gè)內(nèi)核內(nèi)存區(qū)域只有一個(gè)個(gè)內(nèi)核內(nèi)存區(qū)域只有一個(gè)LATCHLATCH華章培訓(xùn)華章培訓(xùn)第9頁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 ret

7、urn FALSE Else Else 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

8、+) Function Fast_Get(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 Fa

9、st_Get(latch_name) 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)第10頁LATCH相關(guān)的時(shí)間開銷三個(gè)方面消耗的時(shí)間：三個(gè)方面消耗的時(shí)間：獲取LATCH的

10、時(shí)間(SPIN:CPU時(shí)間,SLEEP等)持有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)第11頁調(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_c

11、ount找到閂鎖：select latch# from 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)第12頁閂鎖使用的例子操作操作閂鎖等待閂鎖等待其他等待其他等待CPU 時(shí)間時(shí)間說明說明讀?。ㄗx取（10/2512)get_latch(cache buffer chains):spin1獲得閂鎖以便于查找數(shù)據(jù)獲得閂鎖以便于查找數(shù)據(jù)搜索搜索

12、 buffer chain5查找所需數(shù)據(jù)查找所需數(shù)據(jù)db_等待等待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獲取到閂鎖獲取到閂鎖

13、查找可用查找可用DB BUFFER3寫入數(shù)據(jù)寫入數(shù)據(jù)1get_latch(cache buffer chains):spin2獲取閂鎖以便將獲取閂鎖以便將cache鏈入鏈入30537華章培訓(xùn)華章培訓(xùn)第13頁如何發(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/AD

14、DMdb console/ADDM第三方腳本或工具第三方腳本或工具華章培訓(xùn)華章培訓(xùn)第14頁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ì)象分析為什么該閂鎖請求那么高，為什么等待時(shí)分析為什么該閂鎖請求那么高，為什么等待時(shí)間那么長間那么長綜合系統(tǒng)和應(yīng)用情況提出優(yōu)化建議綜合系統(tǒng)和應(yīng)用情況提出優(yōu)化建議華章培訓(xùn)華章培訓(xùn)第15頁一種特殊的閂鎖-MUTEX從從Oracle 10.2Ora

15、cle 10.2開始使用開始使用MutexMutex來實(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ì)于需要保

16、護(hù)一組內(nèi)存的情況，對(duì)于需要保護(hù)一組內(nèi)存的情況，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)第16頁STATSPACK/AWR報(bào)告Statspack/AWRStatspack/AWR報(bào)告是最好的報(bào)告是最好的LATCHLATCH問題分析工具問題分析工具Latch Activity for DBLatch Sleep breakdown for DBLatch Miss Sources

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

18、 cachelibrary cache pinrow cache objectsrow cache enqueue latch華章培訓(xùn)華章培訓(xùn)第22頁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)第23頁REDO LOG相關(guān)閂鎖REDO LOGREDO LOG相關(guān)閂鎖競爭一般由于以下原因：相關(guān)閂鎖競爭一般由于以下原因：LOG B

19、UFFER太小過于頻繁的COMMITREDO LOG的IO性能不佳LOG SWITCH過于頻繁歸檔出現(xiàn)問題或者歸檔過慢主要閂鎖主要閂鎖Redo Copy Redo allocation ：9.2:LOG_PARALLELISM10G:_log_parallelism_maxRedo writingredo on-disk SCN 華章培訓(xùn)華章培訓(xùn)第24頁Simulator lru latch與BUGBUG 2452409/BUG 5918642BUG 2452409/BUG 5918642simulator lru latchsimulator lru latch閂鎖爭用十分高閂鎖爭用十分高C

20、PUCPU使用率很高使用率很高DB CACHEDB CACHE較大，并且訪問負(fù)載較大較大，并且訪問負(fù)載較大影響多個(gè)版本影響多個(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)第25頁其他閂鎖archive controlarchive control：和歸檔目錄有關(guān)：和歸檔目錄有關(guān)process allocationprocess allocation：和進(jìn)程狀態(tài)修改有關(guān)，在短連接：和進(jìn)程狀態(tài)修改有關(guān)，在短連接的系統(tǒng)中可能存在競

21、爭的系統(tǒng)中可能存在競爭session allocationsession allocation：和：和SESSIONSESSION信息修改有關(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 valuesmod

22、ify parameters values：動(dòng)態(tài)調(diào)整參數(shù)有關(guān)：動(dòng)態(tài)調(diào)整參數(shù)有關(guān)parallel query alloc buffer,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)第26頁案例分析案例分析- -現(xiàn)象現(xiàn)象CPU使用率突然增加系統(tǒng)變得十分緩慢 procs memory page faults cpu procs memory page

23、 faults cpu r b w avm free re at pi po fr de sr in sy cs us sy id r 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 13

24、381 94 6 0 176 3 0 1438967 233571 210 25 0 0 0 0 0 10605 94632 13381 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

25、0 0 0 0 0 12534 102372 15937 92 8 0 140 1 0 1453314 232006 55 4 0 0 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)第27頁案例分析-TOP EVENTSTop 5 Timed EventsTop 5 Timed Events % Total % TotalEvent Waits Time (s) Ela TimeEvent Waits Time (s) Ela Time

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