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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24500180/viewspace-1064686/