使用Hint時的注意事項

 

在使用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	Hint 語法
最佳化器模式提示	ALL_ROWS Hint
	FIRST_ROWS Hint
	RULE Hint

訪問路徑提示	CLUSTER Hint
	FULL Hint
	HASH Hint
	INDEX Hint
	NO_INDEX Hint
	INDEX_ASC Hint
	INDEX_DESC Hint
	INDEX_COMBINE Hint
	INDEX_FFS Hint
	INDEX_SS Hint
	INDEX_SS_ASC Hint
	INDEX_SS_DESC Hint
	NO_INDEX_FFS Hint
	NO_INDEX_SS Hint
	ORDERED Hint
	LEADING Hint
	USE_HASH Hint
	NO_USE_HASH Hint
表連線順序提示	USE_MERGE Hint
	NO_USE_MERGE Hint
	USE_NL Hint
	USE_NL_WITH_INDEX Hint
	NO_USE_NL Hint
表關聯方式提示	PARALLEL Hint
	NO_PARALLEL Hint
	PARALLEL_INDEX Hint
	NO_PARALLEL_INDEX Hint
	PQ_DISTRIBUTE Hint
並行執行提示	FACT Hint
	NO_FACT Hint
	MERGE Hint
	NO_MERGE Hint
	NO_EXPAND Hint
	USE_CONCAT Hint
查詢轉換提示	REWRITE Hint
	NO_REWRITE Hint
	UNNEST Hint
	NO_UNNEST Hint
	STAR_TRANSFORMATION Hint
	NO_STAR_TRANSFORMATION Hint
	NO_QUERY_TRANSFORMATION Hint
	APPEND Hint
	NOAPPEND Hint
	CACHE Hint
	NOCACHE Hint
	CURSOR_SHARING_EXACT Hint
其他Hint	DRIVING_SITE Hint
	DYNAMIC_SAMPLING Hint
	PUSH_PRED Hint
	NO_PUSH_PRED Hint
	PUSH_SUBQ Hint
	NO_PUSH_SUBQ Hint
	PX_JOIN_FILTER Hint
	NO_PX_JOIN_FILTER Hint
	NO_XML_QUERY_REWRITE Hint
	QB_NAME Hint
	MODEL_MIN_ANALYSIS Hint

一． 和最佳化器相關的Hint

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

系統級別：

1: SQL>alter system set optimizer_mode=all_rows;

會話級別：

SQL>alter system set optimizer_mode=all_rows;

關於最佳化器，參考：

Oracle Optimizer CBO RBO

 

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

 

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

 

這三種關聯方式是多表關聯中主要使用的關聯方式。 通常來說，當兩個表都比較大時，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 插入大量資料

 

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 選擇錯誤的執行計劃，我們需要考慮表的分析是否是最新的，是否對相關的列做了直方圖，是否對分割槽表做了全域性或者分割槽分析等因素。

關於執行計劃參考：

Oracle Explain Plan

 

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