Oracle Hint 詳解

Hint 是Oracle 提供的一種SQL語法，它允許使用者在SQL語句中插入相關的語法，從而影響SQL的執行方式。

因為Hint的特殊作用，所以對於開發人員不應該在程式碼中使用它，Hint 更像是Oracle提供給DBA用來分析問題的工具 。在SQL程式碼中使用Hint，可能導致非常嚴重的後果，因為資料庫的資料是變化的，在某一時刻使用這個執行計劃是最優的，在另一個時刻，卻可能很差，這也是CBO 取代RBO的原因之一，規則是死的，而資料是時刻變化的，為了獲得最正確的執行計劃，只有知道表中資料的實際情況，通過計算各種執行計劃的成本，則其最優，才是最科學的，這也是CBO的工作機制。 在SQL程式碼中加入Hint，特別是效能相關的Hint是很危險的做法。

Hints

Hints are comments in a SQL statement that pass instructions to the Oracle Database optimizer. The optimizer uses these hints to choose an execution plan for the statement, unless some condition exists that prevents the optimizer from doing so.

Hints were introduced in Oracle7, when users had little recourse if the optimizer generated suboptimal plans. Now Oracle provides a number of tools, including the SQL Tuning Advisor, SQL plan management, and SQL Performance Analyzer, to help you address performance problems that are not solved by the optimizer. Oracle strongly recommends that you use those tools rather than hints. The tools are far superior to hints, because when used on an ongoing basis, they provide fresh solutions as your data and database environment change.

Hints should be used sparingly, and only after you have collected statistics on the relevant tables and evaluated the optimizer plan without hints using the EXPLAIN PLAN statement. Changing database conditions as well as query performance enhancements in subsequent releases can have significant impact on how hints in your code affect performance.

The remainder of this section provides information on some commonly used hints. If you decide to use hints rather than the more advanced tuning tools, be aware that any short-term benefit resulting from the use of hints may not continue to result in improved performance over the long term.

Oracle 聯機文件對Hint的說明：

http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/sql_elements006.htm#SQLRF50705

之前整理的一篇文章：

常見Oracle HINT的用法

http://space.itpub.net/28673746/viewspace-757514

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

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

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

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

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

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

根據hint的功能，可以分成如下幾類：

一． 和優化器相關的Hint

Oracle 允許在系統級別，會話級別和SQL中（hint）優化器型別：

系統級別：

SQL>alter system set optimizer_mode=all_rows;

會話級別：

SQL>alter system set optimizer_mode=all_rows;

關於優化器，參考：

Oracle Optimizer CBO RBO

http://space.itpub.net/28673746/viewspace-757150

1.1 ALL_ROWS 和FIRST_ROWS（n） -- CBO 模式

對於OLAP系統，這種系統中通常都是執行一些大的查詢操作，如統計，報表等任務。 這時優化器模式應該選擇ALL_ROWS. 對於一些分頁顯示的業務，就應該用FIRST_ROWS（n）。 如果是一個系統上執行這兩種業務，那麼就需要在SQL 用hint指定優化器模式。

如：

SQL> select /* + all_rows*/ * from dave;

SQL> select /* + first_rows(20)*/ * from dave;

1.2 RULE Hint -- RBO 模式

儘管Oracle 10g已經棄用了RBO，但是仍然保留了這個hint。 它允許在CBO 模式下使用RBO 對SQL 進行解析。

如：

SQL> show parameter optimizer_mode

NAME TYPE VALUE

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

optimizer_mode string ALL_ROWS

SQL> set autot trace exp;

SQL> select /*+rule */ * from dave;

執行計劃

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

Plan hash value: 3458767806

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

| Id | Operation | Name |

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

| 0 | SELECT STATEMENT | |

| 1 | TABLE ACCESS FULL| DAVE |

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

Note

-----

- rule based optimizer used (consider using cbo) -- 這裡提示使用RBO

SQL>

二． 訪問路徑相關的Hint

這一部分hint 將直接影響SQL 的執行計劃，所以在使用時需要特別小心。 該類Hint對DBA分析SQL效能非常有幫助，DBA 可以讓SQL使用不同的Hint得到不同的執行計劃，通過比較不同的執行計劃來分析當前SQL效能。

2.1 FULL Hint

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

示例：

