概述

先了解一下Oracle的優(yōu)化器：

• RBO: Rule-Based Optimization 基于規(guī)則的優(yōu)化器

  RBO自O(shè)RACLE 6以來(lái)被采用，一直沿用至ORACLE 9i. ORACLE 10g開(kāi)始，ORACLE已經(jīng)徹底丟棄了RBO，但是依然保留了這個(gè)規(guī)則

• CBO: Cost-Based Optimization 基于代價(jià)的優(yōu)化器。

  CBO優(yōu)化器根據(jù)SQL語(yǔ)句生成一組可能被使用的執(zhí)行計(jì)劃，估算出每個(gè)執(zhí)行計(jì)劃的代價(jià)，并調(diào)用計(jì)劃生成器（Plan Generator）生成執(zhí)行計(jì)劃，比較執(zhí)行計(jì)劃的代價(jià)，最終選擇選擇一個(gè)代價(jià)最小的執(zhí)行計(jì)劃。
  CBO由以下組件構(gòu)成： 查詢轉(zhuǎn)化器（Query Transformer） 、代價(jià)評(píng)估器（Estimator）、 計(jì)劃生成器（Plan Generator）

---

在Oracle 10g中，CBO 可選的運(yùn)行模式有2種：
（1） FIRST_ROWS(n)
（2） ALL_ROWS – 10g中的默認(rèn)值

查看CBO 模式：

SQL> show parameter optimizer_mode NAME TYPE VALUE ------------------------------------ ----------- ------------ optimizer_mode string ALL_ROWS

修改CBO 模式的三種方法：
（1） Sessions級(jí)別

SQL> alter session set optimizer_mode=all_rows;

（2） 系統(tǒng)級(jí)別

pfile 參數(shù)：

OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS

或者

SQL> alter system set optimizer_mode=all_rows;

Oracle提供了使用Hint的方式在SQL中設(shè)定優(yōu)化器的類型為CBO或者RBO。

（3） 語(yǔ)句級(jí)別

用Hint（/*+ ... */）來(lái)設(shè)定

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

---

基于代價(jià)的優(yōu)化器(CBO)是很聰明的，在絕大多數(shù)情況下它會(huì)選擇正確的優(yōu)化器，減輕了DBA的負(fù)擔(dān)。但有時(shí)它也聰明反被聰明誤，選擇了很差的執(zhí)行計(jì)劃，使某個(gè)語(yǔ)句的執(zhí)行變得奇慢無(wú)比。

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

在Oracle中，是通過(guò)為語(yǔ)句添加 Hint(提示)來(lái)實(shí)現(xiàn)干預(yù)優(yōu)化器優(yōu)化的目的。

Oracle Hint是一種機(jī)制，用來(lái)告訴優(yōu)化器按照我們的告訴它的方式生成執(zhí)行計(jì)劃。

我們可以用Oracle Hint來(lái)實(shí)現(xiàn)：

• 1) 使用的優(yōu)化器的類型
• 2) 基于代價(jià)的優(yōu)化器的優(yōu)化目標(biāo)，是all_rows還是first_rows。
• 3) 表的訪問(wèn)路徑，是全表掃描，還是索引掃描，還是直接利用rowid。
• 4) 表之間的連接類型
• 5) 表之間的連接順序
• 6) 語(yǔ)句的并行程度

在使用Hint時(shí)需要注意的一點(diǎn)是，并非任何時(shí)刻Hint都起作用。

導(dǎo)致HINT 失效的原因有如下幾點(diǎn)：

• 如果CBO 認(rèn)為使用Hint 會(huì)導(dǎo)致錯(cuò)誤的結(jié)果時(shí)，Hint將被忽略。

• 如索引中的記錄因?yàn)榭罩刀捅淼挠涗洸灰恢聲r(shí)，結(jié)果就是錯(cuò)誤的，會(huì)忽略hint。

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

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

