关于高效捕获数据库非绑定变量的SQL语句

客户一套新业务系统上线，告知数据库相应非常慢，并传了一份AWR报告

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:  100.00       Redo NoWait %:  100.00
            Buffer  Hit   %:  100.03    In-memory Sort %:  100.00
            Library Hit   %:   89.24        Soft Parse %:   89.96
         Execute to Parse %:    1.40         Latch Hit %:   99.84
Parse CPU to Parse Elapsd %:   24.60     % Non-Parse CPU:   88.36

Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:             13,275.52                776.46
              Logical reads:             24,603.65              1,439.02
              Block changes:                 86.79                  5.08
             Physical reads:                  7.80                  0.46
            Physical writes:                  4.26                  0.25
                 User calls:              5,693.60                333.01
                     Parses:                753.93                 44.10

Statistic Name                                       Time (s) % of DB Time
------------------------------------------ ------------------ ------------
DB CPU                                               29,551.0         68.9
sql execute elapsed time                             26,901.8         62.7
parse time elapsed                                   13,859.0         32.3

从awr报告来看,目前buffer cache配置良好
Buffer Nowait %: 和Buffer Hit %:命中率均达到100%
Physical reads也只有7.8 

而从shared pool来看，目前shared pool size配置1,344M 而相应的Library Hit %和Soft Parse %仅仅在90%一下，表现不理想
从解析方面来看每秒Parses:达到753 而每秒的硬解析达到76 过多的硬解析是造成数据库响应慢的一个重要原因
等待事件中出现了latch: library cache和latch: shared pool说明在shared pool使用上存在着争用。
并且从AWR报告中也反应出来，数据库中存在着较多的语句没有使用绑定变量
现在问题已经很明确了，由于绑定变量没有合理的使用，导致数据库中sql的硬解析过多，消耗了过多的CPU资源和shared pool资源
现在的问题就是需要找出这些sql语句，通过应用程序来调整sql
传统的手法：
SELECT substr(sql_text, 1, 80), count(1)
FROM v$sql
GROUP BY substr(sql_text, 1, 80)
HAVING count(1) > 10
ORDER BY 2;
或者
通过v$sqlarea来查询：
select hash_value, substr(sql_text,1,80)
from v$sqlarea
where substr(sql_text,1,40) in (select substr(sql_text,1,40)
from v$sqlarea
having count(*) > 50
group by substr(sql_text,1,40));

不过通过以上sql来进行sql捕获的话会有一个很明显的问题就是运行速度慢，由于涉及到的sql量比较大，所以会造成sql语句运行缓慢，
较为先进的作法是通过create table from v$sql创建表，并且通过对表创建索引等等来优化查询
10g之后，oracle对v$sql视图进行了变更，添加了一个新的字段FORCE_MATCHING_SIGNATURE
该字段oracle对于其解释为The signature used when the CURSOR_SHARING parameter is set to FORCE
初步的理解应该是假定数据库的cursor_sharing为force时计算得到的值，而EXACT_MATCHING_SIGNATURE的解释为
Signature calculated on the normalized SQL text. The normalization includes the removal of white space and the uppercasing of all non-literal strings.
个人的理解为当sql语句进入数据库中时对于一些可以潜在可以共享或者因为绑定变量问题造成游标没有共享的sql他的FORCE_MATCHING_SIGNATURE值完全相同，而EXACT_MATCHING_SIGNATURE值是不同的

我们可以通过一个简单的测试来看一下
以下有一张表：

SQL> select * from test;         ID ----------          1          2 首先我刷新shared pool SQL>  alter system flush shared_pool; System altered. 做几个查询操作： SQL> select * from test where id=1;         ID ----------          1 SQL> select * from test where id=2;         ID ----------          2 SQL> select * from test where id=4; no rows selected SQL> select * from test where id=‘1‘;         ID ----------          1 SQL> select * from test where id=‘2‘;         ID ----------          2 SQL> var v_id number SQL> exec :v_id := 1 PL/SQL procedure successfully completed. SQL> select * from test where id=:v_id;         ID ----------          1 SQL> exec :v_id:=2 PL/SQL procedure successfully completed. SQL> select * from test where id=:v_id;         ID ----------          2 查看v$sql中的sql语句： SQL> col sql_text format a40  SQL> set numwidth 30 SQL> select sql_text,FORCE_MATCHING_SIGNATURE,EXACT_MATCHING_SIGNATURE FROM V$SQL WHERE sql_text like ‘%select * from test%‘; SQL_TEXT                                       FORCE_MATCHING_SIGNATURE       EXACT_MATCHING_SIGNATURE ---------------------------------------- ------------------------------ ------------------------------ select * from test where id=4                      15142173931344982766            9756054619608722298 select * from test where id=2                      15142173931344982766           3132555961613213627 select * from test where id=‘1‘                     15142173931344982766           13165047151983476077 SQL_TEXT                                       FORCE_MATCHING_SIGNATURE       EXACT_MATCHING_SIGNATURE ---------------------------------------- ------------------------------ ------------------------------ select * from test where id=‘2‘                    15142173931344982766            9000319709726737374 select * from test where id=:v_id                  14699597912112598920           14699597912112598920 select * from test where id=1                     15142173931344982766          10881582965512961005 8 rows selected.