SQL> select /*+full(dave) */ * from dave;

要注意，如果表有別名，在hint裡也要用別名， 這點在前面已經說明。

2.2 INDEX Hint

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

示例：

SQL> select /*+index(dave index_dave) */ * from dave where id>1;

謂詞裡有索引欄位，才會用索引。

2.3 NO_INDEX Hint

No_index hint 告訴優化器對指定的表不允許使用索引。

示例：

SQL> select /*+no_index(dave index_dave) */ * from dave where id>1;

2.4 INDEX_DESC Hint

該Hint 告訴優化器對指定的索引使用降序方式訪問資料，當使用這個方式會導致結果集不完整時，優化器將忽略這個索引。

示例：

SQL> select /*+index_desc(dave index_dave) */ * from dave where id>1;

2.5 INDEX_COMBINE Hint

該Hint告訴優化器強制選擇點陣圖索引，當使用這個方式會導致結果集不完整時，優化器將忽略這個Hint。

示例：

SQL> select /*+ index_combine(dave index_bm) */ * from dave;

2.6 INDEX_FFS Hint

該hint告訴優化器以INDEX_FFS(INDEX Fast Full Scan)的方式訪問資料。當使用這個方式會導致結果集不完整時，優化器將忽略這個Hint。

示例：

SQL> select /*+ index_ffs(dave index_dave) */ id from dave where id>0;

2.7 INDEX_JOIN Hint

索引關聯，當謂詞中引用的列上都有索引時，可以通過索引關聯的方式來訪問資料。

示例：

SQL> select /*+ index_join(dave index_dave index_bm) */ * from dave where id>0 and name='安徽安慶';

2.8 INDEX_SS Hint

該Hint強制使用index skip scan 的方式訪問索引，從Oracle 9i開始引入這種索引訪問方式，當在一個聯合索引中，某些謂詞條件並不在聯合索引的第一列時（或者謂詞並不在聯合索引的第一列時），可以通過index skip scan 來訪問索引獲得資料。 當聯合索引第一列的唯一值很小時，使用這種方式比全表掃描效率要高。當使用這個方式會導致結果集不完整時，優化器將忽略這個Hint。

示例：

SQL> select /*+ index_ss(dave index_union) */ * from dave where id>0;

三． 表關聯順序的Hint

表之間的連線方式有三種。 具體參考blog：

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

http://space.itpub.net/28673746/viewspace-757516

3.1 LEADING hint

在一個多表關聯的查詢中，該Hint指定由哪個表作為驅動表，告訴優化器首先要訪問哪個表上的資料。

示例：

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

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

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti

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

| 0 | SELECT STATEMENT | | 14 | 812 | 6 (17)| 00

| 1 | MERGE JOIN | | 14 | 812 | 6 (17)| 00

| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00

| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00

|* 4 | SORT JOIN | | 14 | 532 | 4 (25)| 00

| 5 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00

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

3.2 ORDERED Hint

該hint 告訴Oracle 按照From後面的表的順序來選擇驅動表，Oracle 建議在選擇驅動表上使用Leading，它更靈活一些。

SQL> select /*+ordered */ * from scott.dept t,scott.emp t1 where t.deptno=t1.deptno;

四． 表關聯操作的Hint

4.1 USE_HASH,USE_NL,USE_MERGE hint

表之間的連線方式有三種。 具體參考blog：

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

http://space.itpub.net/28673746/viewspace-757516

這三種關聯方式是多表關聯中主要使用的關聯方式。 通常來說，當兩個表都比較大時，Hash Join的效率要高於巢狀迴圈（nested loops）的關聯方式。

Hash join的工作方式是將一個表（通常是小一點的那個表）做hash運算，將列資料儲存到hash列表中，從另一個表中抽取記錄，做hash運算，到hash 列表中找到相應的值，做匹配。

Nested loops 工作方式是從一張表中讀取資料，訪問另一張表（通常是索引）來做匹配，nested loops適用的場合是當一個關聯表比較小的時候，效率會更高。

Merge Join 是先將關聯表的關聯列各自做排序，然後從各自的排序表中抽取資料，到另一個排序表中做匹配，因為merge join需要做更多的排序，所以消耗的資源更多。 通常來講，能夠使用merge join的地方，hash join都可以發揮更好的效能。

