SAP与Oracle的不同CRM之路
(......接上篇)
4. shared pool的优化
4.1 共享SQL语句
根据上面对shared pool的内部原理的说明，我们已经很清楚的知道，oracle引入shared pool就是为了能够缓存经常使用的SQL语句，从而能够将SQL语句的执行计划缓存在library cache中，这样当第二次执行相同的SQL语句时，就可以跳过硬解析而进行软解析，从而节省了大量的CPU资源。当一句新的SQL语句进入shared pool时，需要分配chunk，这时会持有shared pool latch，直到获得chunk，这是一个潜在的争用点；获得chunk以后，进入library cache时，需要获得library cache latch来保护对lock的获得，这又是一个潜在的争用点。然后，oracle要lock住句柄，才能往里填写内容，这也是一个潜在的争用点；生成执行计划等内容以后，oracle还要pin住若干个heap，才能往里写入实际的数据，这还是一个潜在的争用点。可见，一句新的SQL语句从进入shared pool开始到解析结束，存在一系列的争用点。特别是，当并发用户很多的时候，每个用户都发出对于shared pool来说是新的SQL语句，这时，你会看到CPU非常繁忙，甚至一直处于100%的使用状态，同时这些潜在的争用点都将变成实际的争用点，表现出来就是等待事件非常多，用户响应缓慢甚至没有响应。
为了尽可能减少新的SQL语句，尽可能多的缓存SQL语句，就必须使得应用程序的SQL语句尽量保持一致，包括各个单词之间的空格一致以及大小写一致等。其中最重要的一点就是要使用绑定变量。对于一个系统来说，SQL语句本身所引用的表和列都是有限的，只有SQL语句中所引用的数据才是无限的，因此将SQL语句中所涉及到的数据都用绑定变量来替代，这样就能使得对于不同的数据，SQL语句看起来都是一样的。
判断当前系统是否使用了绑定变量，可以使用如下语句获得当前系统的硬解析次数与解析总次数的比例。硬解析次数越少越好，这个比例也越接近于0越好。
如果发现硬解析比较高，则可以使用下面的方法找到shared pool里那些没有使用绑定变量的SQL语句，从而提交给开发人员进行修改。
如果发现系统中大量的没有使用绑定变量，而且系统是由其他第三方供应商提供的，不能做大量的修改从而使用绑定变量。实际上，这样的系统基本就是一个失败的系统，但是如果必须继续使用而又希望能够尽量减少对CPU资源的争用，oracle还提供了一个参数：cursor_sharing。该参数缺省是exact，表示不对传入shared pool中的SQL语句改写。如果设置为similar或force，则oracle会对SQL语句进行改写，将SQL语句中值的部分都用系统生成的变量来替代，从而达到与绑定变量相同的目的。similar表示，当SQL语句中的数值所在的列存在直方图（histogram）信息时，oracle不对SQL语句进行改写，就像设置为exact一样，每次对于不同的值都要进行硬解析；而当表没有经过分析，不存在直方图时，oracle会对SQL语句进行改写，就像设置为force一样，这样每次对于不同的值都会进行软解析。
但是使用这种方法在不同的oracle版本中可能存在bug，需要在测试环境中仔细测试。同时，将cursor_sharing设置为similar或force以后，会在生成执行计划上产生一些副作用，比如选择错误的索引，以及忽略带有数值的函数索引（比如函数索引为substr(colname,1,6)的情况，因为其中的1和6被系统变量替代了）等。
对于某些非常频繁使用的对象，我们还可以使用存储过程：DBMS_SHARED_POOL.KEEP，从而将它们固定在shared pool里，这样被钉住的对象就不会被交换出shared pool，即便刷新shared pool也不能将这些对象刷新出去。如果没有发现这个存储过程，则可以使用oracle脚本：dbmspool.sql 来创建，该脚本位于目录：$ORACLE_HOME/rdbms/admin下。
该存储过程有两个参数，第一个参数表示要钉在内存里的对象的名字，第二个参数表示要钉住的对象的类型，缺省为P，表示存储过程、包、函数，如果要钉住某个SQL语句，则需要设置该参数为C。对于存储过程、包、函数、触发器以及序列（sequence）等对象，在调用该存储过程时，使用名字对其进行引用。比如：DBMS_SHARED_POOL.KEEP('SALES.PKG_SALES')，这样就将位于SALES下的包PKG_SALES给钉在内存里了。；而对于某条单独的SQL语句来说，则需要使用地址和hash值对其进行引用，地址和hash值可以从v$sqlarea里的address和hash_value列获得，比如对于我们前面测试的SQL语句来说，address就是图二中的handle值，也就是'6758CDBC，hash_value就是图二中的541cf4c2，转换为十进制就是1411183810，则我们将其钉在内存里：DBMS_SHARED_POOL.KEEP('6758CDBC,1411183810',’C’)。如果我们要取消钉住的话，则调用DBMS_SHARED_POOL.UNKEEP即可，该过程的参数与KEEP相同。
如下例所示，我们将该SQL语句钉在shared pool以后，刷新shared pool也没能将它刷新出去。
4.2 shared pool的设置优化
设置shared pool的大小来说，没有一个通用的、普遍适用的值，不同的系统负载需要不同大小的shared pool来管理。通常我们在设置shared pool时，应该遵循“不要太大、也不要太小”的原则，设置一个初始的值，然后让系统正常运行一段时间，在这段时间里，对shared pool的使用情况进行观察监控，最后根据系统的负载得出一个在当前负载下比较合理的值。注意，这里只是说明是在当前负载下，如果随着系统的不断升级，导致负载发生一个比较质的变化，这时又需要对shared pool重新监控并做成调整了。
设置1G以上的shared pool不会给性能带来任何的提高，相反，这将给oracle管理shared pool以及监控shared pool的过程中会带来更多的麻烦。我们可以在系统上线时，设置shared pool为SGA的10%，但是不要超过1G，让系统正常运行一段时间，然后我们可以借助9i以后所引入的advisory来帮助我们判断shared pool设置是否合理。
只要将初始化参数：statistics_level设置为typical（缺省值）或all，就能启动对shared pool的建议功能，如果设置为basic，则关闭建议功能。使用如下的SQL语句显示oracle所建议的shared pool的大小。
第一列表示oracle所估计的shared pool的尺寸值，其他列表示在该估计的shared pool大小下所表现出来的指标值，具体含义可以参见oracle的联机帮助。我们主要关注estd_lc_time_saved_factor列的值，当该列值为1时，表示再增加shared pool对性能的提高没有意义。对于上例来说，当shared pool为224M时，达到非常好的大小。对于设置比224M更大的shared pool来说，就是浪费空间，没有意义了。
我们还可以借助v$shared_pool_advice来观察不同的shared pool尺寸情况下的响应时间（单位是秒）各是多少：
如果是9i之前的版本，没有advisory的话，则可以在系统运行过程中，观察shared pool的统计信息以及等待事件来判断shared pool是否合理。
如果设置了Shared Server连接模式，则注意要通过配置large pool（通过设置large_pool参数）。如果不设置large pool，session的PGA会有一部分在shared pool里进行分配，从而加重shared pool的负担。
4.3 shared pool的统计信息
有关shared pool的最重要的统计信息就是parse count (total)和parse count (hard)，parse count (total)表示解析的总次数，而parse count (hard)表示硬解析的次数，这个值应该越小越好。
对于dictionary cache来说，可以使用如下的统计信息来判断，如果miss-ratio大于0.02，则说明shared pool设置过小，需要增加shared pool。
4.4 shared pool的等待事件
4.4.1 library cache latch
对于library cache latch来说，我们可以看看v$latch中按照sleeps排名前5位中是否有它。
我们看到，library cache latch的sleep排名比较靠前。这时，我们在前面已经说过，子library cache latch可能不会在library cache里的整个bucket链条上均匀分布，因此当我们通过查看v$latch发现library cache latch的等待非常高时，先别急着增加latch的个数，或者调整SQL等，而是先去看看v$latch_children中每个子library cache latch的等待是否都比较接近，如果发现这些latch的等待相差很大的话，则说明可能是没有最有效的使用latch。同时注意观察下面的MISSES/GETS列，如果它高于0.01的话，应该调整。
比如，从上面我们可以看到，CHILD#为5的子latch的miss占了整个latch的87%，因此，有可能是该子latch管理了过多的library cache object。我们可以使用下面的SQL语句来看看这个子latch到底管了哪些SQL对象：
select * from v$db_object_cache where child_latch=5 and namespace='CURSOR';
然后，看看该子latch所管理的SQL语句是不是可以改写一下，从而让它关联到其他4个子latch上去。有时候为列添加一个别名或者在where条件子句中添加一个条件“1=1”，就能够将该SQL转换到另外一个子library cache latch来管理，从而通过分散子library cache latch来达到降低该latch等待的目的。
如果所有的子library cache latch都均匀分布的话，则需要按照前面所说的方法检查SQL语句是否使用了绑定变量、是否大小写一致、空格是否相同等。如果这些都没问题，则检查shared pool是否设置过大，如果设置也合理的话，那就需要按照前面所说的，增加隐藏参数：_kgl_latch_count的值，从而增加library cache latch的数量。
4.4.2 library cache lock和library cache pin
在前面的部分我们已经看到了library cache lock和library cache pin的成因，并且也模拟出了这两个等待事件。那么当我们发现在v$session_wait里出现了这两个等待事件中的某一个式时，该怎么办呢？
还是按照上面我们模拟这两个等待事件的例子。这时我们查看v$session_wait，发现出现这两个等待。
通常，这两个等待都是由于DDL所引起的，因此我们还可以快速的通过查看dba_ddl_lock视图来看当前哪些session正在对表lock_test进行操作。我们可以看到下面的结果中，sid为9的session正以Exclusive模式获得表lock_test，而sid为10的session则正以Exclusive模式申请对该表的lock。
4. shared pool的优化
4.1 共享SQL语句
根据上面对shared pool的内部原理的说明，我们已经很清楚的知道，oracle引入shared pool就是为了能够缓存经常使用的SQL语句，从而能够将SQL语句的执行计划缓存在library cache中，这样当第二次执行相同的SQL语句时，就可以跳过硬解析而进行软解析，从而节省了大量的CPU资源。当一句新的SQL语句进入shared pool时，需要分配chunk，这时会持有shared pool latch，直到获得chunk，这是一个潜在的争用点；获得chunk以后，进入library cache时，需要获得library cache latch来保护对lock的获得，这又是一个潜在的争用点。然后，oracle要lock住句柄，才能往里填写内容，这也是一个潜在的争用点；生成执行计划等内容以后，oracle还要pin住若干个heap，才能往里写入实际的数据，这还是一个潜在的争用点。可见，一句新的SQL语句从进入shared pool开始到解析结束，存在一系列的争用点。特别是，当并发用户很多的时候，每个用户都发出对于shared pool来说是新的SQL语句，这时，你会看到CPU非常繁忙，甚至一直处于100%的使用状态，同时这些潜在的争用点都将变成实际的争用点，表现出来就是等待事件非常多，用户响应缓慢甚至没有响应。
为了尽可能减少新的SQL语句，尽可能多的缓存SQL语句，就必须使得应用程序的SQL语句尽量保持一致，包括各个单词之间的空格一致以及大小写一致等。其中最重要的一点就是要使用绑定变量。对于一个系统来说，SQL语句本身所引用的表和列都是有限的，只有SQL语句中所引用的数据才是无限的，因此将SQL语句中所涉及到的数据都用绑定变量来替代，这样就能使得对于不同的数据，SQL语句看起来都是一样的。
判断当前系统是否使用了绑定变量，可以使用如下语句获得当前系统的硬解析次数与解析总次数的比例。硬解析次数越少越好，这个比例也越接近于0越好。
SQL> select t.value as total,h.value as hard, 2 round(h.value/t.value,2) as ratio_hardtototal 3 from v$sysstat t, v$sysstat h 4 where t.name='parse count (total)' 5 and h.name='parse count (hard)' 6 / TOTAL HARD RATIO_HARDTOTOTAL ---------- ---------- ----------------- 2377895510 47207356 0.02
如果发现硬解析比较高，则可以使用下面的方法找到shared pool里那些没有使用绑定变量的SQL语句，从而提交给开发人员进行修改。
break on plan_hash_value on execnt on hash_value skip 1 select d.plan_hash_value plan_hash_value , d.execnt execnt , a.hash_value hash_value , a.sql_text sql_text from v$sqltext a, (select plan_hash_value,hash_value,execnt from ( select c.plan_hash_value,b.hash_value,c.execnt, rank() over(partition by c.plan_hash_value order by b.hash_value) as hashrank from v$sql b, ( select count(*) as execnt,plan_hash_value from v$sql where plan_hash_value <> 0 group by plan_hash_value having count(*) > 10 order by count(*) desc ) c where b.plan_hash_value = c.plan_hash_value group by c.plan_hash_value,b.hash_value,c.execnt ) where hashrank<=3 ) d where a.hash_value = d.hash_value order by d.execnt desc,a.hash_value,a.piece /
如果发现系统中大量的没有使用绑定变量，而且系统是由其他第三方供应商提供的，不能做大量的修改从而使用绑定变量。实际上，这样的系统基本就是一个失败的系统，但是如果必须继续使用而又希望能够尽量减少对CPU资源的争用，oracle还提供了一个参数：cursor_sharing。该参数缺省是exact，表示不对传入shared pool中的SQL语句改写。如果设置为similar或force，则oracle会对SQL语句进行改写，将SQL语句中值的部分都用系统生成的变量来替代，从而达到与绑定变量相同的目的。similar表示，当SQL语句中的数值所在的列存在直方图（histogram）信息时，oracle不对SQL语句进行改写，就像设置为exact一样，每次对于不同的值都要进行硬解析；而当表没有经过分析，不存在直方图时，oracle会对SQL语句进行改写，就像设置为force一样，这样每次对于不同的值都会进行软解析。
但是使用这种方法在不同的oracle版本中可能存在bug，需要在测试环境中仔细测试。同时，将cursor_sharing设置为similar或force以后，会在生成执行计划上产生一些副作用，比如选择错误的索引，以及忽略带有数值的函数索引（比如函数索引为substr(colname,1,6)的情况，因为其中的1和6被系统变量替代了）等。
对于某些非常频繁使用的对象，我们还可以使用存储过程：DBMS_SHARED_POOL.KEEP，从而将它们固定在shared pool里，这样被钉住的对象就不会被交换出shared pool，即便刷新shared pool也不能将这些对象刷新出去。如果没有发现这个存储过程，则可以使用oracle脚本：dbmspool.sql 来创建，该脚本位于目录：$ORACLE_HOME/rdbms/admin下。
该存储过程有两个参数，第一个参数表示要钉在内存里的对象的名字，第二个参数表示要钉住的对象的类型，缺省为P，表示存储过程、包、函数，如果要钉住某个SQL语句，则需要设置该参数为C。对于存储过程、包、函数、触发器以及序列（sequence）等对象，在调用该存储过程时，使用名字对其进行引用。比如：DBMS_SHARED_POOL.KEEP('SALES.PKG_SALES')，这样就将位于SALES下的包PKG_SALES给钉在内存里了。；而对于某条单独的SQL语句来说，则需要使用地址和hash值对其进行引用，地址和hash值可以从v$sqlarea里的address和hash_value列获得，比如对于我们前面测试的SQL语句来说，address就是图二中的handle值，也就是'6758CDBC，hash_value就是图二中的541cf4c2，转换为十进制就是1411183810，则我们将其钉在内存里：DBMS_SHARED_POOL.KEEP('6758CDBC,1411183810',’C’)。如果我们要取消钉住的话，则调用DBMS_SHARED_POOL.UNKEEP即可，该过程的参数与KEEP相同。
如下例所示，我们将该SQL语句钉在shared pool以后，刷新shared pool也没能将它刷新出去。
SQL> select address,hash_value from v$sqlarea 2 where sql_text = 'select object_id,object_name from sharedpool_test '; ADDRESS HASH_VALUE -------- ---------- 6758CDBC 1411183810 SQL> exec DBMS_SHARED_POOL.KEEP('6758CDBC,1411183810','C'); SQL> alter system flush shared pool; SQL> select address,hash_value from v$sqlarea 2 where sql_text = 'select object_id,object_name from sharedpool_test '; ADDRESS HASH_VALUE -------- ---------- 6758CDBC 1411183810 SQL> exec DBMS_SHARED_POOL.UNKEEP('6758CDBC,1411183810','C'); SQL> alter system flush shared pool; SQL> select address,hash_value from v$sqlarea 2 where sql_text = 'select object_id,object_name from sharedpool_test '; ADDRESS HASH_VALUE -------- ----------
4.2 shared pool的设置优化
设置shared pool的大小来说，没有一个通用的、普遍适用的值，不同的系统负载需要不同大小的shared pool来管理。通常我们在设置shared pool时，应该遵循“不要太大、也不要太小”的原则，设置一个初始的值，然后让系统正常运行一段时间，在这段时间里，对shared pool的使用情况进行观察监控，最后根据系统的负载得出一个在当前负载下比较合理的值。注意，这里只是说明是在当前负载下，如果随着系统的不断升级，导致负载发生一个比较质的变化，这时又需要对shared pool重新监控并做成调整了。
设置1G以上的shared pool不会给性能带来任何的提高，相反，这将给oracle管理shared pool以及监控shared pool的过程中会带来更多的麻烦。我们可以在系统上线时，设置shared pool为SGA的10%，但是不要超过1G，让系统正常运行一段时间，然后我们可以借助9i以后所引入的advisory来帮助我们判断shared pool设置是否合理。
只要将初始化参数：statistics_level设置为typical（缺省值）或all，就能启动对shared pool的建议功能，如果设置为basic，则关闭建议功能。使用如下的SQL语句显示oracle所建议的shared pool的大小。
SQL> SELECT shared_pool_size_for_estimate, estd_lc_size, estd_lc_memory_objects, 2 estd_lc_time_saved, estd_lc_time_saved_factor, 3 estd_lc_memory_object_hits 4 FROM v$shared_pool_advice; SHARED_POOL_ ESTD_LC ESTD_LC_MEMORY ESTD_LC_TIME ESTD_LC_TIME ESTD_LC_MEMORY SIZE_FOR_ESTIMATE _SIZE _OBJECTS _SAVED _SAVED_FACTOR _OBJECT_HITS ----------------- ------- --------------- ------------ ------------- ------------ 128 135 12223 8566 0.9993 2980874 160 166 15809 8567 0.9994 2981291 192 197 19167 8570 0.9998 2982322 224 228 22719 8572 1 2982859 256 259 27594 8572 1 2982906 288 292 31436 8572 1 2982917 320 323 36157 8572 1 2982920 352 354 40371 8572 1 2982929 384 385 45019 8572 1 2982937 416 389 46099 8572 1 2982937 448 389 46099 8572 1 2982937 480 389 46099 8572 1 2982937 512 389 46099 8572 1 2982937
第一列表示oracle所估计的shared pool的尺寸值，其他列表示在该估计的shared pool大小下所表现出来的指标值，具体含义可以参见oracle的联机帮助。我们主要关注estd_lc_time_saved_factor列的值，当该列值为1时，表示再增加shared pool对性能的提高没有意义。对于上例来说，当shared pool为224M时，达到非常好的大小。对于设置比224M更大的shared pool来说，就是浪费空间，没有意义了。
我们还可以借助v$shared_pool_advice来观察不同的shared pool尺寸情况下的响应时间（单位是秒）各是多少：
SQL> SELECT 'Shared Pool' component, 2 shared_pool_size_for_estimate estd_sp_size, 3 estd_lc_time_saved_factor parse_time_factor, 4 CASE 5 WHEN current_parse_time_elapsed_s + adjustment_s < 0 THEN 6 0 7 ELSE 8 current_parse_time_elapsed_s + adjustment_s 9 END response_time 10 FROM (SELECT shared_pool_size_for_estimate, 11 shared_pool_size_factor, 12 estd_lc_time_saved_factor, 13 a.estd_lc_time_saved, 14 e.VALUE / 100 current_parse_time_elapsed_s, 15 c.estd_lc_time_saved - a.estd_lc_time_saved adjustment_s 16 FROM v$shared_pool_advice a, 17 (SELECT * FROM v$sysstat WHERE NAME = 'parse time elapsed') e, 18 (SELECT estd_lc_time_saved 19 FROM v$shared_pool_advice 20 WHERE shared_pool_size_factor = 1) c); COMPONENT ESTD_SP_SIZE PARSE_TIME_FACTOR RESPONSE_TIME ----------- ------------ ----------------- ------------- Shared Pool 128 0.9993 252.82 Shared Pool 160 0.9994 251.82 Shared Pool 192 0.9998 248.82 Shared Pool 224 1 246.82 Shared Pool 256 1 246.82 Shared Pool 288 1 246.82 Shared Pool 320 1 246.82 Shared Pool 352 1 246.82 Shared Pool 384 1 246.82 Shared Pool 416 1 246.82 Shared Pool 448 1 246.82 Shared Pool 480 1 246.82 Shared Pool 512 1 246.82
如果是9i之前的版本，没有advisory的话，则可以在系统运行过程中，观察shared pool的统计信息以及等待事件来判断shared pool是否合理。
如果设置了Shared Server连接模式，则注意要通过配置large pool（通过设置large_pool参数）。如果不设置large pool，session的PGA会有一部分在shared pool里进行分配，从而加重shared pool的负担。
4.3 shared pool的统计信息
有关shared pool的最重要的统计信息就是parse count (total)和parse count (hard)，parse count (total)表示解析的总次数，而parse count (hard)表示硬解析的次数，这个值应该越小越好。
SQL> select * from v$sysstat where name in ('parse count (total)','parse count (hard)'); STATISTIC# NAME CLASS VALUE ---------- ------------------ -------- ---------- 235 parse count (total) 64 4989 236 parse count (hard) 64 993 对于library cache来说，可以使用如下的统计信息来判断，如果reload-to-pins大于0.01，则说明shared pool设置过小，需要增加shared pool。 SQL> select sum(pins) "Executions", sum(reloads) "Cache Misses", 2 sum(reloads)/sum(pins) " reload-to-pins" from v$librarycache; Executions Cache Misses reload-to-pins ---------- ------------ --------------- 4458375 4159 0.0009328510948
对于dictionary cache来说，可以使用如下的统计信息来判断，如果miss-ratio大于0.02，则说明shared pool设置过小，需要增加shared pool。
SQL> select sum(gets) as gets,sum(getmisses) as misses, 2 sum(getmisses)/sum(gets) as miss_ratio from v$rowcache; GETS MISSES MISS_RATIO ---------- ---------- ---------- 4762783 15397 0.00323277
4.4 shared pool的等待事件
4.4.1 library cache latch
对于library cache latch来说，我们可以看看v$latch中按照sleeps排名前5位中是否有它。
SQL> select latch#,name,sleeps from v$latch where sleeps!=0 order by sleeps desc; LATCH# NAME SLEEPS ---------- -------------------- ---------- 4 session allocation 38389 157 library cache 1221 3 process allocation 974 115 redo allocation 841 156 shared pool 755
我们看到，library cache latch的sleep排名比较靠前。这时，我们在前面已经说过，子library cache latch可能不会在library cache里的整个bucket链条上均匀分布，因此当我们通过查看v$latch发现library cache latch的等待非常高时，先别急着增加latch的个数，或者调整SQL等，而是先去看看v$latch_children中每个子library cache latch的等待是否都比较接近，如果发现这些latch的等待相差很大的话，则说明可能是没有最有效的使用latch。同时注意观察下面的MISSES/GETS列，如果它高于0.01的话，应该调整。
SQL> select latch#,child#,gets,round(ratio_to_report(gets) OVER (),2) as gets_radio, 2 misses,round(ratio_to_report(misses) OVER (),2) as misses_radio,misses/gets 3 from v$latch_children where name='library cache'; LATCH# CHILD# GETS GETS_RADIO MISSES MISSES_RADIO MISSES/GETS ---------- ---------- ---------- ---------- ---------- ------------ ----------- 157 5 7187577 0.3 160964 0.85 0.022394751 157 4 4308236 0.18 19943 0.11 0.004629040 157 3 4138652 0.17 2453 0.01 0.000592705 157 2 2458795 0.1 448 0 0.000182203 157 1 5974764 0.25 4619 0.02 0.000773084
比如，从上面我们可以看到，CHILD#为5的子latch的miss占了整个latch的87%，因此，有可能是该子latch管理了过多的library cache object。我们可以使用下面的SQL语句来看看这个子latch到底管了哪些SQL对象：
select * from v$db_object_cache where child_latch=5 and namespace='CURSOR';
然后，看看该子latch所管理的SQL语句是不是可以改写一下，从而让它关联到其他4个子latch上去。有时候为列添加一个别名或者在where条件子句中添加一个条件“1=1”，就能够将该SQL转换到另外一个子library cache latch来管理，从而通过分散子library cache latch来达到降低该latch等待的目的。
如果所有的子library cache latch都均匀分布的话，则需要按照前面所说的方法检查SQL语句是否使用了绑定变量、是否大小写一致、空格是否相同等。如果这些都没问题，则检查shared pool是否设置过大，如果设置也合理的话，那就需要按照前面所说的，增加隐藏参数：_kgl_latch_count的值，从而增加library cache latch的数量。
4.4.2 library cache lock和library cache pin
在前面的部分我们已经看到了library cache lock和library cache pin的成因，并且也模拟出了这两个等待事件。那么当我们发现在v$session_wait里出现了这两个等待事件中的某一个式时，该怎么办呢？
还是按照上面我们模拟这两个等待事件的例子。这时我们查看v$session_wait，发现出现这两个等待。
SQL> select sid,event from v$session_wait where event like 'library%'; SID EVENT ---------- ---------------------------------------------------------------- 9 library cache pin 10 library cache lock
这时，我们可以使用如下的SQL语句来获得是哪个session通过哪条SQL语句获得了library cache pin，而哪个session又试图通过哪条SQL语句去申请library cache pin。 
从上面的结果可以很清楚的看到，sid为8的session阻止了sid为9的session获得pin，而sid为9的session又阻止了sid为10的session获得lock。SQL> SELECT distinct 2 decode(kglpnreq,0,'holding_session: ' || s.sid,'waiting_session: ' || s.sid) sid, 3 s.SERIAL#, 4 kglpnmod "Pin-Mode", 5 kglpnreq "Req-Pin", 6 a.sql_text, 7 kglnaown "Owner", 8 kglnaobj "Object" 9 FROM x$kglpn p, v$session s, v$sqlarea a, v$session_wait sw, x$kglob x 10 WHERE p.kglpnuse = s.saddr 11 AND kglpnhdl = sw.p1raw 12 and kglhdadr = sw.p1raw 13 and event = 'library cache pin' 14 and (a.hash_value, a.address) IN 15 (select DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value), 16 DECODE(sql_hash_value, 0, prev_sql_addr, sql_address) 17 from v$session s2 18 where s2.sid = s.sid); SID SERIAL# Pin-Mode Req-Pin SQL_TEXT Owner Object --------------- ------- ------ ------- ------------------------------ ------ ---------- holding_session:8 22 2 0 BEGIN lock_test; END; COST LOCK_TEST waiting_session:9 16 0 3 alter procedure lock_test compile COST LOCK_TEST
同样，我们可以使用下面的SQL语句来显示哪个session正持有lock或者pin，而哪个session又在等待lock或者pin。
注意，我们不能获得正在等待lock的session所发出的SQL语句，因为该session都还没
有获得library cache对象句柄的控制权，就更谈不上将SQL语句写入heap里了，所以我们无法获得该session所发出的SQL语句。
因此，我们只能显示哪个session正在等待lock。
SQL> select /*+ ordered */ 2 w1.sid waiting_session, 3 h1.sid holding_session,
4 w.kgllktype lock_or_pin, 5 w.kgllkhdl address,
6 decode(h.kgllkmod,0,'None',1,'Null',2,'Share',3,'Exclusive','Unknown')
mode_held, 7 decode(w.kgllkreq,0,'None',1,'Null',2,'Share',3,'Exclusive','Unknown')
mode_requested 8 from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1 9
where (((h.kgllkmod != 0) and (h.kgllkmod != 1) 10 and ((h.kgllkreq = 0) or (h.kgllkreq = 1))) 11
and (((w.kgllkmod = 0) or (w.kgllkmod = 1)) 12 and ((w.kgllkreq != 0) and (w.kgllkreq != 1)))) 13
and w.kgllktype = h.kgllktype 14 and w.kgllkhdl = h.kgllkhdl 15 and w.kgllkuse = w1.saddr 16
and h.kgllkuse = h1.saddr; WAITING_SESSION HOLDING_SESSION LOCK_OR_PIN ADDRESS MODE_HELD MODE_REQUESTED
--------------- --------------- ----------- -------- --------- --------------
9 8 Pin 675C3078 Share Exclusive 10 9 Lock 675C3078 Exclusive Exclusive
通常，这两个等待都是由于DDL所引起的，因此我们还可以快速的通过查看dba_ddl_lock视图来看当前哪些session正在对表lock_test进行操作。我们可以看到下面的结果中，sid为9的session正以Exclusive模式获得表lock_test，而sid为10的session则正以Exclusive模式申请对该表的lock。
SQL> select session_id,owner,type,mode_held,mode_requested
2 from dba_ddl_locks where owner='COST' and name='LOCK_TEST';
SESSION_ID OWNER TYPE MODE_HELD MODE_REQUESTED
---------- ------------ -------------------- --------- --------------
8 COST Table/Procedure/Type Null None
9 COST Table/Procedure/Type Exclusive None
10 COST Table/Procedure/Type None Exclusive
0
相关文章
关注我们
