16. Oracle Hint (2)

• Join相關Hint :

/*+ USE_NL(TABLE) */

使用 Nested-loop 進行表的連結並指定驅動表， USE_NL 經常配合 ORDERED 提示一並使用。例如 :

SQL> select /*+ ORDERED USE_NL(DEP) */ count(*)  from departments dep,employees emp where dep.department_id=emp.department_id;

/*+ USE_MERGE(TABLE) */

將指定的 Table 進行 Merge join 。例如:

SQL> select /*+ USE_MERGE(DEP,EMP) */ count(*)         from departments dep,employees emp where dep.department_id=emp.department_id;

/*+ USE_HASH(TABLE) */

將指定的 Table 進行 Hash join 。例如:

SQL> select /*+ USE_HASH(DEP,EMP) */ count(*)         from departments dep,employees emp where dep.department_id=emp.department_id;

/*+ ORDERED */

強制 SQL 表連接的順序依照 From 子句後面 Table 的順序進行連接，當 CBO 無法將資料較小的 Table 當做驅動表時，可將資料較少的 Table 放置到 From 子句的最前面並利用 ORDERED 提示指定驅動表。例如:

SQL> select /*+ ORDERED */ count(*) from departments dep,employees emp where dep.department_id=emp.department_id;

/*+ LEADING(Table) */

LEADING 提示可以直接指定 from 子句後面的哪個 Table 為驅動表，例如 :

SQL> SELECT /*+ LEADING(e j) */ *  FROM employees e, departments d, job_history j WHERE e.department_id = d.department_id AND e.hire_date = j.start_date;

• 子查詢相關Hint :

當一段 select statement 包含了另一段 select statement ，後面這段 SQL我們就稱為子查詢。子查詢可以是一段 select statement 或是一個 view ，一般來說，對於子查詢進行 join 時，以 view access path 來執行效能較好，而所謂 view access path 指的是子查詢將獨立執行，執行出來的結果再與外部 SQL 做 join 。子查詢相關的 Hint 便是用來控制子查詢的 SQL 是否要展開，不展開表示子查詢的 SQL 將獨立執行，並將其結果與外部 SQL 做 join ；展開表示把子查詢的 Table 拆解出來，直接與外部 SQL 組成多個 Table 互相 join 的執行計畫。

/*+ QB_NAME (Name) */

當我們想控制子查詢的 SQL 是否要展開時，首先必須賦予這段 SQL 一個名稱，這樣外部 SQL 才能夠明確的指出要對哪段子查詢作操作。 QB_NAME 這個 Hint 就是用來賦予一段 select statement 一個別名，例如:

SQL> select /*+ FULL(@qb e) */ * from   (select /*+ QB_NAME(qb) */ * from employees e  where department_id=20);

當對一段 SQL 定義好 QB_NAME 之後，其他的 Hint 必須加上 "@" 符號來使用，上述查詢就是透過外部 SQL 提示子查詢必須使用 Full Table Scan :

/*+ MERGE(QB_NAME) */

MERGE 主要針對 from 子句後面的子查詢做操作並使用在主查詢裡，表示展開from 子句後面的子查詢，形成多個 Table 互相 join 的計畫，例如:

SQL> select /*+ merge(b) */ b.*  from departments d,         (select e.* from employees e,jobs j            where e.job_id=j.job_id              and j.max_salary < 9000) b where d.department_id=b.department_id;

使用 merge 之後的執行計畫將會是 employees 、 departments 與 jobs三個 Table 互相 join 的計畫:

/*+ NO_MERGE(QB_NAME) */

NO_MERGE 主要針對 from 子句後面的子查詢做操作並使用在主查詢裡，表示不展開子查詢，子查詢將獨立執行並將其結果與其他 Table 做 join 。例如:

SQL> select /*+ no_merge(b) */ b.*  from departments d,         (select e.* from employees e,jobs j            where e.job_id=j.job_id              and j.max_salary < 9000) b where d.department_id=b.department_id;

使用 no_merge 之後子查詢將會獨立執行，其結果再與外部的 departments做 join ，由執行計畫我們可以看到 view access path :

/*+ UNNEST */

UNNEST 主要針對的是 where 子句後面的子查詢進行操作並使用在子查詢當中，表示展開所在的子查詢，讓子查詢的 Table 與其它 Table 做 join ，例如 :

