HINT篇---優化器相關

一：和優化器相關的hint
1、/*+ ALL_ROWS */
 表明對語句塊選擇基於開銷的優化方法,並獲得最佳吞吐量,使資源消耗最小化.
2、/*+ FIRST_ROWS*/
 表明對語句塊選擇基於開銷的優化方法,並獲得最佳響應時間,使資源消耗最小化.
3、/*+ RULE*/
 表明對語句塊選擇基於規則的優化方法.
詳解：對於optimizer_mode預設就是ALL_ROWS 的，all_rows的意思就是說所有的結果全查出來後在一起返回給使用者，比較適合報表等平時的查詢，而first_rows是隻有查出來一條就顯示一條，比較適合分頁的查詢，但如果要將所有都查出來的話肯定是all_rows快，first_rows(n),這裡的n為3就是查出3條就顯示。而rule是表示按照RBO的方式走。
實驗：
SQL> select  /*+first_ROWS(2) */ object_name from t1 where object_id = 600;

64 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 838529891

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     3 |    87 |   306   (2)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T1   |     3 |    87 |   306   (2)| 00:00:04 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=600)

Statistics
----------------------------------------------------------
          1  recursive calls
          1  db block gets
      23304  consistent gets
          0  physical reads
          0  redo size
       1667  bytes sent via SQL*Net to client
        568  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         64  rows processed

SQL> select  /*+first_ROWS(20) */ object_name from t1 where object_id = 600;

64 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 838529891

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    21 |   609 |  2135   (2)| 00:00:26 |
|*  1 |  TABLE ACCESS FULL| T1   |    21 |   609 |  2135   (2)| 00:00:26 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=600)

Statistics
----------------------------------------------------------
          1  recursive calls
          1  db block gets
      23304  consistent gets
          0  physical reads
          0  redo size
       1667  bytes sent via SQL*Net to client
        568  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         64  rows processed
對於first_rows(n)取值不同，消耗的cost值也不同，時間也就不一樣。所以說對於要求訪問速度快的話，first_rows還是比較有用的。
SQL> select  /*+all_ROWS */ object_name from t1 where object_id = 600;

64 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 838529891

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    63 |  1827 |  6420   (2)| 00:01:18 |
|*  1 |  TABLE ACCESS FULL| T1   |    63 |  1827 |  6420   (2)| 00:01:18 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=600)

Statistics
----------------------------------------------------------
          1  recursive calls
          1  db block gets
      23304  consistent gets
          0  physical reads
          0  redo size
       1667  bytes sent via SQL*Net to client
        568  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         64  rows processed

SQL> select  /*+first_ROWS */ object_name from t1 where object_id = 600;

64 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 838529891

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    63 |  1827 |  6420   (2)| 00:01:18 |
|*  1 |  TABLE ACCESS FULL| T1   |    63 |  1827 |  6420   (2)| 00:01:18 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=600)

Statistics
----------------------------------------------------------
          1  recursive calls
          1  db block gets
      23304  consistent gets
          0  physical reads
          0  redo size
       1667  bytes sent via SQL*Net to client
        568  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         64  rows processed
而對於first_rows不加n得到的結果和all_rows消耗的cost值是一樣的。所以在使用first_rows的時候還是需要帶一個引數較好。

 

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