[Oracle Debug] SQL Tuning For Dealing with Hard Parse

* Finding the Problem

最近在處理DB突然在某個時間點會突然無反應的問題
發現Shared Pool被塞爆
觀察了Oracle AWR Report，覺得可能是某句SQL的Hard Parse次數過高所造成的
這幾天，該SQL的Hard Parse累計竟然有2000多萬次...
這句本來就有使用綁定變量的方式避免Hard Parse

SELECT 'Y'
INTO v_check
FROM tableA a
WHERE a.colA = v_colA_value
    and a.colB = v_colB_value

* 解決

1. 使用v$sqlarea分析

這個Table可以用sql_id去查為什麼無法使用Soft Parse
而這次出事SQL是optimizer_mismatch
表示Oracle DB在搜尋最佳路徑(執行計畫)時，無法使用上次執行的結果
造成每一次執行SQL時都要再重新尋找一次最佳路徑

2. SQL Tuning

雖然知道每次都Hard Parse的原因是什麼，但是根本不知道為什麼會每次要重新搜尋最佳路徑
偶然發現這篇：Oracle AWR - high SQL Parse Calls but 0 Executions
Atmega的解答(最下面那一個解答)完全不起眼，可是對這次事件卻很有用

Oracle parser doesn't cache parsed queries with "*" so there is additional parse operation per every insert.

Oracle Parser不會對「 * 」產生Cache，所以每次都會要重新Parse

就在猜測直接給定'Y'是否也是一樣的道理，這種語法根本不會產生Cache

* Result

後來將SQL改為，Hard Parse大為改善!!

SELECT decode(count(*), 0, 'N', 'Y')
INTO v_check
FROM tableA a
WHERE a.colA = v_colA_value
    and a.colB = v_colB_value