---

語(yǔ)法

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

• 1) DELETE、INSERT、SELECT和UPDATE是標(biāo)識(shí)一個(gè)語(yǔ)句塊開(kāi)始的關(guān)鍵字，包含提示的注釋只能出現(xiàn)在這些關(guān)鍵字的后面，否則提示無(wú)效。
• 2) “+”號(hào)表示該注釋是一個(gè)Hint，該加號(hào)必須立即跟在”/*”的后面，中間不能有空格。
• 3) hint是下面介紹的具體提示之一，如果包含多個(gè)提示，則每個(gè)提示之間需要用一個(gè)或多個(gè)空格隔開(kāi)。
• 4) text 是其它說(shuō)明hint的注釋性文本

如果你沒(méi)有正確的指定Hint，Oracle將忽略該Hint，并且不會(huì)給出任何錯(cuò)誤。

另：每個(gè)SELECT/INSERT/UPDATE/DELETE命令后只能有一個(gè)/+ /，但提示內(nèi)容可以有多個(gè)，可以用逗號(hào)分開(kāi)，空格也可以。

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

---

Hint分類

優(yōu)化器模式Hint

select /*+ rule */ * from t where id < 20; select /*+ first_rows(20) */ * from t where id < 20; select /*+ all_rows */ * from t where id < 20;

---

訪問(wèn)路徑Hint

---

表連接順序Hint

---

表關(guān)聯(lián)方式Hint

---

并行執(zhí)行Hint

---

查詢轉(zhuǎn)換Hint

---

其他Hint

---

Hint詳解

在SQL語(yǔ)句優(yōu)化過(guò)程中，我們經(jīng)常會(huì)用到hint,現(xiàn)總結(jié)一下在SQL優(yōu)化過(guò)程中常見(jiàn)Oracle HINT的用法：

1./*+ALL_ROWS*/

表明對(duì)語(yǔ)句塊選擇基于開(kāi)銷的優(yōu)化方法,并獲得最佳吞吐量,使資源消耗最小化.

當(dāng)CBO 模式設(shè)置為ALL_ROWS時(shí)，Oracle 會(huì)用最快的速度將SQL執(zhí)行完畢，將結(jié)果集全部返回，它和FIRST_ROWS（n）的區(qū)別在于，ALL_ROWS強(qiáng)調(diào)以最快的速度將SQL執(zhí)行完畢，并將所有的結(jié)果集反饋回來(lái)，而FIRST_ROWS（n）則側(cè)重于返回前n條記錄的執(zhí)行時(shí)間。

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

---

2./*+FIRST_ROWS*/

表明對(duì)語(yǔ)句塊選擇基于開(kāi)銷的優(yōu)化方法,并獲得最佳響應(yīng)時(shí)間,使資源消耗最小化.

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

當(dāng)CBO 的優(yōu)化模式設(shè)置為FIRST_ROWS（n）時(shí)，Oracle 在執(zhí)行SQL時(shí)，優(yōu)先考慮將結(jié)果集中的前n條記錄以最快的速度反饋回來(lái)，而其他的結(jié)果并不需要同時(shí)返回。

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

Select /*+ first_rows(10) */b.x, b.yfrom (Select /*+ first_rows(10) */a.*, rownum rnumfrom (Select /*+ first_rows(20) */*from torder by x) aWhere rownum < 20) bwhere rnum >= 10;

分頁(yè)例子中，每次從結(jié)果集中取10條記錄，記錄按照x字段排序。

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

---

3./*+CHOOSE*/

　表明如果數(shù)據(jù)字典中有訪問(wèn)表的統(tǒng)計(jì)信息,將基于開(kāi)銷的優(yōu)化方法,并獲得最佳的吞吐量;
　表明如果數(shù)據(jù)字典中沒(méi)有訪問(wèn)表的統(tǒng)計(jì)信息,將基于規(guī)則開(kāi)銷的優(yōu)化方法;

