Oracle中Hint隨記

tthero00boo發表於2013-07-08
Oracle
Oracle中Hint隨記

1. 優化器:
Oracle的優化器有兩種優化方式,即基於規則的優化方式(Rule-Based Optimization，簡稱為RBO)和基於代價的優化方式(Cost-Based Optimization，簡稱為CBO)，在Oracle8及以後的版本,Oracle強列推薦用CBO的方式
RBO方式：優化器在分析SQL語句時,所遵循的是Oracle內部預定的一些規則。比如我們常見的，當一個where子句中的一列有索引時去走索引。
CBO方式：它是看語句的代價(Cost),這裡的代價主要指Cpu和記憶體。優化器在判斷是否用這種方式時,主要參照的是表及索引的統計資訊。統計資訊給出表的大小、有少行、每行的長度等資訊。這些統計資訊起初在庫內是沒有的，是做analyze後才出現的，很多的時候過期統計資訊會令優化器做出一個錯誤的執行計劃,因些應及時更新這些資訊。
Examda提示：主索引不一定就是優的，比如一個表只有兩行資料，一次IO就可以完成全表的檢索,而此時走索引時則需要兩次IO,這時全表掃描(full table scan)是最好
優化模式包括Rule、Choose、First rows、All rows四種方式：
Rule：基於規則的方式。
Choose：預設的情況下Oracle用的便是這種方式。指的是當一個表或或索引有統計資訊，則走CBO的方式，如果表或索引沒統計資訊，表又不是特別的小，而且相應的列有索引時，那麼就走索引，走RBO的方式。
First Rows：它與Choose方式是類似的，所不同的是當一個表有統計資訊時，它將是以最快的方式返回查詢的最先的幾行，從總體上減少了響應時間。
All Rows:也就是我們所說的Cost的方式，當一個表有統計資訊時，它將以最快的方式返回表的所有的行，從總體上提高查詢的吞吐量。沒有統計資訊則走RBO的方式。
設定選用哪種優化模式：
A、Instance級別我們可以通過在initSID.ora檔案中設定OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS如果沒設定OPTIMIZER_MODE引數則預設用的是Choose方式。
B、Sessions級別通過ALTER SESSION SET OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS來設定。
C、語句級別用Hint（/*+ ... */）來設定
為什麼表的某個欄位明明有索引，但執行計劃卻不走索引？
1、優化模式是all_rows的方式
2、表作過analyze，有統計資訊(最可能的就是統計資訊有誤)
3、表很小，上文提到過的，Oracle的優化器認為不值得走索引。

2. Hint
人為的干預優化器,按照我們的告訴它的方式生成執行計劃。我們可以用Oracle Hints來實現
1) 使用的優化器的型別
2) 基於代價的優化器的優化目標，是all_rows還是first_rows。
3) 表的訪問路徑，是全表掃描，還是索引掃描，還是直接利用rowid。
4) 表之間的連線型別
5) 表之間的連線順序
6) 語句的並行程度
除了”RULE”提示外，一旦使用的別的提示，語句就會自動的改為使用CBO優化器，此時如果你的資料字典中沒有統計資料，就會使用預設的統計資料。所以建議大家如果使用CBO或Hints提示，則最好對錶和索引進行定期的分析。
如何使用Hints:
Hints只應用在它們所在sql語句塊(statement block，由select、update、delete關鍵字標識)上，對其它SQL語句或語句的其它部分沒有影響。如：對於使用union操作的2個sql語句，如果只在一個sql語句上有Hints，則該Hints不會影響另一個sql語句。
我們可以使用註釋(comment)來為一個語句新增Hints，一個語句塊只能有一個註釋，而且註釋只能放在SELECT, UPDATE, or DELETE關鍵字的後面
使用Oracle Hints的語法：
{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */
or
{DELETE|INSERT|SELECT|UPDATE} --+ hint [text] [hint[text]]...
註解：
1) DELETE、INSERT、SELECT和UPDATE是標識一個語句塊開始的關鍵字，包含提示的註釋只能出現在這些關鍵字的後面，否則提示無效。
2) “+”號表示該註釋是一個Hints，該加號必須立即跟在”/*”的後面，中間不能有空格。
3) hint是下面介紹的具體提示之一，如果包含多個提示，則每個提示之間需要用一個或多個空格隔開。
4) text 是其它說明hint的註釋性文字
5)使用表別名。如果在查詢中指定了表別名，那麼提示必須也使用表別名。例如：select /*+ index(e,dept_idx) */ * from emp e;
6)不要在提示中使用模式名稱：如果在提示中指定了模式的所有者，那麼提示將被忽略。例如：
select /*+ index(scott.emp,dept_idx) */ * from emp
注意：如果你沒有正確的指定Hints，Oracle將忽略該Hints，並且不會給出任何錯誤。

