[SQL Tune] Hard Parse vs. Soft Parse

Parse 是SQL在執行前的一個重要步驟，也是DBA在調整效能時的一個重要參考指標. Parse可以分為Hard-Parse和Soft-Parse，其介紹如下：

- Hard parse
當 SQL執行時，當Oracle發現在shared pool中找不到相同的SQL時，Oracle就會做Hard-Parse的動作. 他包含以下動作：
* 檢查該SQL的語法
* 檢查執行該SQL的相關權限
* 在shared pool中配置memory給該SQL.
* 做查詢轉換(Query Transformation)：當有用到Materialized View時做Table的轉換.
* 最佳化(Optimization)：就是產生 Execution Plan，這大概是最耗費CPU的動作.
* 產生執行物件（用VB的講法就是執行檔）
由上面的動作可以知道，Hard-Parse是一個昂貴的動作. 所謂昂貴就是只從Latch的使用到CPU時間的耗用等等.

- Soft parse
相較於Hard-Parse，如果該SQL在shared pool中找到相同的SQL時，Oracle就會做Soft-Parse的動作. 它只會包含前述的步驟1-2. 至於何謂相同的SQL？這時就要瞭解何謂Bind Variable?以下兩個SQL對Oracle來說就是不同的SQL.

select * from X1 where A1='1';
select * from X1 where A1='2';

使用Bind Vriable，才可以將兩句SQL的語法統一如下：

select * from X1 where A1=:X;
:X='1'

至於程式是否使用到Bind Variable則端視應用系統的架構而定，本人服務的公司使用的MES系統是3-tier，但是就是中間層，也就是Application Server並未使用Bind Variable，造成DBA在校調系統時碰到很大的困難. 因為整個系統的延展性(Scalable)很差，導因於每個SQL都被Oracle視為不同的SQL，都要做一次編譯，CPU資源的耗費可想而知.

所以Bind Variable雖然是一個簡單的觀念，卻是開發Oracle應用程式時必要的觀念！

Tom有更精闢的解說在此!

[SQL Tune] 用 Oracle Constraint 改善 Query 效能

多數人在 tune SQL 採用的方式多從 Index 或 Join的方式著手. Oracle Optimizer在決定一個 Execution Plan時會有以下的考量:

1. The query to optimize
2. All available database object statistics
3. System statistics(CPU, I/O,..)
4. Initialization parameters
5. Constraints

看到第五項嗎? 原來Constraint也是Oracle在決定Execution Plan時的考慮因素之ㄧ. Constraint對多數人而言是確保資料一致性的最後一道關卡, 但對SQL效能而言也有一定幫助, 且看以下說明:

Constraint --> Check

當SQL遇到 Check constraint時, 若 Check有限制某欄位的值而查詢條件又恰好有該值, 這時Oracle就會根據 Check的限制調整實際SQL的 Execution Plan.

實際範例可參閱Oracle 雜誌 5/6月 Asktom的文章.

http://www.oracle.com/technology/oramag/oracle/09-may/o39asktom.html

這部分在真實狀況下發生的機會較低, 因此不多說明.

Constraint --> NOT NULL

這個情況比較清楚, 就是當某欄位有Index, 但允許空值, 此時空值部份不會被存入 Index中. 但若是該欄位設定為 Not Null, 則當此類SQL(Select Count(*) from Table1 t) 時, Oracle 會很聰明的選取"Index Fast Full Scan"而不用"Full Table Scan", 效能就有一定的提升, 因為該欄位的所有值都對被存入 Index中. 有關"Index Fast Full Scan", 可參閱以下文章.

http://oracle-wei.blogspot.com/2009/03/sql-tune-index-access-methods.html

Constraint --> Primary Key/Foreign Key

這部份對效能有更顯著的提升, 因為 Oracle做的是 Table Elimination, 就是將執行 SQL所需的 Table刪除. 甚麼? 我也是第一次聽到耶! 比較實際的例子用以下說明:

Master Table --> M1 (M_Key, M_Value)

Detail Table --> D1 (M_Key)

有一個SQL如下:
select Sum(M_Value)
from M1, D1
where M1.M_Key=D1.M_Key

可能 Oracle的 Execution Plan是 "Hash Join"就是將 Master和 Detail做 Join後去得到結果.

但如果將 M_Key 加成 D1對M1的 Foreign Key, 而且 M_key為 M1的 Primary Key. 則方才 SQL的 Execution Plan可能會換成只對M1的存取. 因為以實際而言, Ｄ1 的存取並不需要, 只是 Constraint確定了這一件事情. 讓 Oracle 放手去做.

不好意思, 偷懶一下這次沒有用自己跑的實例說明, AskTom的說明應該夠清楚也有說服力.