Oracle-Hints詳解

https://www.2cto.com/database/201611/568701.html

概述

先了解一下Oracle的優化器：

RBO: Rule-Based Optimization 基于規則的優化器

RBO自ORACLE 6以來被采用，一直沿用至ORACLE 9i. ORACLE 10g開始，ORACLE已經徹底丟棄了RBO

CBO: Cost-Based Optimization 基于代價的優化器。

CBO優化器根據SQL語句生成一組可能被使用的執行計劃，估算出每個執行計劃的代價，并調用計劃生成器(Plan Generator)生成執行計劃，比較執行計劃的代價，最終選擇選擇一個代價最小的執行計劃。

CBO由以下組件構成： 查詢轉化器(Query Transformer) 、代價評估器(Estimator)、 計劃生成器(Plan Generator)

在Oracle 10g中，CBO 可選的運行模式有2種：

(1) FIRST_ROWS(n)

(2) ALL_ROWS – 10g中的默認值

查看CBO 模式：

SQL> show parameter optimizer_mode

NAME TYPE VALUE

---

optimizer_mode string ALL_ROWS

修改CBO 模式的三種方法：

(1) Sessions級別

SQL> alter session set optimizer_mode=all_rows;

(2) 系統級別

修改pfile 參數：

OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS

(3) 語句級別

用Hint(/* + … */)來設定

Select /*+ first_rows(10) */ name from table;

Select /*+ all_rows */ name from table;

基于代價的優化器(CBO)是很聰明的，在絕大多數情況下它會選擇正確的優化器，減輕了DBA的負擔。但有時它也聰明反被聰明誤，選擇了很差的執行計劃，使某個語句的執行變得奇慢無比。

此時就需要DBA進行人為的干預，告訴優化器使用我們指定的存取路徑或連接類型生成執行計劃，從而使語句高效的運行。例如，如果我們認為對于一個特定的語句，執行全表掃描要比執行索引掃描更有效，則我們就可以指示優化器使用全表掃描。

在Oracle中，是通過為語句添加 Hints(提示)來實現干預優化器優化的目的。

Oracle Hints是一種機制，用來告訴優化器按照我們的告訴它的方式生成執行計劃。

我們可以用Oracle Hints來實現：

使用的優化器的類型 2) 基于代價的優化器的優化目標，是all_rows還是first_rows。 3) 表的訪問路徑，是全表掃描，還是索引掃描，還是直接利用rowid。 4) 表之間的連接類型 5) 表之間的連接順序 6) 語句的并行程度

在使用Hint時需要注意的一點是，并非任何時刻Hint都起作用。 導致HINT 失效的原因有如下2點：

如果CBO 認為使用Hint 會導致錯誤的結果時，Hint將被忽略。

如索引中的記錄因為空值而和表的記錄不一致時，結果就是錯誤的，會忽略hint。

如果表中指定了別名，那么Hint中也必須使用別名，否則Hint也會忽略。

Select /+full(a)/ * from t a; -- 使用hint

Select /*+full(t) */ * from t a; --不使用hint

語法

{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */

or

{DELETE|INSERT|SELECT|UPDATE} --+ hint [text] [hint[text]]...

DELETE、INSERT、SELECT和UPDATE是標識一個語句塊開始的關鍵字，包含提示的注釋只能出現在這些關鍵字的后面，否則提示無效。 2) “+”號表示該注釋是一個Hints，該加號必須立即跟在”/*”的后面，中間不能有空格。 3) hint是下面介紹的具體提示之一，如果包含多個提示，則每個提示之間需要用一個或多個空格隔開。 4) text 是其它說明hint的注釋性文本

如果你沒有正確的指定Hints，Oracle將忽略該Hints，并且不會給出任何錯誤。

Hints類型

另：每個SELECT/INSERT/UPDATE/DELETE命令后只能有一個/+ /，但提示內容可以有多個，可以用逗號分開，空格也可以。

如：/*+ ordered index() use_nl() */

在SQL語句優化過程中，我們經常會用到hint,現總結一下在SQL優化過程中常見Oracle HINT的用法：

1./+ALL_ROWS/

表明對語句塊選擇基于開銷的優化方法,并獲得最佳吞吐量,使資源消耗最小化.

當CBO 模式設置為ALL_ROWS時，Oracle 會用最快的速度將SQL執行完畢，將結果集全部返回，它和FIRST_ROWS(n)的區別在于，ALL_ROWS強調以最快的速度將SQL執行完畢，并將所有的結果集反饋回來，而FIRST_ROWS(n)則側重于返回前n條記錄的執行時間。

SELECT /+ALL+_ROWS/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

2./+FIRST_ROWS/

表明對語句塊選擇基于開銷的優化方法,并獲得最佳響應時間,使資源消耗最小化.

SELECT /+FIRST_ROWS/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