SQL> select * from employees e,departments d        where job_id in        (select /*+ unnest */ job_id from jobs where max_salary < 9000)          and e.department_id=d.department_id    and e.department_id=50;

透過執行計畫可知子查詢中的 jobs 展開與 employees 與 departments 進行 join :

/*+ NO_UNNEST */

NO_UNNEST 主要是針對 where 條件後面的子查詢進行操作並使用在子查詢裡， NO_UNNEST 代表子查詢將獨立執行，再將結果與其他條件進行 filter 的操作，例如 :

SQL> select e.* from employees e,departments d        where job_id in         (select /*+ no_unnest */ job_id           from jobs where max_salary < 9000)           and e.department_id=d.department_id     and e.department_id=50;

透過執行計畫可知 employees 與 departments 先行 join 之後，再與子查詢產生出的結果進行 filter 把符合的資料挑選出來:

/*+ PUSH_SUBQ(QB_NAME) */

由前述 SQL ，使用 NO_UNNEST 可以讓子查詢獨立執行並產生 filter 的操作，而這個 filter 操作對於效能來說往往是沒有效率的，原因在於 where 子句後的子查詢都是最後才執行的，若子查詢外的條件回傳的結果集太大，整段 SQL 的效能也就不好，這也失去了讓子查詢的條件獨立執行的意義，而 PUSH_SUBQ 就是為了解決 filter 操作導致效能不好的問題。使用 PUSH_SUBQ 可以確保子查詢的結果可以優先被執行，利用產生出的結果集再與其在 Table 進行 join ，如此一來便可減少 Table 之間 join 時所需搜尋資料的範圍，藉以提高效能，所以當子查詢回傳的資料越少時，使用 PUSH_SUBQ 對整體效能來說會越有效率。例如我們將上述 SQL 加入 PUSH_SUBQ :

SQL> select /*+ push_subq(@qb) */ e.* from employees e,departments d        where job_id in         (select /*+ qb_name(qb) no_unnest */ job_id            from jobs where max_salary < 9000)          and e.department_id=d.department_id    and e.department_id=50;

透過執行計畫可以發現 filter 操作被消除了，子查詢將優先查詢出max_salary < 9000 的 job_id ，再利用產生出的結果與 employees 與 departments 做 join ，比對先前的計畫可發現整體的 cost 降低了不少 :

不過值得住意的是，若 join 語法使用 merge join 或是透過 db_link 存取遠端 table ，這兩者將會導致 PUSH_SUBQ 提示無效。

/*+ PUSH_PRED(QB_NAME) */

我們可以說 PUSH_SUBQ 是用來輔助 NO_UNNEST 這個 Hint ，而 PUSH_PRED則是用來輔助 NO_MERGE 這個 Hint 。 PUSH_PRED 只能使用在 NO_MERGE 而且是 outer join 的情況之下，當子查詢位於 from 子句之後且 where 條件使用的是 outer join 時，我們可以使用 PUSH_PRED 強制讓子查詢的結果集與其他 Table 以 Nest Loop 的方式做 join ，例如一段 SQL 具有子查詢且使用 outer join 如下 :

SQL> select /*+ no_merge(b) */ b.*  from departments d,         (select e.* from employees e,jobs j           where e.job_id=j.job_id             and j.max_salary < 9000) b where d.department_id=b.department_id(+);

在本例中優化器使用的是 Hash Join :

現在將 SQL 添加 PUSH_PRED 這個 Hint 如下 :

SQL> select /*+ no_merge(b) push_pred(b) */ b.*  from departments d,         (select e.* from employees e,jobs j            where e.job_id=j.job_id              and j.max_salary < 9000) b where d.department_id=b.department_id(+);

添加 PUSH_PRED 之後可以看出執行計畫由 Hash join 變為 Nest Loop 了:

而 PUSH_PRED 只是在 outer join 的情況下用來改變 join 方式的一種手段，並不代表使用之後對效能一定有正面的幫助。

• 其它Hint :

/*+ APPEND */

APPEND 提示使用在 Insert…select… 的語法，原理為略過 buffer cache直接對 Disk 進行 direct load ，並使用 High Water Mark 以上的 Block 進行 Insert 的動作，因此使用 APPEND 提示對 INSERT 的效能較好，但缺點會對 Source Table 進行 Table Lock 的動作，這是在使用 APPEND 提示需注意的地方。例如:

