捕獲非綁定變量的SQL語句

之前一直用如下sql來查看非綁定變量的sql，但是不準(zhǔn)

成都創(chuàng)新互聯(lián)公司成立與2013年，是專業(yè)互聯(lián)網(wǎng)技術(shù)服務(wù)公司，擁有項目成都網(wǎng)站建設(shè)、成都網(wǎng)站制作網(wǎng)站策劃，項目實施與項目整合能力。我們以讓每一個夢想脫穎而出為使命，1280元汝陽做網(wǎng)站,已為上家服務(wù),為汝陽各地企業(yè)和個人服務(wù),聯(lián)系電話:13518219792

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

初步的理解應(yīng)該是假定數(shù)據(jù)庫的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.

個人的理解為當(dāng)sql語句進入數(shù)據(jù)庫中時對于一些可以潛在可以共享或者因為綁定變量問題造成游標(biāo)沒有共享的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

參考：關(guān)于高效捕獲數(shù)據(jù)庫非綁定變量的SQL語句

網(wǎng)頁名稱：捕獲非綁定變量的SQL語句
分享網(wǎng)址：http://muchs.cn/article12/jeppdc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供定制網(wǎng)站、小程序開發(fā)、、動態(tài)網(wǎng)站、移動網(wǎng)站建設(shè)、網(wǎng)站營銷

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)

手機網(wǎng)站建設(shè)