捕获非绑定变量的SQL语句

之前一直用如下sql来查看非绑定变量的sql，但是不准

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(*) > 1         group by substr(sql_text, 1, 40));SELECT substr(sql_text, 1, 80), count(1)  FROM v$sql GROUP BY substr(sql_text, 1, 80)HAVING count(1) > 1 ORDER BY 2;

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值是不同的

下面在11gr2中做个测试：

MOE@xbtst SQL>select * from test;    DEPTNO DNAME          LOC---------- -------------- -------------        10 ACCOUNTING     NEW YORK        20 RESEARCH       DALLAS        30 SALES          CHICAGO        40 OPERATIONS     BOSTONMOE@xbtst SQL>alter system flush shared_pool;System altered.MOE@xbtst SQL>select * from test where deptno=10;    DEPTNO DNAME          LOC---------- -------------- -------------        10 ACCOUNTING     NEW YORKMOE@xbtst SQL>select * from test where deptno=20;    DEPTNO DNAME          LOC---------- -------------- -------------        20 RESEARCH       DALLASMOE@xbtst SQL>select * from test where deptno=30;    DEPTNO DNAME          LOC---------- -------------- -------------        30 SALES          CHICAGOMOE@xbtst SQL>select * from test where deptno='10';    DEPTNO DNAME          LOC---------- -------------- -------------        10 ACCOUNTING     NEW YORKMOE@xbtst SQL>select * from test where deptno='20';    DEPTNO DNAME          LOC---------- -------------- -------------        20 RESEARCH       DALLASMOE@xbtst SQL>select * from test where deptno='30';    DEPTNO DNAME          LOC---------- -------------- -------------        30 SALES          CHICAGOMOE@xbtst SQL>var v_id numberMOE@xbtst SQL>exec :v_id := 10PL/SQL procedure successfully completed.MOE@xbtst SQL>select * from test where deptno=:v_id;    DEPTNO DNAME          LOC---------- -------------- -------------        10 ACCOUNTING     NEW YORKMOE@xbtst SQL>exec :v_id := 20PL/SQL procedure successfully completed.MOE@xbtst SQL>select * from test where deptno=:v_id;    DEPTNO DNAME          LOC---------- -------------- -------------        20 RESEARCH       DALLASMOE@xbtst SQL>exec :v_id := 30PL/SQL procedure successfully completed.MOE@xbtst SQL>select * from test where deptno=:v_id;    DEPTNO DNAME          LOC---------- -------------- -------------        30 SALES          CHICAGOMOE@xbtst SQL>set line 123MOE@xbtst SQL>col sql_text format a40MOE@xbtst SQL>set numwidth 30MOE@xbtst 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 deptno=20                  1674223644458057282            5701787720123824641select * from test where deptno='20'                1674223644458057282            6624213459289620561select * from test where deptno='30'                1674223644458057282           15799720645668840753select * from test where deptno='10'                1674223644458057282            7423854019058606662select * from test where deptno=30                  1674223644458057282            6295409922938069091select * from test where deptno=10                  1674223644458057282            5918141949209886904select * from test where deptno=:v_id               5038495461207490287            5038495461207490287MOE@xbtst SQL>show parameter cursor_sharNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------cursor_sharing                       string      EXACT

可以看到以上的sql在没有使用绑定变量的sql中FORCE_MATCHING_SIGNATURE值均是相同的而EXACT_MATCHING_SIGNATURE是不同的那么通过以上的sql我们就可以完善出查找没有使用绑定变量的sql语句：

MOE@xbtst SQL>select *  2    from (select sql_text,  3                 row_number() over(partition by FORCE_MATCHING_SIGNATURE order by FORCE_MATCHING_SIGNATURE) rn  4            from v$sql  5           where FORCE_MATCHING_SIGNATURE > 0  6             and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE)  7   where rn > 1;SQL_TEXT                                                             RN---------------------------------------- ------------------------------select * from test where deptno='30'                                  2select * from test where deptno='20'                                  3select * from test where deptno=10                                    4select * from test where deptno=30                                    5select * from test where deptno=20                                    6

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