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

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

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

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

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

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

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

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

12、 buffer chain5查找所需数据查找所需数据db_等待等待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获取到闩锁获取到闩锁

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

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

15、cle 10.2开始使用开始使用MutexMutex来实现部分内存的保护来实现部分内存的保护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华章培训华章培训第16页STATSPACK/AWR报告Statspack/AWRStatspack/AWR报告是最好的报告是最好的LATCHLATCH问题分析工具问题分析工具Latch Activity for DBLatch Sleep breakdown for DBLatch Miss Sources

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

18、 cachelibrary cache pinrow cache objectsrow cache enqueue latch华章培训华章培训第22页DB CACHE相关闩锁和和LRU CHAINSLRU CHAINS或者或者HASH CHAINSHASH CHAINS相关相关cache buffer handlescache buffers chainscache buffers lru chainmultiblock read objects华章培训华章培训第23页REDO LOG相关闩锁REDO LOGREDO LOG相关闩锁竞争一般由于以下原因：相关闩锁竞争一般由于以下原因：LOG B

19、UFFER太小过于频繁的COMMITREDO LOG的IO性能不佳LOG SWITCH过于频繁归档出现问题或者归档过慢主要闩锁主要闩锁Redo Copy Redo allocation ：9.2:LOG_PARALLELISM10G:_log_parallelism_maxRedo writingredo on-disk SCN 华章培训华章培训第24页Simulator lru latch与BUGBUG 2452409/BUG 5918642BUG 2452409/BUG 5918642simulator lru latchsimulator lru latch闩锁争用十分高闩锁争用十分高C

20、PUCPU使用率很高使用率很高DB CACHEDB CACHE较大，并且访问负载较大较大，并且访问负载较大影响多个版本影响多个版本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华章培训华章培训第25页其他闩锁archive controlarchive control：和归档目录有关：和归档目录有关process allocationprocess allocation：和进程状态修改有关，在短连接：和进程状态修改有关，在短连接的系统中可能存在竞

21、争的系统中可能存在竞争session allocationsession allocation：和：和SESSIONSESSION信息修改有关信息修改有关sort extent poolsort extent pool：和硬盘排序有关：和硬盘排序有关child cursor hash tablechild cursor hash table：和：和SQLSQL分析以及分析以及CURSOR CURSOR VERSIONVERSION有关有关enqueue hash chainsenqueue hash chains和锁的管理有关和锁的管理有关modify parameters valuesmod

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