生产环境Shared pool共享池调优-SQL层面

oracle环境：9.2.0.5

cursor_sharing参数值=EXACT

在某些应用层面上cursor_sharing的值被设定为SIMILAR

1.调优多版本SQL

 select * from (

select substr(sql_text,1,180),count(*), ADDRESS,HASH_VALUE from v$sql a

group by substr(sql_text,1,180),address,hash_value

order by count(*) desc

)

where rownum<11

找出共享池中排名前10的多版本的sql，超过10个以上的版本就应该调优了，不过如果过多，可以先从top 10开始优化

 调优方法：

举例：

select * from g_akec g where g.wh=:"SYS_B_0" ;

该语句在共享池的版本为500个

相同的语句版本数过多，不利与语句的共享，增加了硬解析，消耗CPU资源，造成闩锁竞争，降低数据库性能，建议检查语句的大小写、空格、换行,对象前面最好带上用户名等，只有完全相同的语句才能共享SQL,还可以将语句写入procedure,function等，目的是减少相同语句的版本数，共享SQL,从而减少CPU的消耗，提高数据库性能。

2.调优未使用绑定变量SQL

select * from (

select substr(sql_text,1,180),count(*), ADDRESS,HASH_VALUE from v$sql a

where upper(sql_text) like 'SELECT%'

group by substr(sql_text,1,180),address,hash_value

having count(*)=1

order by count(*) DESC

)

;

like 'SELECT%' 可以替换成update,insert等语句，进行查找，以找到没有使用绑定变量的sql。

调优方法：

举例：

SELECT -1 As nk_row_num, nk_q1.*

  FROM (SELECT DISTINCT C.LOT_I D, C.NATURE_NUMBER

          FROM nake.l_flat_location a,

               nake.h_flat b,

               nake.h_building      c

         WHERE a.districtid = :1

           and a.location_i d in (24000000241155)

           AND a.house_id = b.house_id

           AND A.DISTRI CTID = B.DISTRICTID

           AND b.building_id = c.building_id

           AND B.DISTR ICTID = C.DISTRICTID) nk_q1

该语句在共享池有相似语句1200条左右，主要是常量不同，常量未使用绑定变量，使用绑定变量，以消除数据库的硬解析，降低CPU的消耗。