Oracle数据库性能优化实务 闩锁及闩锁优化

1、Oracle数据库性能优化实务第四讲：闩锁及闩锁优化主讲人：白鳝华章培训网、华章培训网版权所有华章培训华章培训Oracle的锁第2页应用级锁：应用中对表等资源进行锁定，保证业应用级锁：应用中对表等资源进行锁定，保证业务逻辑正确性务逻辑正确性数据字典锁：数据字典锁：Oracle RDBMSOracle RDBMS内核程序员使用的用内核程序员使用的用来保证数据字典访问逻辑正确性的锁来保证数据字典访问逻辑正确性的锁内存控制锁：用来保护内存控制锁：用来保护OracleOracle内部数据结构的锁内部数据结构的锁（LATCH,MUTEXLATCH,MUTEX）华章培训华章培训应用程序的锁第3页应用程序锁

2、的优化取决于应用软件编写人员应用程序锁的优化取决于应用软件编写人员使用使用v$lockv$lock可以观察锁的情况（可以观察锁的情况（TM,TXTM,TX）?/rdbms/admin/catblock.sql ?/rdbms/admin/catblock.sql 使用dba_waiters,dba_blockers来查找阻塞华章培训华章培训数据字典的锁第4页Oracle RDBMSOracle RDBMS也是一个也是一个“程序程序”，需要通过锁机，需要通过锁机制来保证程序的逻辑制来保证程序的逻辑比如创建一张表要插入比如创建一张表要插入sys.tab$sys.tab$和和sys.obj$sys.

3、obj$等数等数据字典表据字典表类似这些操作也会在类似这些操作也会在v$lockv$lock看到看到锁的名字是特殊的锁的名字是特殊的华章培训华章培训管理共享内存的锁OracleOracle的内存结构也必须进行同步互斥保护的内存结构也必须进行同步互斥保护OracleOracle通过对访问核心内存结构的代码的执行进通过对访问核心内存结构的代码的执行进行控制来达到间接的保护核心内存的目的行控制来达到间接的保护核心内存的目的每个每个LATCHLATCH都涉及到特定的内核代码都涉及到特定的内核代码要执行某个代码，必须先获得相应的要执行某个代码，必须先获得相应的LATCHLATCHOracleOracle

4、通过上述过程，可以保证核心内存访问的通过上述过程，可以保证核心内存访问的高效性和一致性高效性和一致性华章培训华章培训什么是闩锁LatchLatch可以保证可以保证OracleOracle串行访问核心内存串行访问核心内存LatchLatch必须十分高效必须十分高效LatchLatch通过简单的底层的技术实现，尽可能使用操通过简单的底层的技术实现，尽可能使用操作系统的底层技术作系统的底层技术LatchLatch的申请不通过队列机制的申请不通过队列机制华章培训华章培训闩锁的使用If get_latch(If get_latch(latch namelatch name, mode) , mode)

5、- -执行某段内核代码执行某段内核代码 release(release(latch namelatch name);); 华章培训华章培训MUTLI-LATCHOracleOracle对特定的内核内存只使用一个对特定的内核内存只使用一个LATCHLATCH来保护来保护如果某些内核内存太大，如果某些内核内存太大，OracleOracle会分为一些子区域会分为一些子区域，每个子区域由一个，每个子区域由一个LATCHLATCH来管理，比如：来管理，比如：多个相同功能的LRU LATCH用来保护多条LRU链多个HASH LATCH来保护多条HASH 链一个一个LATCHLATCH可以保护多个内核内存区

6、域，但是一个可以保护多个内核内存区域，但是一个内核内存区域只有一个内核内存区域只有一个LATCHLATCH华章培训华章培训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(“latch: Register_Event(“latch: $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) 华章培训华章培训LATCH相关的时间开销三个方面消耗的时间：三个方面消耗的时间：获取LATCH的时间(SPIN:CPU时间,SLEEP等)持

10、有LATCH的时间(内核代码:cpu时间,OS调用,锁等待)LATCH释放的时间(内核代码:cpu时间)注意的要点：注意的要点：spin消耗CPU资源，因此提高_spin_count会加大CPU开销spin不产生等待事件sleep不消耗cpu时间，会记录latch free等待华章培训华章培训调整_SPIN_COUNT调整调整_SPIN_COUNT_SPIN_COUNT是十分危险的动作是十分危险的动作Oracle 9i R2Oracle 9i R2开始支持按照开始支持按照CLASSCLASS分类设置分类设置_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;华章培训华章培训闩锁使用的例子操作操作闩锁等待闩锁等待其他等待其他等待CPU 时间时间说明说明读取（读取（10/2512)get_latch(cache buffer chains):spin1获得闩锁以便于查找数据获得闩锁以便于查找数据搜索搜索 buffer chain5查找所需数据查找所需数据db_f

12、ile_sequential_read等待等待5正常的正常的IO时间时间get_latch(cache buffer lru chains):spin10获取闩锁获取闩锁get_latch(cache buffer lru chains):sleep10获取不到，休眠获取不到，休眠get_latch(cache buffer lru chains):spin10继续获取继续获取get_latch(cache buffer lru chains):sleep20再次休眠再次休眠get_latch(cache buffer lru chains):spin5获取到闩锁获取到闩锁查找可用查找可用DB

