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 -------- ----------
