互聯網 - 大數據

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://blog.csdn.net/tianlesoftware/archive/2009/12/13/4969702.aspx

在使用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）優化器類型：

系統級別：

   1: SQL>alter system set optimizer_mode=all_rows;

會話級別：

SQL>alter system set optimizer_mode=all_rows;

關于優化器，參考：

Oracle Optimizer CBO RBO

http://blog.csdn.net/tianlesoftware/archive/2010/08/19/5824886.aspx

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://blog.csdn.net/tianlesoftware/archive/2010/08/20/5826546.aspx

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://blog.csdn.net/tianlesoftware/archive/2010/08/20/5826546.aspx

這三種關聯方式是多表關聯中主要使用的關聯方式。 通常來說，當兩個表都比較大時，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://blog.csdn.net/tianlesoftware/archive/2009/10/30/4745144.aspx

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

http://blog.csdn.net/tianlesoftware/archive/2010/08/20/5827245.aspx

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