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');