13、 BUFFER3写入数据写入数据1get_latch(cache buffer chains):spin2获取闩锁以便将获取闩锁以便将cache链入链入30537华章培训华章培训如何发现闩锁等待v$session_wait,v$latch,v$latch_childrenv$session_wait,v$latch,v$latch_children等视等视图图StatspackStatspack报告报告/AWR/AWR报告：最好的工具报告：最好的工具OEM performance managerOEM performance managerdb console/ADDMdb console/A

14、DDM第三方脚本或工具第三方脚本或工具华章培训华章培训LATCH分析的主要思路理解理解LATCHLATCH的基本原理和算法的基本原理和算法发现发现LATCH FREELATCH FREE问题问题找出存在严重冲突的找出存在严重冲突的LATCHLATCH找出存在问题的找出存在问题的LATCHLATCH相关的内核对象相关的内核对象分析为什么该闩锁请求那么高，为什么等待时间分析为什么该闩锁请求那么高，为什么等待时间那么长那么长综合系统和应用情况提出优化建议综合系统和应用情况提出优化建议华章培训华章培训一种特殊的闩锁-MUTEX从从Oracle 10.2Oracle 10.2开始使用开始使用MutexM

15、utex来实现部分内存的保护来实现部分内存的保护10.2.0.210.2.0.2开始开始CURSORCURSOR操作中替代操作中替代library cache,library library cache,library cach pincach pinMutexMutex用来保护内存的访问用来保护内存的访问, ,保证内存访问的串行性保证内存访问的串行性和和LATCHLATCH不同，一个不同，一个MutexMutex只保护一块内存只保护一块内存比比LATCHLATCH开销更小开销更小LATCH：150-200条指令MUTEX：35-40条指令对于需要保护一组内存的情况，对于需要保护一组内存的情况

16、，LATCHLATCH效率更高效率更高MUTEXMUTEX也支持也支持OWIOWI在不支持在不支持CASCAS的平台上慎用的平台上慎用MUTEX LATCHMUTEX LATCH设置_kks_use_mutex_pin比如HP-UX PA-RISC华章培训华章培训STATSPACK/AWR报告Statspack/AWRStatspack/AWR报告是最好的报告是最好的LATCHLATCH问题分析工具问题分析工具Latch Activity for DBLatch Sleep breakdown for DBLatch Miss Sources for DBChild Latch Statist

17、ics DB华章培训华章培训闩锁总体情况华章培训华章培训闩锁休眠情况分解华章培训华章培训闩锁问题源分析华章培训华章培训子闩锁情况华章培训华章培训共享池相关的闩锁共享池相关共享池相关LATCHLATCH一般和共享池不足或者分析过于一般和共享池不足或者分析过于频繁有关频繁有关如果共享池命中率不高或者共享池使用率接近于如果共享池命中率不高或者共享池使用率接近于100%100%使用，需要加大共享池使用，需要加大共享池共享池碎片问题也会加大闩锁竞争共享池碎片问题也会加大闩锁竞争shared poollibrary cachelibrary cache pinrow cache objectsrow ca

18、che enqueue latch华章培训华章培训DB CACHE相关闩锁和和LRU CHAINSLRU CHAINS或者或者HASH CHAINSHASH CHAINS相关相关cache buffer handlescache buffers chainscache buffers lru chainmultiblock read objects华章培训华章培训REDO LOG相关闩锁REDO LOGREDO LOG相关闩锁竞争一般由于以下原因：相关闩锁竞争一般由于以下原因：LOG BUFFER太小过于频繁的COMMITREDO LOG的IO性能不佳LOG SWITCH过于频繁归档出现问题或

19、者归档过慢主要闩锁主要闩锁Redo Copy Redo allocation ：9.2:LOG_PARALLELISM10G:_log_parallelism_maxRedo writingredo on-disk SCN 华章培训华章培训Simulator lru latch与BUGBUG 2452409/BUG 5918642BUG 2452409/BUG 5918642simulator lru latchsimulator lru latch闩锁争用十分高闩锁争用十分高CPUCPU使用率很高使用率很高DB CACHEDB CACHE较大，并且访问负载较大较大，并且访问负载较大影响多个版

20、本影响多个版本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华章培训华章培训其他闩锁archive controlarchive control：和归档目录有关：和归档目录有关process allocationprocess allocation：和进程状态修改有关，在短连接的：和进程状态修改有关，在短连接的系统中可能存在竞争系统中可能存在竞争session allocationsession allocation：和：和SESSIONSESS

21、ION信息修改有关信息修改有关sort extent poolsort extent pool：和硬盘排序有关：和硬盘排序有关child cursor hash tablechild cursor hash table：和：和SQLSQL分析以及分析以及CURSOR CURSOR VERSIONVERSION有关有关enqueue hash chainsenqueue hash chains和锁的管理有关和锁的管理有关modify parameters valuesmodify parameters values：动态调整参数有关：动态调整参数有关parallel query alloc bu

22、ffer,parallel query stats :parallel query alloc buffer,parallel query stats :和并行查询有关和并行查询有关GESGES* *：和全局锁有关：和全局锁有关GCSGCS* *：和全局：和全局CACHECACHE有关有关华章培训华章培训案例分析案例分析- -现象现象CPU使用率突然增加系统变得十分缓慢 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华章培训华章培训案例分析-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 - -平均每次闩锁等待时间高达平均每次闩锁等待时间高达481481毫秒毫秒华章培训华章培训案例分析：闩锁情况 Pct Avg Wait Pct Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Get Get Slps Time NoWait NoWaitLatch Requests Miss /Miss