MISTECH 技術手抄本

Oracle的優化器(Optimizer)有兩種優化方式, Hint也不例外，除了/*+rule*/其他的都是CBO優化方式

• 基於規則的優化方式(Rule-Based Optimization，簡稱為RBO)
• 基於成本的優化方式(Cost-Based Optimization，簡稱為CBO)

RBO(Rule-Based Optimization)方式：優化器在分析SQL語句時,所遵循的是Oracle內部預定的一些規則。比如我們常見的，當一個where子句中的一列有索引時去走索引。
CBO(Cost-Based Optimization)方式：它是看語句的成本(Cost),這裡的成本主要指CPU和記憶體RAM。優化器在判斷是否用這種方式時,主要參照的是表及索引的統計資訊。統計資訊給出表的大小、 有多少行、每行的長度等資訊。這些統計資訊起初在庫內是沒有的，是做 Analyze 後才出現的，很多的時侯過期統計資訊會令優化器做出一個錯誤的執行計畫, 因些應及時更新這些資訊。

優化模式(Optimization)包括Rule、Choose、First rows、All rows四種方式：

•     Rule：基於規則的方式。
•     Choose：預設的情況下Oracle用的便是這種方式。指的是當一個表或索引有統計資訊，則走CBO的方式，如果表或索引沒統計資訊，表又不是特別的小，而且相應的列有索引時，那麼就走索引，走RBO的方式。
•     First Rows：它與Choose方式是類似的，所不同的是當一個表有統計資訊時，它將是以最快的方式返回查詢的最先的幾行，從總體上減少了響應時間。
•     All Rows:也就是我們所說的Cost的方式，當一個表有統計資訊時，它將以最快的方式返回表的所有的行，從總體上提高查詢的輸送量。沒有統計資訊則走RBO的方式

Oracle DB 可以在哪些設定配置上修改預設的優化規則
    A、Instance 層級：我們可以通過在 initSID.ora 檔中設定 OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS 如果沒設定OPTIMIZER_MODE參數則預設用的是Choose方式。
    B、Sessions 層級：可通過ALTER SESSION SET OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS來設定。
    C、SQL語句層級：用Hint（/*+ ... */）來設定

為什麼表的某個欄位明明有索引，但執行計畫卻不走索引，原因有下列幾項：
    1、優化模式是 all_rows 的方式
    2、表作過 Analyze，有統計資訊
    3、表很小，Oracle DB 的優化器認為不值得走索引。

SQL Optimizer Hints的用法：

1. 不區分大小寫, 多個提示用空格分開
如：select /*+ hint1(tab1) hint2(TAB1 idx1) */ col1, col2 from tab1 where col1='xxx';

2. 如果表使用了別名, 那麼提示裡也必須使用別名
如：select /*+ hint1(t1) */ col1, col2 from tab1 t1 where col1='xxx';

3. 如果使用同一個表的多個欄位用逗號 "," 號分開
如： select /*+ index(t1.A,t1.B) */ col1, col2
    from   tab1 t1
    where  col1='xxx';

Oracle DB hints 概念：10g資料庫可以使用更多新的optimizer hints來控制優化行為。現在讓我們快速解析一下這些強大的新hints：

1、spread_min_analysis

   使用這一hint，你可以忽略一些關於如詳細的關係，像依賴圖分析等試算表的編譯時間優化規則。其他的一些優化，如創建過濾以有選擇性的定位試算表訪問結構並限制修訂規則等，得到了繼續使用。

   由於在規則數非常大的情況下，試算表分析 ( Analyze ) 會很長。這一提示可以幫助我們減少由此產生的數以百小時計的編譯時間。

例：SELECT /*+ SPREAD_MIN_ANALYSIS */ ...

2、spread_no_analysis

   通過這一hint，可以使無試算表分析( Analyze )成為可能。同樣，使用這一hint可以忽略修訂規則和過濾產生。如果存在一試算表分析，編譯時間可以被減少到最低程度。

例：SELECT /*+ SPREAD_NO_ANALYSIS */ ...

3、use_nl_with_index

   這項hint使CBO通過嵌套迴圈把特定的表格加入到另一原始行。只有在以下情況中，它才使用特定表格作為內部表格：如果沒有指定標籤，CBO必須可以使 用一些標籤，且這些標籤至少有一個作為索引鍵值加入判斷；反之，CBO必須能夠使用至少有一個作為索引鍵值加入判斷的標籤。

例： SELECT /*+ USE_NL_WITH_INDEX (polrecpolrind) */ ...

4、CARDINALITY

  此hint定義了對由查詢或查詢部分返回的基數的評價。注意如果沒有定義表格，基數是由整個查詢所返回的總行數。

例：SELECT /*+ CARDINALITY ( [tablespec] card ) */

5、SELECTIVITY

  此hint定義了對查詢或查詢部分選擇性的評價。如果只定義了一個表格，選擇性是在所定義表格裡滿足所有單一表格判斷的行部分。如果定義了一系列表格，選擇性是指在合併以任何順序滿足所有可用判斷的全部表格後，所得結果中的行部分。