轉自：http://czmmiao.iteye.com/blog/1478465

3.oracle hint中ordered 和leading
hash_join可以通過no_swap_join_inputs/swap_join_inputs來強制控制build表，配合leading或者ordered可以控制多表之前的連線順序。

比如t1,t2,t3,t4共4張表做hash_join
可以通過ordered+no_swap_join_inputs/swap_join_inputs來實現。
比如
如果想實現

( T3 hash-join (T1 hash-join T2)) hash-join T4

t1作為build表和T2做hash_join，然後t3作為build表和t1,t2的結果集作hash_join,在把t3,t1,t2的結果集作build表和t4做hash_join
通過sql可以寫為

SQL >select
  2  /*+
  3  ordered
  4  use_hash(t2)
  5  use_hash(t3)
  6  swap_join_inputs(t3)
  7  use_hash(t4)
  8  no_swap_join_inputs(t4)
  9  */
10  * from t1,t2,t3,t4
11  where t1.object_id=t2.object_id
12  and t2.object_name=t3.object_name
13  and t3.owner=t4.owner
14  and t4.owner='MYDB'
15  /
已用時間:  00: 00: 00.07

執行計劃
----------------------------------------------------------
Plan hash value: 3494725078

-------------------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |  2137 |   801K|       |   182   (2)| 00:00:03 |
|*  1 |  HASH JOIN           |      |  2137 |   801K|       |   182   (2)| 00:00:03 |
|*  2 |   HASH JOIN          |      |    52 | 14976 |       |   167   (2)| 00:00:03 |
|*  3 |    TABLE ACCESS FULL | T3   |    40 |  3840 |       |    15   (0)| 00:00:01 |
|*  4 |    HASH JOIN         |      | 11651 |  2184K|  1232K|   151   (1)| 00:00:02 |
|   5 |     TABLE ACCESS FULL| T1   | 11651 |  1092K|       |    15   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL| T2   | 11652 |  1092K|       |    15   (0)| 00:00:01 |
|*  7 |   TABLE ACCESS FULL  | T4   |    41 |  3936 |       |    15   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T3"."OWNER"="T4"."OWNER")
   2 - access("T2"."OBJECT_NAME"="T3"."OBJECT_NAME")
   3 - filter("T3"."OWNER"='MYDB')
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   7 - filter("T4"."OWNER"='MYDB')

Note
-----
   - dynamic sampling used for this statement
說明：
ordered表示依據from後面寫的表的順序來做聯結~
寫hints，分開些思路清晰~ ordered後 from t1 ,t2 ,t3 ,t4說明首先使用t1做驅動表來連線t2,如何連線呢？看後面的hint use_hash(t2)
代表連線t2的方式是hash_join;然後用use_hash(t3)表示連線t3的方式是hash-join，那麼誰作build表呢？看後面的swap_join_inputs(t3)代表t3作build表和t1-t2的結果集作連線....依此類推~

ordered 是陳舊的hints,leading是用來代替ordered的~ leading不要求sql的寫法(from後面的順序不要求)，直接可以在leading中定義連線順序~
leading和ordered不能一起使用，也沒必要一起使用~

針對leading使用：
10g中對leading做了加強~ 可以直接在後面寫多表的連線順序了，也就是說使用leading不需要from後面的固定順序了
sql >select
  2     /*+
  3     leading(t1 t2 t3 t4)
  4     use_hash(t2)
  5     use_hash(t3)
  6     swap_join_inputs(t3)
  7     use_hash(t4)
  8     no_swap_join_inputs(t4)
  9     */  * from t3,t4,t2,t1
10   where t1.object_id=t2.object_id
11   and t2.object_name=t3.object_name
12   and t3.owner=t4.owner
13   and t4.owner='MYDB'
14  /

SQL >select
  2     /*+
  3     ordered
  4     use_hash(t2)
  5     use_hash(t3)
  6     swap_join_inputs(t3)
  7     use_hash(t4)
  8     no_swap_join_inputs(t4)
  9     */  * from t1,t2,t3,t4
10   where t1.object_id=t2.object_id
11   and t2.object_name=t3.object_name
12   and t3.owner=t4.owner
13   and t4.owner='MYDB'
14  /

轉自：http://blog.csdn.net/mantisxf/article/details/5560429

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

相關文章