ITPub博客

HINT篇---优化器相关

转载 作者:波仔strong 时间:2018-05-27 16:03:10 0 删除 编辑

一:和优化器相关的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的时候还是需要带一个参数较好。

 

上一篇: oracle 索引详解
下一篇: Oracle中的hint
请登录后发表评论 登录
全部评论

注册时间:2017-08-25

  • 博文量
    87
  • 访问量
    33333