例： SELECT /*+ SELECTIVITY ( [tablespec] sel ) */

然而，注意如果hints CARDINALITY 和 SELECTIVITY都定義在同樣的一批表格，二者都會被忽略。

6、no_use_nl

  Hint no_use_nl 使CBO執行迴圈嵌套，通過把指定表格作為內部表格，把每個指定表格連接到另一原始行。通過這一hint，只有hash join和sort-merge joins會為指定表格所考慮。

例： SELECT /*+ NO_USE_NL ( employees ) */ ...

7、no_use_merge

  此hint使CBO通過把指定表格作為內部表格的方式，拒絕 sort-merge 把每個指定表格加入到另一原始行。

例： SELECT /*+ NO_USE_MERGE ( employees dept ) */ ...

8、no_use_hash

  此hint使CBO通過把指定表格作為內部表格的方式，拒絕hash joins把每個指定表格加入到另一原始行。

例： SELECT /*+ NO_USE_HASH ( employees dept ) */ ...

9、no_index_ffs

  此hint使CBO拒絕對指定表格的指定標籤進行fast full-index scan。 Syntax: /*+ NO_INDEX_FFS ( tablespecindexspec ) */


在SQL優化過程中常見HINT的用法(前10個比較常用, 前3個最常用)：

1. /*+ INDEX */ 和 /*+ INDEX(TABLE INDEX1, index2) */ 和 /*+ INDEX(tab1.col1 tab2.col2) */ 和 /*+ NO_INDEX */ 和 /*+ NO_INDEX(TABLE INDEX1, index2) */

表明對表選擇索引的掃描方法：
第一種：不指定索引名是讓 oracle 對表中可用索引比較並選擇某個最佳索引;
第二種：指定索引名且可指定多個索引;
第三種：是10g開始有的, 指定列名, 且表名可不用別名;
第四種：即全資料表掃描;
第五種：表示禁用某個索引, 特別適合於準備刪除某個索引前的評估操作. 如果同時使用了 INDEX 和 NO_INDEX 則兩個提示都會被忽略掉.
例如:SELECT /*+ INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX='M';

2. /*+ ORDERED */
FROM子句中默認最後一個表是驅動表,ORDERED將from子句中第一個表作為驅動表. 特別適合於多表連接非常慢時嘗試.
例如:SELECT /*+ ORDERED */ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 C WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;

3. /*+ PARALLEL(table1,DEGREE) */ 和 /*+ NO_PARALLEL(table1) */
該提示會將需要執行全資料表掃描的查詢分成多個部分（並行度）執行, 然後在不同的作業系統進程中處理每個部分. 該提示還可用於DML語句. 如果SQL裡還有排序操作, 進程數會翻倍,此外還有一個一個負責組合這些部分的進程,如下面的例子會產生9個進程. 如果在提示中沒有指定DEGREE, 那麼就會使用創建表時的預設值. 該提示在預設情況下會使用APPEND提示. NO_PARALLEL是禁止平行作業,否則語句會使用由於定義了並行物件而產生的並行處理.
例如:select /*+ PARALLEL(tab_test,4) */ col1, col2 from tab_test order by col2;

4. /*+ FIRST_ROWS */ 和 /*+ FIRST_ROWS(n) */
表示用最快速度獲得第1/n行, 獲得最佳回應時間, 使資源消耗最小化.
在 update 和 delete 語句裡會被忽略, 使用分組語句如 group by/distinct/intersect/minus/union 時也會被忽略.
例如:SELECT /*+ FIRST_ROWS */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

5. /*+ RULE */
表明對語句塊選擇基於規則的優化方法.
例如:SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

6. /*+ FULL(TABLE) */
表明對表選擇全域掃描 Full Table Scan 的方法.
例如:SELECT /*+ FULL(A) */ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='SCOTT';

7. /*+ LEADING(TABLE) */
類似於ORDERED提示, 將指定的表作為連接次序中的驅動表.

8. /*+ USE_NL(TABLE1,TABLE2) */
將指定表與嵌套的連接的行源進行連接,以最快速度返回第一行再連接,與 USE_MERGE 剛好相反.
例如:SELECT /*+ ORDERED USE_NL(BSEMPMS) */ BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

9. /*+ APPEND */ 和 /*+ NOAPPEND */
直接插入到表的最後,該提示不會檢查當前是否有插入操作所需的塊空間而是直接添加到新塊中, 所以可以提高速度. 當然也會浪費些空間, 因為它不會使用那些做了delete操作的塊空間. NOAPPEND提示則相反,所以會取消PARALLEL提示的默認APPEND提示.
例如:insert /*+ append */ into test1 select * from test4;
insert /*+ parallel(test1) noappend */ into test1 select * from test4;

10. /*+ USE_HASH(TABLE1,table2) */
將指定的表與其它行源通過雜湊連接方式連接起來.為較大的結果集提供最佳回應時間. 類似於在連接表的結果中遍歷每個表上每個結果的嵌套迴圈, 指定的hash表將被放入記憶體, 所以需要有足夠的記憶體（hash_area_size或pga_aggregate_target）才能保證語句正確執行, 否則將在磁片裡進行.
例如:SELECT /*+ USE_HASH(BSEMPMS,BSDPTMS) */ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

