前言：

由於各種原因（開發人員的技術水平、開發的管理規範、趕進度），系統在執行一段時間後，發現效能越來越慢。這時候往往是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時，產生的執行計劃是一樣的）

測試1：cursor_sharing值為EXACT

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.

2、同樣執行三條查詢語句

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管理）

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

V$sql查詢未使用繫結變數的語句

相關文章