SQL> Insert /*+ append */ into emp select * from employees;

/*+ PARALLEL(Table Degree) */

PARALLEL 提示可以使 select statement 或是 DML statement 進行平行處理， Degree 是用來設定需透過幾個程序 (thread) 來作平行處理，這個値可以是整數或是設定為 default ，若設定為 default 則處理程序 (thread) 的數量將依參數 parallel_threads_per_cpu 與 cpu_count 來決定。在此值得住意的是， PARALLEL 必須使用在 Full Table Scan 的狀態下，否則這個 Hint 將失效，例如:

SQL> select * from employees e where department_id=20;

原 SQL 的執行計畫使用的是 Index Range Scan :

加上 PARALLEL 改寫如下 :

SQL> select /*+ parallel(e 2) */ * from employees e        where department_id=20;

由於使用的不是 Full Table Scan ，因此 PARALLEL 不會生效 :

再修正 FULL Hint 強制使用 Full Table Scan 改寫如下 :

SQL> select /*+ full(e) parallel(e 2) */ * from employees e        where department_id=20;

使用 FULL 之後，執行計畫便以 PARALLEL 方式來執行了 :

/*+ RESULT_CACHE */ (Oracle 11g later)

RESULT CACHE 為 Oracle 11g 的新特性，以往我們在執行一段 SQL 時，系統會將其進行 Soft Parse 或是 Hard Parse ， SQL 經由解析過的執行計畫來執行時，便會到 Buffer Cache 或是直接於 Disk 讀取所需的資料， Oracle 11g 提供了 result cache 功能，可以將 SQL 執行過後的結果直接快取起來，等到下次同樣的 SQL 再執行時，系統便無需真的再次執行 SQL 指令，而是從 result cache 將結果集直接返回數據，如此一來便可大大縮短 SQL 的回應時間而提高效能，當參數 result_cache_mode 設定為 MANUAL 時，我們便可透過 RESULT_CACHE 這個 Hint 來針對所需的 SQL 結果集作 cache 的動作，例如:

SQL> select /*+ result_cache */ count(*) from scott.objects;

為 SQL 添加了 RESULT_CACHE 提示，由於第一次執行，因此產生了 1029 的consistent gets :

同樣的 SQL 我們再執行一次，由於已經存在了 result cache ，無需再由 Buffer Cache 或是 Disk 讀取任何 block ， consistent gets 為 0 :

/*+ MONITOR */ (Oracle 11g later)

Oracle 11g 開始提供了可以動態監督 SQL 語句的功能，這個功能可以透過 EM Console 中的  "SQL監督" 或是查詢 V$SQL_MONITOR 來使用，而預設這個功能只會監督 long running SQL ，一般消費 cpu 或 I/O 時間小於 5 秒的 SQL 不在監督的範圍內，而 MONITOR 這個 Hint 可以確保執行的 SQL 是被系統監督的，例如 :

SQL> select /*+ MONITOR */ * from employees;

/*+ NO_MONITOR */ (Oracle 11g later)

相對於 MONITOR ， NO_MONITOR 可以確保執行的 SQL 不被系統監督，即便是 long running SQL 也不納入監督範圍，藉以減輕 long running SQL 所消費額外的系統資源，例如 :

SQL> select /*+ NO_MONITOR */ * from (select * from dba_dependencies) start with referenced_owner not in ('SYS','PUBLIC') connect by nocycle prior referenced_name=name and referenced_owner not in ('SYS','PUBLIC');

15. Oracle Hint (1)

當 CBO 選擇的執行計畫無法符合我們的期望時，可使用 Hint 來強制指定SQL 的執行計畫， Hint 的使用方式為在 select 、 insert 、 update 語句之後加上 /*+ <Hint_name> */ ，需要注意的是， <Hint_name> 與 /*+  */ 之間最好有空白，避免 Hint 在程式語法裡失效卻無法知道原因為何， Hint 依照種類可以區分幾項 : Optimizer 相關 Hint 、 SQL Access Path 相關 Hint 、 Join 相關 Hint 、 sub-query 相關 Hint ，以及其它種類。

• Optimizer相關Hint :

/*+ RULE */

