捕獲非綁定變量的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     BOSTON

MOE@xbtst SQL>alter system flush shared_pool;

System altered.

MOE@xbtst SQL>select * from test where deptno=10;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

MOE@xbtst SQL>select * from test where deptno=20;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        20 RESEARCH       DALLAS

MOE@xbtst SQL>select * from test where deptno=30;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO

MOE@xbtst SQL>select * from test where deptno='10';

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

MOE@xbtst SQL>select * from test where deptno='20';

    DEPTNO DNAME          LOC
---------- -------------- -------------
        20 RESEARCH       DALLAS

MOE@xbtst SQL>select * from test where deptno='30';

    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO

MOE@xbtst SQL>var v_id number
MOE@xbtst SQL>exec :v_id := 10

PL/SQL procedure successfully completed.

MOE@xbtst SQL>select * from test where deptno=:v_id;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

MOE@xbtst SQL>exec :v_id := 20

PL/SQL procedure successfully completed.

MOE@xbtst SQL>select * from test where deptno=:v_id;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        20 RESEARCH       DALLAS

MOE@xbtst SQL>exec :v_id := 30

PL/SQL procedure successfully completed.

MOE@xbtst SQL>select * from test where deptno=:v_id;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO

MOE@xbtst SQL>set line 123
MOE@xbtst SQL>col sql_text format a40
MOE@xbtst SQL>set numwidth 30
MOE@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            5701787720123824641
select * from test where deptno='20'                1674223644458057282            6624213459289620561
select * from test where deptno='30'                1674223644458057282           15799720645668840753
select * from test where deptno='10'                1674223644458057282            7423854019058606662
select * from test where deptno=30                  1674223644458057282            6295409922938069091
select * from test where deptno=10                  1674223644458057282            5918141949209886904
select * from test where deptno=:v_id               5038495461207490287            5038495461207490287

MOE@xbtst SQL>show parameter cursor_shar

NAME                                 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'                                  2
select * from test where deptno='20'                                  3
select * from test where deptno=10                                    4
select * from test where deptno=30                                    5
select * from test where deptno=20                                    6

參考：關于高效捕獲數據庫非綁定變量的SQL語句