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%的使用状态，同时这些潜在的争用点都将变成实际的争用点，表现出来就是等待事件非常多，用户响应缓慢甚至没有响应。

判断当前系统是否使用了绑定变量，可以使用如下语句获得当前系统的硬解析次数与解析总次数的比例。硬解析次数越少越好，这个比例也越接近于0越好。

 

select t.value as total,h.value as hard,
 round(h.value/t.value,2) as ratio_hardtototal
 from v$sysstat t, v$sysstat h
 where t.name='parse count (total)'
 and h.name='parse count (hard)'

TOTAL HARD RATIO_HARDTOTOTAL
---------- ---------- -----------------
2377895510 47207356 0.02

 

http://tech.it168.com/oldarticle/2007-07-13/200707131148906.shtml