SELECT /*+CHOOSE*/EMP_NO, EMP_NAM, DAT_INFROM BSEMPMSWHERE EMP_NO = 'SCOTT';

---

4. /*+RULE*/

表明對(duì)語(yǔ)句塊選擇基于規(guī)則的優(yōu)化方法.
例如:

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

---

5. /*+FULL(TABLE)*/

表明對(duì)表選擇全局掃描的方法.

該Hint告訴優(yōu)化器對(duì)指定的表通過(guò)全表掃描的方式訪問(wèn)數(shù)據(jù)。
例如:

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

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

---

6. /*+ROWID(TABLE)*/

提示明確表明對(duì)指定表根據(jù)ROWID進(jìn)行訪問(wèn).
例如:

SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS WHERE ROWID>='AAAAAAAAAAAAAA' AND EMP_NO='SCOTT';

---

7. /*+CLUSTER(TABLE)*/

提示明確表明對(duì)指定表選擇簇掃描的訪問(wèn)方法,它只對(duì)簇對(duì)象有效.
例如:

SELECT /*+CLUSTER */BSEMPMS.EMP_NO, DPT_NOFROM BSEMPMS, BSDPTMSWHERE DPT_NO = 'TEC304'AND BSEMPMS.DPT_NO = BSDPTMS.DPT_NO;

---

8. /*+INDEX(TABLE INDEX_NAME)*/

表明對(duì)表選擇索引的掃描方法.
Index hint 告訴優(yōu)化器對(duì)指定的表通過(guò)索引的方式訪問(wèn)數(shù)據(jù)，當(dāng)訪問(wèn)索引會(huì)導(dǎo)致結(jié)果集不完整時(shí)，優(yōu)化器會(huì)忽略這個(gè)Hint。

例如:

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

謂詞里有索引字段，才會(huì)用索引。

---

9. /*+INDEX_ASC(TABLE INDEX_NAME)*/

表明對(duì)表選擇索引升序的掃描方法.
例如:

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

---

10. /*+INDEX_COMBINE*/

為指定表選擇位圖訪問(wèn)路經(jīng),如果INDEX_COMBINE中沒(méi)有提供作為參數(shù)的索引,將選擇出位圖索引的布爾組合方式.
例如:

SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI)*/ * FROM BSEMPMS WHERE SAL<5000000 AND HIREDATE<SYSDATE;

---

11. /*+INDEX_JOIN(TABLE INDEX_NAME)*/

提示明確命令優(yōu)化器使用索引作為訪問(wèn)路徑.
例如:

SELECT /*+INDEX_JOIN(BSEMPMS SAL_HMI HIREDATE_BMI)*/SAL, HIREDATEFROM BSEMPMSWHERE SAL < 60000;

---

12. /*+INDEX_DESC(TABLE INDEX_NAME)*/

表明對(duì)表選擇索引降序的掃描方法.
例如:

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

---

13. /*+INDEX_FFS(TABLE INDEX_NAME)*/

對(duì)指定的表執(zhí)行快速全索引掃描,而不是全表掃描的辦法.
例如:

SELECT /*+INDEX_FFS(BSEMPMS IN_EMPNAM)*/ * FROM BSEMPMS WHERE DPT_NO='TEC305';

---

14. /*+ADD_EQUAL TABLE INDEX_NAM1,INDEX_NAM2,...*/

提示明確進(jìn)行執(zhí)行規(guī)劃的選擇,將幾個(gè)單列索引的掃描合起來(lái).
例如:

SELECT /*+INDEX_FFS(BSEMPMS IN_DPTNO,IN_EMPNO,IN_SEX)*/ * FROM BSEMPMS WHERE EMP_NO='SCOTT' AND DPT_NO='TDC306';

---

15. /*+USE_CONCAT*/