當CBO 的優化模式設置為FIRST_ROWS(n)時，Oracle 在執行SQL時，優先考慮將結果集中的前n條記錄以最快的速度反饋回來，而其他的結果并不需要同時返回。

這種需求在一些網站或者BBS的分頁上經常看到，比如每次只顯示查詢信息的前20條或者BBS上的前20個帖子， 這時候設置FIRST_ROWS(20)就非常合適，優化器并不需要同事將所有符合條件的結果返回，用戶也不需要。這時，CBO將考慮用一種最快的返回前20條記錄的執行計劃，這種執行計劃對于SQL的整體執行時間也不不是最快的，但是在返回前20條記錄的處理上，確實最快的。

Select /*+ first_rows(10) */b.x,b.y from

(

Select /*+ first_rows(10) / a., rownum rnum from

(

Select /*+ first_rows(20) */ * from t order by x

) a

Where rownum < 20

) b where rnum >=10;
分頁例子中，每次從結果集中取10條記錄，記錄按照x字段排序。

注意： 排序使用的字段x 必須創建有索引，否則CBO 會忽略FIRST_ROWS(n)，而使用ALL_ROWS.

3./+CHOOSE/

表明如果數據字典中有訪問表的統計信息,將基于開銷的優化方法,并獲得最佳的吞吐量;

表明如果數據字典中沒有訪問表的統計信息,將基于規則開銷的優化方法;

SELECT /+CHOOSE/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

/+RULE/

表明對語句塊選擇基于規則的優化方法.

例如:

SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

/+FULL(TABLE)/

表明對表選擇全局掃描的方法.

該Hint告訴優化器對指定的表通過全表掃描的方式訪問數據。

例如:

SELECT /+FULL(A)/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='SCOTT';

要注意，如果表有別名，在hint里也要用別名

/+ROWID(TABLE)/

提示明確表明對指定表根據ROWID進行訪問.

例如:

SELECT /+ROWID(BSEMPMS)/ * FROM BSEMPMS WHERE ROWID>='AAAAAAAAAAAAAA'

AND EMP_NO='SCOTT';

/+CLUSTER(TABLE)/

提示明確表明對指定表選擇簇掃描的訪問方法,它只對簇對象有效.

例如:

SELECT /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS

WHERE DPT_NO='TEC304' AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

/+INDEX(TABLE INDEX_NAME)/

表明對表選擇索引的掃描方法.

Index hint 告訴優化器對指定的表通過索引的方式訪問數據，當訪問索引會導致結果集不完整時，優化器會忽略這個Hint。

例如:

SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX='M';

謂詞里有索引字段，才會用索引。

/+INDEX_ASC(TABLE INDEX_NAME)/

表明對表選擇索引升序的掃描方法.

例如:

SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='SCOTT';

/+INDEX_COMBINE/

為指定表選擇位圖訪問路經,如果INDEX_COMBINE中沒有提供作為參數的索引,將選擇出位圖索引的布爾組合方式.

例如:

SELECT /+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI)/ * FROM BSEMPMS

WHERE SAL<5000000 AND HIREDATE11. /+INDEX_JOIN(TABLE INDEX_NAME)/提示明確命令優化器使用索引作為訪問路徑.

例如:SELECT /+INDEX_JOIN(BSEMPMS SAL_HMI HIREDATE_BMI)/ SAL,HIREDATE

FROM BSEMPMS WHERE SAL<60000;12. /+INDEX_DESC(TABLE INDEX_NAME)/表明對表選擇索引降序的掃描方法.例如:SELECT /+INDEX_DESC(BSEMPMS PK_BSEMPMS) / FROM BSEMPMS WHERE DPT_NO='SCOTT';13. /+INDEX_FFS(TABLE INDEX_NAME)/對指定的表執行快速全索引掃描,而不是全表掃描的辦法.例如:SELECT /+INDEX_FFS(BSEMPMS IN_EMPNAM)/ * FROM BSEMPMS WHERE DPT_NO='TEC305';