USE_HASH,USE_NL,USE_MERGE 這三種hint 就是告訴優化器使用哪種關聯方式。

示例如下：

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

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

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

4.2 NO_USE_HASH,NO_USE_NL,NO_USE_MERGE HINT

分別禁用對應的關聯方式。

示例：

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

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

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

五． 並行執行相關的Hint

5.1 PARALLEL HINT

指定SQL 執行的並行度，這個值會覆蓋表自身設定的並行度，如果這個值為default，CBO使用系統引數值。

示例：

SQL> select /*+parallel(t 4) */ * from scott.dept t;

關於表的並行度，我們在建立表的時候可以指定，如：

SQL> CREATE TABLE Anqing

2 (

3 name VARCHAR2 (10)

4 )

5 PARALLEL 2;

表已建立。

SQL> select degree from all_tables where table_name = 'ANQING'; -- 檢視錶的並行度

DEGREE

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

2

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

表已更改。

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

DEGREE

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

3

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

表已更改。

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

DEGREE

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

1

5.2 NO_PARALLEL HINT

在SQL中禁止使用並行。

示例：

SQL> select /*+ no_parallel(t) */ * from scott.dept t;

六． 其他方面的一些Hint

6.1 APPEND HINT

提示資料庫以直接載入的方式（direct load）將資料載入入庫。

示例：

Insert /*+append */ into t as select * from all_objects;

這個hint 用的比較多。 尤其在插入大量的資料，一般都會用此hint。

Oracle 插入大量資料

http://space.itpub.net/28673746/viewspace-757515

6.2 DYNAMIC_SAMPLING HINT

提示SQL 執行時動態取樣的級別。 這個級別從0-10，它將覆蓋系統預設的動態取樣級別。

示例：

SQL> select /*+ dynamic_sampling(t 2) */ * from scott.emp t where t.empno>0;

6.3 DRIVING_SITE HINT

這個提示在分散式資料庫操作中比較有用，比如我們需要關聯本地的一張表和遠端的表：

Select /* + driving_site(departmetns) */ * from employees,departments@dblink where

employees .department_id = departments.department_id;

如果沒有這個提示，Oracle 會在遠端機器上執行departments 表查詢，將結果送回本地，再和employees表關聯。 如果使用driving_site(departments), Oracle將查詢本地表employees，將結果送到遠端，在遠端將資料庫上的表與departments關聯，然後將查詢的結果返回本地。

如果departments查詢結果很大，或者employees查詢結果很小，並且兩張表關聯之後的結果集很小，那麼就可以考慮把本地的結果集傳送到遠端。 在遠端執行完後，在將較小的最終結果返回本地。

6.4 CACHE HINT

在全表掃描操作中，如果使用這個提示，Oracle 會將掃描的到的資料塊放到LRU（least recently Used： 最近很少被使用列表，是Oracle 判斷記憶體中資料塊活躍程度的一個演算法）列表的最被使用端（資料塊最活躍端），這樣資料塊就可以更長時間地駐留在記憶體當中。 如果有一個經常被訪問的小表，這個設定會提高查詢的效能；同時CACHE也是表的一個屬性，如果設定了表的cache屬性，它的作用和hint一樣，在一次全表掃描之後，資料塊保留在LRU列表的最活躍端。

示例：

SQL> select /*+full(t) cache (t) */ * from scott.emp;

小結：

對於DBA來講，掌握一些Hint操作，在實際效能優化中有很大的好處，比如我們發現一條SQL的執行效率很低，首先我們應當檢視當前SQL的執行計劃，然後通過hint的方式來改變SQL的執行計劃，比較這兩條SQL 的效率，作出哪種執行計劃更優，如果當前執行計劃不是最優的，那麼就需要考慮為什麼CBO 選擇了錯誤的執行計劃。當CBO 選擇錯誤的執行計劃，我們需要考慮表的分析是否是最新的，是否對相關的列做了直方圖，是否對分割槽表做了全域性或者分割槽分析等因素。

 

總之，在處理問題時，我們要把問題掌握在可控的範圍內，不能將問題擴大化，甚至失控。 作為一個DBA，需要的紮實的基本功，還有膽大心細，遇事不慌。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28673746/viewspace-757517/，如需轉載，請註明出處，否則將追究法律責任。