前言：
由于各种原因（开发人员的技术水平、开发的管理规范、赶进度），系统在运行一段时间后，发现性能越来越慢。这时候往往是DBA发挥价值的时候到了，DBA通过查看数据库的AWR报告，如果发现以下值Library Hit、Soft Parse偏低（95%以下），Hard parses偏高。那就很有可能是数据库太多语句未使用绑定变量引起的。
DBA可以通过v$sq视图定期抓取一些未使用绑定变量的语句，并由开发进行相应的程序优化，逐步改善系统运行缓慢的问题；
一、查找方法：
从ORACLE10G开始，V$SQL视图中增加列FORCE_MATCHING_SIGNATURE：Signature used when the CURSOR_SHARING parameter is set to FORCE；（当CURSOR_SHARING参数设置成FORCE时，产生的执行计划是一样的）
| SQL> show parameter cursor_sharing;
NAME TYPE VALUE ------------------------------------ ----------- --------------------- cursor_sharing string EXACT |
| SQL>SELECT /*john*/* from bas.john where name='曹定芳';
SQL>SELECT /*john*/* from bas.john where name='张杰雄';
SQL>SELECT /*john*/* from bas.john where name='练为斌'; |
| select sql_text,FORCE_MATCHING_SIGNATURE,EXACT_MATCHING_SIGNATURE from v$sql t where sql_text like '%john%';
SQL_TEXT FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE ---------------------------------------------------- --------------------- ----------------------------- SELECT /*john*/* from bas.john where name='曹定芳' 1.58728914732591E19 8.65016113461197E18 SELECT /*john*/* from bas.john where name='张杰雄' 1.58728914732591E19 1.55664749687513E19 SELECT /*john*/* from bas.john where name='练为斌' 1.58728914732591E19 6.34382341893161E18 |
测试1说明:当name值不一样的时候FORCE_MATCHING_SIGNATURE的值相同，而EXACT_MATCHING_SIGNATURE值不同，说明在cursor_sharing=force时，执行计划可共用；cursor_sharing=exact时，执行计划不可共用；我们使用绑定变量后，同样也可以达到执行计划共用的效果；
测试2：cursor_sharing值为FORCE
1、执行语句：alter system set cursor_sharing=force;
| SQL> alter system set cursor_sharing=force;
System altered.
SQL> show parameter cursor_sharing;
NAME TYPE VALUE --------------------------- --------- ----------- cursor_sharing string FORCE
SQL> alter system flush shared_pool;
System altered. |
| SQL>SELECT /*john*/* from bas.john where name='曹定芳';
SQL>SELECT /*john*/* from bas.john where name='张杰雄';
SQL>SELECT /*john*/* from bas.john where name='练为斌'; |
3、查找V$SQL的语句情况
| select sql_text,FORCE_MATCHING_SIGNATURE,EXACT_MATCHING_SIGNATURE from v$sql t where sql_text like '%john%'; SQL_TEXT FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE ---------------------------------------------------- --------------------- ----------------------------- SELECT /*john*/* from bas.john where name=:"SYS_B_0" 1.58728914732591E19 1.58728914732591E19 |
测试2说明：当我们把cursor_sharing设置为force后，发现语句执行计划共用了，并且自动使用绑定变量(此时v$sql视图中只查询到一条与之相关的语句)。
测试3、cursor_sharing值为EXACT，但使用绑定变量
1、执行语句：alter system set cursor_sharing=EXACT;
| SQL> alter system set cursor_sharing=force;
System altered.
SQL> show parameter cursor_sharing;
NAME TYPE VALUE --------------------------- --------- ----------- cursor_sharing string EXACT
SQL> alter system flush shared_pool;
System altered. |
2、使用绑定变量查询
SQL>SELECT /*john*/* from bas.john where name=:a; |
3、查找V$SQL的语句情况
| select sql_text,FORCE_MATCHING_SIGNATURE,EXACT_MATCHING_SIGNATURE from v$sql t where sql_text like '%john%';
SQL_TEXT FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE ---------------------------------------------------- --------------------- ----------------------------- SELECT /*john*/* from bas.john where name=:a 1.4513622515145E19 1.4513622515145E19 |
测试3总结：当我们把cursor_sharing设置为EXACT后，并通过绑定变量查询，发现语句执行计划共用了；
二、解决方法
通过以上测试已经了解：如果V$SQL里面的FORCE_MATCHING_SIGNATURE值一样的SQL语句，那么就可以通过绑定变量来进行相应的优化了。
| select to_char(FORCE_MATCHING_SIGNATURE) as FORCE_MATCHING_SIGNATURE, count(1) as counts from v$sql where FORCE_MATCHING_SIGNATURE>0 and FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE group by FORCE_MATCHING_SIGNATURE having count(1) > &a order by 2 desc; |
找到语句后，剩下的工作就交给开发人员进行修改了，DBA又为整个系统性能的提高立了一大功。
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
本文作者：JOHN
ORACLE技术博客：ORACLE 猎人笔记 数据库技术群：367875324 （请备注ORACLE管理 ）
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