/+ADD_EQUAL TABLE INDEX_NAM1,INDEX_NAM2,.../提示明確進行執行規劃的選擇,將幾個單列索引的掃描合起來.例如:SELECT /+INDEX_FFS(BSEMPMS IN_DPTNO,IN_EMPNO,IN_SEX)/ * FROM BSEMPMS WHERE EMP_NO='SCOTT' AND DPT_NO='TDC306';15. /+USE_CONCAT/對查詢中的WHERE后面的OR條件進行轉換為UNION ALL的組合查詢.例如:SELECT /+USE_CONCAT/ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';16. /+NO_EXPAND/對于WHERE后面的OR 或者IN-LIST的查詢語句,NO_EXPAND將阻止其基于優化器對其進行擴展.例如:SELECT /+NO_EXPAND/ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';17. /+NOWRITE/禁止對查詢塊的查詢重寫操作.18. /+REWRITE/可以將視圖作為參數.19. /+MERGE(TABLE)/能夠對視圖的各個查詢進行相應的合并.例如:SELECT /*+MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELET DPT_NO

,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO

AND A.SAL>V.AVG_SAL;20. /+NO_MERGE(TABLE)/對于有可合并的視圖不再合并.例如:SELECT /+NO_MERGE(V) / A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELECT DPT_NO,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO AND A.SAL>V.AVG_SAL;21. /+ORDERED/根據表出現在FROM中的順序,ORDERED使ORACLE依此順序對其連接.該hint 告訴Oracle 按照From后面的表的順序來選擇驅動表，Oracle 建議在選擇驅動表上使用Leading，它更靈活一些。例如: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;22. /+USE_NL(TABLE)/多表連接的三種方式詳解 HASH JOIN MERGE JOIN NESTED LOOP將指定表與嵌套的連接的行源進行連接,并把指定表作為內部表.在多表關聯查詢中，指定使用nest loops方式進行多表關聯。例如:SELECT /+ORDERED USE_NL(BSEMPMS)/ BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;23. /+USE_MERGE(TABLE)/多表連接的三種方式詳解 HASH JOIN MERGE JOIN NESTED LOOP將指定的表與其他行源通過合并排序連接方式連接起來.在多表關聯查詢中，指定使用merge join方式進行多表關聯。例如:SELECT /+USE_MERGE(BSEMPMS,BSDPTMS)/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;24. /+USE_HASH(TABLE)/多表連接的三種方式詳解 HASH JOIN MERGE JOIN NESTED LOOP將指定的表與其他行源通過哈希連接方式連接起來.在多表關聯查詢中，指定使用hash join方式進行多表關聯。例如:SELECT /+USE_HASH(BSEMPMS,BSDPTMS)/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

/+DRIVING_SITE(TABLE)/強制與ORACLE所選擇的位置不同的表進行查詢執行.例如:SELECT /+DRIVING_SITE(DEPT)/ * FROM BSEMPMS,DEPT@BSDPTMS WHERE BSEMPMS.DPT_NO=DEPT.DPT_NO;26. /+LEADING(TABLE)/將指定的表作為連接次序中的首表.在一個多表關聯的查詢中，該Hint指定由哪個表作為驅動表，告訴優化器首先要訪問哪個表上的數據。 select /+leading(t1,t) / * from scott.dept t,scott.emp t1 where t.deptno=t1.deptno;27. /+CACHE(TABLE)/在全表掃描操作中，如果使用這個提示，Oracle 會將掃描的到的數據塊放到LRU(least recently Used： 最近很少被使用列表，是Oracle 判斷內存中數據塊活躍程度的一個算法)列表的最被使用端(數據塊最活躍端)，這樣數據塊就可以更長時間地駐留在內存當中。如果有一個經常被訪問的小表，這個設置會提高查詢的性能;同時CACHE也是表的一個屬性，如果設置了表的cache屬性，它的作用和hint一樣，在一次全表掃描之后，數據塊保留在LRU列表的最活躍端。例如:SELECT /+FULL(BSEMPMS) CAHE(BSEMPMS) / EMP_NAM FROM BSEMPMS;28. /+NOCACHE(TABLE)/例如:SELECT /+FULL(BSEMPMS) NOCAHE(BSEMPMS) / EMP_NAM FROM BSEMPMS;29. /+APPEND/直接插入到表的最后,可以提高速度.提示數據庫以直接加載的方式(direct load)將數據加載入庫。這個hint 用的比較多。 尤其在插入大量的數據，一般都會用此hint。insert /+append/ into test1 select * from test4 ;30. /+NOAPPEND/通過在插入語句生存期內停止并行模式來啟動常規插入.insert /+noappend/ into test1 select * from test4 ;31. NO_INDEX: 指定不使用哪些索引/+ NO_INDEX ( table [index [index]…] ) / select /+ no_index(emp ind_emp_sal ind_emp_deptno)/ * from emp where deptno=200 and sal>300;并行執行相關的Hintparallel在sql中指定執行的并行度，這個值將會覆蓋自身的并行度select /+ parallel(emp,4)/ * from emp where deptno=200 and sal>300;
關于表的并行度，我們在創建表的時候可以指定，如：SQL> CREATE TABLE Anqing

2 (

3 name VARCHAR2 (10)

4 )

5 PARALLEL 2;表已創建。SQL> select degree from all_tables where table_name = 'ANQING'; -- 查看表的并行度

DEGREE

2SQL> alter table anqing parallel(degree 3); -- 修改表的并行度

表已更改。

SQL> select degree from all_tables where table_name = 'ANQING';

DEGREE

SQL> alter table anqing noparallel; -- 取消表的并行度

表已更改。

SQL> select degree from all_tables where table_name = 'ANQING';

DEGREE

no_parallel在sql中指定執行的不使用并行select /*+ no_parallel(t) / count() from t;

總結

以上是生活随笔為你收集整理的Oracle-Hints详解的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。