/*以上将变量硬编码至SQL中的游标，FORCE_MATCHING_SIGNATURE值完全相同，
而EXACT_MATCHING_SIGNATURE值各有不同。
FORCE_MATCHING_SIGNATURE值相同说明在游标共享FORCE模式下，这些游标满足CURSOR SHARING的条件 */
SQL> alter system flush shared_pool;
 
可以看到以上的sql在没有使用绑定变量的sql中FORCE_MATCHING_SIGNATURE值均是相同的而EXACT_MATCHING_SIGNATURE是不同的
那么通过以上的sql我们就可以完善出查找没有使用绑定变量的sql语句：
SQL> select * from 
(select sql_text,row_number() over(partition by FORCE_MATCHING_SIGNATURE order by FORCE_MATCHING_SIGNATURE) rn
  2  from v$sql 
  3    4  where FORCE_MATCHING_SIGNATURE >0 
  5  and FORCE_MATCHING_SIGNATURE !=EXACT_MATCHING_SIGNATURE) 
  6  where rn>1;

SQL_TEXT                                                             RN
---------------------------------------- ------------------------------
select * from test where id=4                                         2
select * from test where id=2                                         3
select * from test where id=1                                         4

SQL_TEXT                                                             RN
---------------------------------------- ------------------------------
select * from test where id=‘1‘                                       5

就能够查到以上数据库中没有做绑定变量的sql了。
在metalink上，也有提供的查找绑定变量的sql脚本：
How to Find Literal SQL in Shared Pool [ID 187987.1]
这里列出脚本内容可参考，不过个人觉得脚本一般般，测试环境就几条sql跑了很久很久

[oracle@rac1 ~]$ more find_literal.sql  set serveroutput on set linesize 120 -- -- This anonymous PL/SQL block must be executed as INTERNAL or SYS -- Execute from : SQL*PLUS -- CAUTION: -- This sample program has been tested on Oracle Server - Enterprise Edition -- However, there is no guarantee of effectiveness because of the possibility -- of error in transmitting or implementing it. It is meant to be used as a -- template, and it may require modification. -- declare b_myadr VARCHAR2(20); b_myadr1 VARCHAR2(20); qstring VARCHAR2(100); b_anybind NUMBER; cursor my_statement is select address from v$sql group by address; cursor getsqlcode is select substr(sql_text,1,60) from v$sql where address = b_myadr; cursor kglcur is select kglhdadr from x$kglcursor where kglhdpar = b_myadr and kglhdpar != kglhdadr and kglobt09 = 0; cursor isthisliteral is select kkscbndt from x$kksbv where kglhdadr = b_myadr1; begin dbms_output.enable(10000000); open my_statement; loop Fetch my_statement into b_myadr; open kglcur; fetch kglcur into b_myadr1; if kglcur%FOUND Then open isthisliteral; fetch isthisliteral into b_anybind; if isthisliteral%NOTFOUND Then open getsqlcode; fetch getsqlcode into qstring; dbms_output.put_line(‘Literal:‘||qstring||‘ address: ‘||b_myadr); close getsqlcode; end if; close isthisliteral; end if; close kglcur; Exit When my_statement%NOTFOUND; End loop; close my_statement; end; /

有如下的结论：对SQL语句，去掉重复的空格（不包括字符常量），将大小写转换成相同，比如均为大写（不包括字符常量）后，如果SQL相同，那么SQL语句的exact_matching_signature就是相同的。对SQL语句，去掉重复的空格（不包括字符常量），将大小写转换成相同，比如均为大写（不包括字符常量），然后去掉SQL中的常量，如果SQL相同，那么SQL语句的force_matching_signature就是相同的。但是例外的情况是：如果SQL中有绑定变量，force_matching_signature就会与exact_matching_signature一样的生成标准。