使用 /*+ RULE */ 表示對當前 SQL 語句使用基於規則的優化器進行解析， RULE 提示在 Tuning 過程中十分有用，當我們無法確定 CBO 所選擇的執行計畫是否正確時，會以 RULE 提示所產生的執行計畫為基礎，然後再對 SQL 的執行計畫進行調整， RULE 提示便是彌補以 CBO 為主的優化模式下的不足。例如:

SQL> select /*+ RULE */ * from employees where department_id=50;

/*+ ALL_ROWS */

使用 /*+ ALL_ROWS */ 表示當前 SQL 使用 CBO 優化器進行解析，並且選擇能夠快速返回所有 rows 的最佳執行計畫，例如:

SQL> SELECT /*+ ALL_ROWS */ employee_id, last_name, salary, job_id        FROM employees WHERE employee_id = 7566;

/*+ FIRST_ROWS(n) */

使用 /*+ FIRST_ROWS(n) */ 表示當前 SQL 使用 CBO 優化器進行解析，並且選擇能夠最快返回前面 n 筆資料的最佳執行計畫，例如:

SQL> SELECT /*+ FIRST_ROWS(10) */ employee_id, last_name, salary, job_id FROM employees WHERE department_id = 20;

• SQL Access Path相關Hint :

/*+ FULL(TABLE) */

FULL 提示表示強制對 Table 進行全表掃描，先前我們曾提到過對於某些小表來說，使用全表掃描有時會比使用 Index 來得有效率。例如:

SQL> select /*+ FULL(EMP) */ * from employees emp  where department_id=50;

/*+ CACHE(TABLE) */

CACHE 提示確保 Table 能存放於 data buffer ，通常針對小 Table 並且常與 FULL 提示一起使用，例如:

SQL> select /*+ FULL(REG) CACHE(REG) */ count(*)  from regions REG;

/*+ NOCACHE(TABLE) */

NOCACHE 提示相對於 CACHE ，代表 select 過的 table 不要 cache 在buffer 裡，例如:

SQL> SELECT /*+ FULL(hr_emp) NOCACHE(hr_emp) */ last_name FROM employees hr_emp;

/*+ INDEX(TABLE INDEX_NAME) */

INDEX 提示可以強制使用 Table 中的某一個 Index ，使用方式為 Index 提示後面加上 Table_Name 與 Table 所附屬的 Index_Name ，在此需注意的是，若 SQL 語法有定義 Table 的別名，則 Index 提示後面也必須使用別名，否則 Hint 將無效。例如:

SQL> select /*+ INDEX(EMP EMP_EMP_ID_PK) */ * from employees emp;

/*+ NO_INDEX(TABLE INDEX_NAME) */

NO_INDEX 代表指定不使用 Table 中的某個 Index 。例如:

SQL> SELECT /*+ NO_INDEX(EMP emp_empid) */ employee_id FROM employees EMP WHERE employee_id > 200;

/*+ INDEX_ASC(TABLE INDEX_NAME) */

任何 Index 在建立的同時，我們可以指定 Index 上存放的資料是以遞增(ASC) 排序或是遞減 (DESC) 排序，若 Index 建立是以遞增 (ASC) 排序，則使用 Index 掃描的順序預設為 ASC ，反之若 Index 是以 DESC 建立，則預設以 DESC 進行掃描， INDEX_ASC 提示代表指定對 Index 進行遞增 (ASC) 方式的掃描，例如:

SQL> SELECT /*+ INDEX_ASC(e emp_name_ix) */ * FROM employees e;

/*+ INDEX_DESC(TABLE INDEX_NAME) */

INDEX_DESC 相對於 INDEX_ASC ，代表指定對 Index 進行遞減 (DESC) 方式掃描，例如:

SQL> SELECT /*+ INDEX_DESC(e emp_name_ix) */ * FROM employees e;

/*+ INDEX_FFS(TABLE INDEX_NAME) */

INDEX_FFS 代表使用 Index fast full scan 進行掃描，若查詢的 rows 占總資料量的多數，則使用 full table scan 將比使用 Index 來的有效率，在此若 select 的欄位上已具有 Index ，則使用 Index fast full scan 將比 full table scan 來得有效率，例如:

SQL> SELECT /*+ INDEX_FFS(e emp_name_ix) */ first_name FROM employees e;

/*+ NO_INDEX_FFS(TABLE INDEX_NAME) */