對(duì)查詢中的WHERE后面的OR條件進(jìn)行轉(zhuǎn)換為UNION ALL的組合查詢.
例如:

SELECT /*+USE_CONCAT*/ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';

---

16. /*+NO_EXPAND*/

對(duì)于WHERE后面的OR 或者IN-LIST的查詢語(yǔ)句,NO_EXPAND將阻止其基于優(yōu)化器對(duì)其進(jìn)行擴(kuò)展.
例如:

SELECT /*+NO_EXPAND*/*FROM BSEMPMSWHERE DPT_NO = 'TDC506'AND SEX = 'M';

---

17. /*+NOWRITE*/

禁止對(duì)查詢塊的查詢重寫(xiě)操作.

---

18. /*+REWRITE*/

可以將視圖作為參數(shù).

---

19. /*+MERGE(TABLE)*/

能夠?qū)σ晥D的各個(gè)查詢進(jìn)行相應(yīng)的合并.
例如:

SELECT /*+MERGE(V) */A.EMP_NO, A.EMP_NAM, B.DPT_NOFROM BSEMPMS A(SELET DPT_NO, AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) VWHERE A.DPT_NO = V.DPT_NOAND A.SAL > V.AVG_SAL;

---

20. /*+NO_MERGE(TABLE)*/

對(duì)于有可合并的視圖不再合并.
例如:

SELECT /*+NO_MERGE(V) */A.EMP_NO, A.EMP_NAM, B.DPT_NOFROM BSEMPMS A (SELECT DPT_NO, AVG(SAL) AS AVG_SALFROM BSEMPMS BGROUP BY DPT_NO) VWHERE A.DPT_NO = V.DPT_NOAND A.SAL > V.AVG_SAL;

21. /*+ORDERED*/

根據(jù)表出現(xiàn)在FROM中的順序,ORDERED使ORACLE依此順序?qū)ζ溥B接.

該hint 告訴Oracle 按照From后面的表的順序來(lái)選擇驅(qū)動(dòng)表，Oracle 建議在選擇驅(qū)動(dòng)表上使用Leading，它更靈活一些。

例如:

SELECT /*+ORDERED*/A.COL1, B.COL2, C.COL3FROM TABLE1 A, TABLE2 B, TABLE3 CWHERE A.COL1 = B.COL1AND B.COL1 = C.COL1;

---

22. /*+USE_NL(TABLE)*/

多表連接的三種方式詳解 HASH JOIN MERGE JOIN NESTED LOOP

將指定表與嵌套的連接的行源進(jìn)行連接,并把指定表作為內(nèi)部表.

在多表關(guān)聯(lián)查詢中，指定使用nest loops方式進(jìn)行多表關(guān)聯(lián)。
例如:

SELECT /*+ORDERED USE_NL(BSEMPMS)*/BSDPTMS.DPT_NO, BSEMPMS.EMP_NO, BSEMPMS.EMP_NAMFROM BSEMPMS, BSDPTMSWHERE BSEMPMS.DPT_NO = BSDPTMS.DPT_NO;

23. /*+USE_MERGE(TABLE)*/

多表連接的三種方式詳解 HASH JOIN MERGE JOIN NESTED LOOP

將指定的表與其他行源通過(guò)合并排序連接方式連接起來(lái).

在多表關(guān)聯(lián)查詢中，指定使用merge join方式進(jìn)行多表關(guān)聯(lián)。
例如:

SELECT /*+USE_MERGE(BSEMPMS,BSDPTMS)*/*FROM BSEMPMS, BSDPTMSWHERE BSEMPMS.DPT_NO = BSDPTMS.DPT_NO;

---

24. /*+USE_HASH(TABLE)*/

多表連接的三種方式詳解 HASH JOIN MERGE JOIN NESTED LOOP

將指定的表與其他行源通過(guò)哈希連接方式連接起來(lái).

在多表關(guān)聯(lián)查詢中，指定使用hash join方式進(jìn)行多表關(guān)聯(lián)。
例如:

SELECT /*+USE_HASH(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

---

25. /+DRIVING_SITE(TABLE)/

強(qiáng)制與ORACLE所選擇的位置不同的表進(jìn)行查詢執(zhí)行.
例如:

SELECT /*+DRIVING_SITE(DEPT)*/ * FROM BSEMPMS,DEPT@BSDPTMS WHERE BSEMPMS.DPT_NO=DEPT.DPT_NO;

---

26. /+LEADING(TABLE)/

將指定的表作為連接次序中的首表.

在一個(gè)多表關(guān)聯(lián)的查詢中，該Hint指定由哪個(gè)表作為驅(qū)動(dòng)表，告訴優(yōu)化器首先要訪問(wèn)哪個(gè)表上的數(shù)據(jù)。

select /*+leading(t1,t) */ * from scott.dept t,scott.emp t1 where t.deptno=t1.deptno;

---

27. /+CACHE(TABLE)/

在全表掃描操作中，如果使用這個(gè)提示，Oracle 會(huì)將掃描的到的數(shù)據(jù)塊放到LRU（least recently Used： 最近很少被使用列表，是Oracle 判斷內(nèi)存中數(shù)據(jù)塊活躍程度的一個(gè)算法）列表的最被使用端（數(shù)據(jù)塊最活躍端），這樣數(shù)據(jù)塊就可以更長(zhǎng)時(shí)間地駐留在內(nèi)存當(dāng)中。

如果有一個(gè)經(jīng)常被訪問(wèn)的小表，這個(gè)設(shè)置會(huì)提高查詢的性能；同時(shí)CACHE也是表的一個(gè)屬性，如果設(shè)置了表的cache屬性，它的作用和hint一樣，在一次全表掃描之后，數(shù)據(jù)塊保留在LRU列表的最活躍端。

例如:

SELECT /*+FULL(BSEMPMS) CAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;

---

28. /*+NOCACHE(TABLE)*/

例如:

SELECT /*+FULL(BSEMPMS) NOCAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;

---

29. /*+APPEND*/

直接插入到表的最后,可以提高速度.

提示數(shù)據(jù)庫(kù)以直接加載的方式（direct load）將數(shù)據(jù)加載入庫(kù)。

這個(gè)hint 用的比較多。 尤其在插入大量的數(shù)據(jù)，一般都會(huì)用此hint。

insert /*+append*/ into test1 select * from test4 ;

---

30. /*+NOAPPEND*/

通過(guò)在插入語(yǔ)句生存期內(nèi)停止并行模式來(lái)啟動(dòng)常規(guī)插入.

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;

---

并行執(zhí)行相關(guān)的Hint

parallel

在sql中指定執(zhí)行的并行度，這個(gè)值將會(huì)覆蓋自身的并行度

select /*+ parallel(emp,4)*/ * from emp where deptno=200 and sal>300;

關(guān)于表的并行度，我們?cè)趧?chuàng)建表的時(shí)候可以指定，如：

SQL> CREATE TABLE XGJ2 (3 name VARCHAR2 (10)4 )5 PARALLEL 2;

表已創(chuàng)建。

SQL> select degree from all_tables where table_name = 'XGJ'; -- 查看表的并行度 DEGREE -------------------- 2 SQL> alter table XGJ parallel(degree 3); -- 修改表的并行度 表已更改。 SQL> select degree from all_tables where table_name = 'XGJ'; DEGREE -------------------- 3 SQL> alter table XGJ noparallel; -- 取消表的并行度 表已更改。 SQL> select degree from all_tables where table_name = 'XGJ'; DEGREE -------------------- 1

---

no_parallel

在sql中指定執(zhí)行的不使用并行

select /*+ no_parallel(t) */ count(*) from t;

總結(jié)

以上是生活随笔為你收集整理的Oracle优化06-Hint的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。