---------------------------------------------------------------------

11. /*+ USE_MERGE(TABLE) */
將指定的表與其它行源通過合併排序連接方式連接起來.特別適合於那種在多個表大量行上進行集合操作的查詢, 它會將指定表檢索到的的所有行排序後再被合併, 與USE_NL剛好相反.
例如:SELECT /*+ USE_MERGE(BSEMPMS,BSDPTMS) */ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

12. /*+ ALL_ROWS */
表明對語句塊選擇基於開銷的優化方法,並獲得最佳輸送量,使資源消耗最小化. 可能會限制某些索引的使用.
例如:SELECT /*+ ALL+_ROWS */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

13. /*+ CLUSTER(TABLE) */
提示明確表明對指定表選擇簇掃描的存取方法. 如果經常訪問連接表但很少修改它, 那就使用集群提示.
例如:SELECT /*+ CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS WHERE DPT_NO='TEC304' AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

14. /*+ INDEX_ASC(TABLE INDEX1, INDEX2) */
表明對表選擇索引昇冪的掃描方法. 從8i開始, 這個提示和INDEX提示功能一樣, 因為預設oracle就是按照昇冪掃描索引的, 除非未來oracle還推出降冪掃描索引.
例如:SELECT /*+ INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='SCOTT';

15. /*+ INDEX_COMBINE(TABLE INDEX1, INDEX2) */
指定多個點陣圖索引, 對於B樹索引則使用INDEX這個提示,如果INDEX_COMBINE中沒有提供作為參數的索引,將選擇出點陣圖索引的布林組合方式.
例如:SELECT /*+ INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI) */ * FROM BSEMPMS WHERE SAL<5000000 AND HIREDATE<SYSDATE;

16. /*+ INDEX_JOIN(TABLE INDEX1, INDEX2) */
合併索引, 所有資料都已經包含在這兩個索引裡, 不會再去訪問表, 比使用索引並通過rowid去掃描表要快5倍.
例如:SELECT /*+ INDEX_JOIN(BSEMPMS SAL_HMI HIREDATE_BMI) */ SAL,HIREDATE FROM BSEMPMS WHERE SAL<60000;

17. /*+ INDEX_DESC(TABLE INDEX1, INDEX2) */
表明對表選擇索引降冪的掃描方法.
例如:SELECT /*+ INDEX_DESC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='SCOTT';

18. /*+ INDEX_FFS(TABLE INDEX_NAME) */
對指定的表執行快速全索引掃描,而不是全資料表掃描的辦法.要求要檢索的列都在索引裡, 如果表有很多列時特別適用該提示.
例如:SELECT /*+ INDEX_FFS(BSEMPMS IN_EMPNAM) */ * FROM BSEMPMS WHERE DPT_NO='TEC305';

19. /*+ NO_EXPAND */
對於WHERE後面的OR 或者IN-LIST的查詢語句,NO_EXPAND將阻止其基於優化器對其進行擴展, 縮短解析時間.
例如:SELECT /*+ NO_EXPAND */ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';

20. /*+ DRIVING_SITE(TABLE) */
強制與ORACLE所選擇的位置不同的表進行查詢執行.特別適用於通過dblink連接的遠端表.
例如:SELECT /*+ DRIVING_SITE(DEPT) */ * FROM BSEMPMS,DEPT@BSDPTMS DEPT WHERE BSEMPMS.DPT_NO=DEPT.DPT_NO;

21. /*+ CACHE(TABLE) */ 和 /*+ NOCACHE(TABLE) */
當進行全資料表掃描時,CACHE提示能夠將表全部緩存到記憶體中,這樣訪問同一個表的使用者可直接在記憶體中查找資料. 比較適合資料量小但常被訪問的表, 也可以建表時指定cache選項這樣在第一次訪問時就可以對其緩存. NOCACHE則表示對已經指定了CACHE選項的表不進行緩存.
例如:SELECT /*+ FULL(BSEMPMS) CAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;

22. /*+ PUSH_SUBQ */
當SQL裡用到了子查詢且返回相對少的行時, 該提示可以盡可能早對子查詢進行評估從而改善性能, 不適用於合併連接或帶遠端表的連接.
例如:select /*+ PUSH_SUBQ */ emp.empno, emp.ename, itemno from emp, orders where emp.empno = orders.empno and emp.deptno = (select deptno from dept where loc='XXX');
遠端連接其他資料庫，注意判斷資料庫是否啟動，或者是否有需要的表，否則會出錯

23. /*+ INDEX_SS(TABLE INDEX1,INDEX2) */
指示對特定表的索引使用跳躍掃描, 即當組合索引的第一列不在where子句中時, 讓其使用該索引

參考資料：

http://docs.oracle.com/cd/E11882_01/server.112/e16638/hintsref.htm#autoId0

http://jojo117.iteye.com/blog/584845