Oracle 11g 高效識別未使用綁定變數的 SQL 語句

Oracle 11g 高效識別未使用綁定變數的 SQL 語句

在 Oracle 11g 中，可利用 V$SQL 視圖的 FORCE_MATCHING_SIGNATURE 與 EXACT_MATCHING_SIGNATURE 欄位，快速找出因字面量差異導致無法共享游標（Hard Parse）的 SQL。以下是一條經實戰驗證、精準且高效的查詢語句：

核心查詢：找出共享池中未使用綁定變數的高頻 SQL

SELECT FORCE_MATCHING_SIGNATURE,
       TO_CHAR(FORCE_MATCHING_SIGNATURE) AS SIG,
       COUNT(1) AS REPLACE_NUM,
       ROUND(SUM(SHARABLE_MEM) / 1024 / 1024) AS SHARED_MEM_MB,
       ROUND(SUM(PERSISTENT_MEM) / 1024 / 1024) AS PERSISTENT_MEM_MB,
       ROUND(SUM(RUNTIME_MEM) / 1024 / 1024) AS RUNTIME_MEM_MB,
       ROUND(SUM(CPU_TIME) / SUM(EXECUTIONS) / 1000, 3) AS AVG_CPU_MS,
       ROUND(SUM(ELAPSED_TIME) / SUM(EXECUTIONS) / 1000, 3) AS AVG_ELAPSED_MS
FROM   V$SQL
WHERE  FORCE_MATCHING_SIGNATURE > 0
AND    EXECUTIONS > 0
AND    FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE
GROUP BY FORCE_MATCHING_SIGNATURE
HAVING COUNT(1) > 1
ORDER BY 2 DESC;

後續追蹤：根據簽名查看具體 SQL 文本

SELECT *
FROM   V$SQL
WHERE  FORCE_MATCHING_SIGNATURE = '&FORCE_MATCHING_SIGNATURE';

原理解析

• EXACT_MATCHING_SIGNATURE：SQL 文本完全相同（含字面量、大小寫、空白）時相同。
• FORCE_MATCHING_SIGNATURE：將字面量替換為綁定變數後的簽名。若兩者不同，表示 SQL 僅因字面量不同而產生多個游標（未使用綁定變數）。
• 篩選條件 FORCE_MATCHING_SIGNATURE > 0 排除無簽名的 DDL 等語句；EXECUTIONS > 0 確保已執行；!= EXACT_MATCHING_SIGNATURE 精準定位有字面量差異的 SQL；HAVING COUNT(1) > 1 鎖定至少出現兩次以上的模式。

應用價值

此查詢能幫助 DBA 迅速定位因硬解析導致共享池壓力大、執行計劃不穩定的問題 SQL，指導開發人員改寫為使用綁定變數，從而大幅降低解析開銷、提升系統吞吐量。