NO_INDEX_FFS 代表使用 Index 進行掃描時，不考慮使用 Index fast full scan 這個方式進行掃描，例如:

SQL> SELECT /*+ NO_INDEX_FFS(e emp_name_ix) */ first_name FROM employees e;

/*+ INDEX_COMBINE(TABLE INDEX_NAME…INDEX_NAME) */

若 where 條件上的每個欄位均具有 Index ，在 Rule Based 下會以 And-Equal 的方式同時使用所有 Index ，使用的順序會依照 where 條件的順序來決定，但在 Cost Based 下只會使用選擇性較高的 Index 。而 INDEX_COMBINE 這個 HINT 則是實現了即使是 Cost Based 也能同時使用包含在 where 條件中的所有 Index 。 INDEX_COMBINE 的原理是使用 BITMAP 方式掃描數據，通過 BITMAP CONVERSION FROM ROWIDS 這個步驟將所有 B-Tree 索引中獲得的 rowid 信息組合 成BITMAP ，藉此縮小資料查詢的範圍，然後再經由 BITMAP CONVERSION TO ROWIDS 這個步驟轉換出 rowid 來獲得所需的數據。例如一段SQL如下:

SQL> select employee_id,department_id,first_name,salary from employees where department_id=30 and manager_id=114;

此段 SQL 包含兩個 where 條件，一個為 department_id=30 ，另一個為manager_id=114 ，而 employees 這個 table 於這兩個欄位上分別都建有 Index， EMP_DEPARTMENT_IX 與 EMP_MANAGER_IX ，在未使用 INDEX_COMBINE 提示之前 CBO 將使用選擇性較高的索引:

其挑選資料的順序是先以 rowid 找出所有 manager_id=114 的資料之後，再由這些資料過濾出 department_id=30 的資料。

接下來使用 INDEX_COMBINE 提示之後，兩個索引便會同時納入 CBO 的執行計畫當中 :

透過 INDEX_COMBINE ， CBO 先從兩個 B-Tree Index 中找出各自條件的rowid，經由比對之後留下同時符合 manager_id=114 與 department_id=30 兩個條件的 rowid ，最終再利用這組 rowid 來獲取所需的數據。

/*+ INDEX_SS(TABLE INDEX_NAME) */

INDEX_SS 代表使用 Index skip scan ，當複合索引時，若 where條件的欄位不包含複合索引的第一個欄位，則優化器不會使用索引，從 Oracle 9i 開始，可以使用 Index skip scan 來跳過複合索引的前綴欄位 (prefix column) 來使用索引，例如 EMP_NAME_IX 這個複合索引的欄位順序為 LAST_NAME、FIRST_NAME :

若一段 SQL 如下， where 條件中只包含 first_name :

SQL> select employee_id,first_name,salary  from employees where first_name='Daivid';

由於 where 條件不包含複合索引的第一個欄位，所以優化器不會使用索引 :

透過 INDEX_SS 提示使用 Index skip scan ，此時優化器便可使用索引 :

/*+ USE_CONCAT */

若於索引的欄位上使用 or 條件，優化器不會選擇使用索引，建議使用 UNION 替代 or ，如此一來優化器便可使用索引。若遇到無法修改程式語法的情況，便可使用 USE_CONCAT 來提示優化器以 UNION 方式來執行 or 語法，如此一來便可使用索引，例如一段使用 or 語法的 SQL 如下:

SQL> select * from employees  where department_id = 20 or  manager_id = 140;

由於使用 or ，因此優化器不會使用索引 :

使用了 USE_CONCAT 之後，優化器以 UNION 方式執行，此時便可使用到索引 :

/*+ DRIVING_SITE(Table) */

當 SQL 的查詢透過 DBLINK 與遠端的 Table 進行 JOIN 時，先對 Remote的 Table 進行 Full Table Scan 將所有的 row set 回傳到本地端再進行 JOIN 與篩選的動作，若 Remote 的 Table 資料量非常大時，此時 SQL 的效能將越差，而 DRIVING_SITE 提示可將資料於 Remote 端先篩選過後再傳回本地端進行 JOIN ，使用 DRIVING_SITE 可以減少傳輸的資料量，進而提升效能。例如:

SQL> select /*+ DRIVING_SITE(EMP) */ count(*)   from departments dep,employees@USA emp where dep.department_id=emp.department_id and emp.salary > 35000;