备案控制台
开发者社区 数据库 文章 正文

optimizer_index_caching和optimizer_index_cost_adj两个参数说明

2017-12-01 1588
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： optimizer_index_caching和optimizer_index_cost_adj两个参数说明 OPTIMIZER_INDEX_COST_ADJ Property ...

optimizer_index_caching和optimizer_index_cost_adj两个参数说明



OPTIMIZER_INDEX_COST_ADJ

Property Description
Parameter type Integer
Default value 100
Modifiable ALTER SESSION, ALTER SYSTEM
Range of values 1 to 10000

OPTIMIZER_INDEX_COST_ADJ lets you tune optimizer behavior for access path selection to be more or less index friendly—that is, to make the optimizer more or less prone to selecting an index access path over a full table scan.

The default for this parameter is 100 percent, at which the optimizer evaluates index access paths at the regular cost. Any other value makes the optimizer evaluate the access path at that percentage of the regular cost. For example, a setting of 50 makes the index access path look half as expensive as normal.

Note:

The adjustment does not apply to user-defined cost functions for domain indexes.

See Also:

Oracle Database Performance Tuning Guide for more information on setting this parameter, and on its relationship to "OPTIMIZER_INDEX_CACHING"

OPTIMIZER_INDEX_CACHING

Property Description
Parameter type Integer
Default value 0
Modifiable ALTER SESSION, ALTER SYSTEM
Range of values 0 to 100

OPTIMIZER_INDEX_CACHING lets you adjust the behavior of cost-based optimization to favor nested loops joins and IN-list iterators.

The cost of executing an index using an IN-list iterator or of executing a nested loops join when an index is used to access the inner table depends on the caching of that index in the buffer cache. The amount of caching depends on factors that the optimizer cannot predict, such as the load on the system and the block access patterns of different users.

You can modify the optimizer's assumptions about index caching for nested loops joins and IN-list iterators by setting this parameter to a value between 0 and 100 to indicate the percentage of the index blocks the optimizer should assume are in the cache. Setting this parameter to a higher value makes nested loops joins and IN-list iterators look less expensive to the optimizer. As a result, it will be more likely to pick nested loops joins over hash or sort-merge joins and to pick indexes using IN-list iterators over other indexes or full table scans. The default for this parameter is 0, which results in default optimizer behavior.

See Also:

Oracle Database Performance Tuning Guide for more information on setting this parameter




一、optimizer_index_cost_adj参数
优化器计算通过索引扫描访问表数据的cost开销，可以通过这个参数进行调整。参数可用值的范围为1到10000。默认值为100，超过100后越大则越会使索引扫描的COST开销越高（计算的），从而导致查询优化器更加倾向于使用全表扫描。相反，值越小于100，计算出来的索引扫描的开销就越低。
注意：
1、这里描述的开销，仅仅为优化器评估出来的而已，而非实际执行的开销；例如同样的SQL语句，同样的执行路径，修改这个参数以后，计算出来的cost不同，但是，SQL语句执行的实际物理路径、时间、逻辑读都是一样的。
2、这个参数影响优化器评估索引访问的IO开销
3、可以参见后面的示例进一步理解这个参数

二、optimizer_index_caching参数
用于在执行in-list遍历和嵌套循环连接时，优化器评估已经存在于buffer cache中的索引块的数量（以百分比的方式）。参数的取值范围是0到100，默认值为0,取值越大就越减少优化器在评估In-list和嵌套循环连接的索引扫描的开销COST。
1、这里的描述仅仅为优化器的评估结果。换句话说，它不是用来指定数据库实际缓存的每个索引块的数量
2、可以参见后面的示例进一步理解这个参数

三、索引范围扫描的成本计算公式（与两个参数相结合）
cost = { (blevel+leaf_blocks * effective index selectivity)*(1-optimizer_index_caching/100)+
          cluster_factor * effective table selectivity)*(optimizer_index_cost_adj/100) }
说明
1、 blevel=索引的层数，即dba_indexes视图中的blevel列值
2、 leaf_blocks为索引的叶子块数量,即 dba_indexes视图中的leaf_blocks列值
3、 effective index selectivity指的是SQL语句中用于索引扫描的 谓词 驱动条件的列字段的选择率（ where条件中）
4、cluster_factor为索引的聚簇因子（表示索引数据与表数据排序后的相近程度）
5、 effective table selectivity指的是SQL where条件中能够在索引上进行过滤的所有谓词列字段的选择率（可以理解为通过索引扫描后（包含驱动和过滤），最后通过rowid定位表的数量）
6、一般是4、5两个部分往往是整个计算公式中权重最大的因素。

注意
1、从上面的成本计算公式和说明，我们知道， optimizer_index_cost_adj参数对优化器的影响要远远大于 optimizer_index_caching参数
2、随着优化器越来越智能这两个参数的作用已经慢慢被 系统统计信息 给替换了。
3、10gR2开始，这两个参数的值尽量保持为默认值。如果实在需要调整，请严格测试！


四、optimizer_index_cost_adj参数示例
SQL >   create   table  t  as   select  rownum rn ,object_name  name   from  dba_objects a  where  rownum < 5000 ;
Table  created.
SQL >   create   index  t_idx  on  t(rn);
Index  created.
SQL >   BEGIN
   2   
   3     DBMS_STATS.GATHER_TABLE_STATS(OWNNAME = > 'SYS' ,
   4                                   TABNAME = > 'T' ,
   5                                   ESTIMATE_PERCENT = > 30 ,
   6                                   METHOD_OPT = > 'FOR ALL COLUMNS SIZE 1' ,
   7                                   NO_INVALIDATE = > FALSE ,
   8                                    CASCADE = > TRUE ,
   9                                   DEGREE  = >   4 ); 
  10    END ; 
  11    /
PL / SQL   procedure  successfully completed.
SQL >    explain  plan  for   select   *   from  t  where  rn < 200 ;
Explained.
SQL >   select   *   from   table (dbms_xplan.display);
PLAN_TABLE_OUTPUT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Plan hash  value :  470836197

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| Id  |  Operation                    |  Name   |  Rows   | Bytes | Cost (%CPU)|  Time      |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
|    0  |  SELECT   STATEMENT             |       |    199  |   4179  |      3    ( 0 )| 00:00: 35  |
|    1  |   TABLE   ACCESS   BY   INDEX  ROWID| T     |    199  |   4179  |      3    ( 0 )| 00:00: 35  |
| *    2  |    INDEX  RANGE SCAN          | T_IDX |    199  |       |      2    ( 0 )| 00:00: 24  |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Predicate Information (identified  by   operation  id):
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    2   -   access ( "RN" < 200 )

14   rows  selected.

SQL >   alter   session   set  optimizer_index_cost_adj = 50 ;
Session  altered.
SQL >   explain  plan  for   select   *   from  t  where  rn < 200 ;
Explained.
SQL >   select   *   from   table (dbms_xplan.display);
PLAN_TABLE_OUTPUT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Plan hash  value :  470836197
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| Id  |  Operation                    |  Name   |  Rows   | Bytes | Cost (%CPU)|  Time      |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
|    0  |  SELECT   STATEMENT             |       |    199  |   4179  |      2    ( 0 )| 00:00: 18  |
|    1  |   TABLE   ACCESS   BY   INDEX  ROWID| T     |    199  |   4179  |      2    ( 0 )| 00:00: 18  |
| *    2  |    INDEX  RANGE SCAN          | T_IDX |    199  |       |      1    ( 0 )| 00:00: 12  |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Predicate Information (identified  by   operation  id):
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    2   -   access ( "RN" < 200 )
14   rows  selected.

SQL >   alter   session   set  optimizer_index_cost_adj = 500 ;
Session  altered.
SQL >    explain  plan  for   select   *   from  t  where  rn < 200 ;
Explained.
SQL >   select   *   from   table (dbms_xplan.display);
PLAN_TABLE_OUTPUT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Plan hash  value :  1601196873
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| Id  |  Operation          |  Name  |  Rows   | Bytes | Cost (%CPU)|  Time      |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
|    0  |  SELECT   STATEMENT   |      |    199  |   4179  |      6    ( 0 )| 00: 01 : 10  |
| *    1  |   TABLE   ACCESS   FULL | T    |    199  |   4179  |      6    ( 0 )| 00: 01 : 10  |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Predicate Information (identified  by   operation  id):
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    1   -  filter( "RN" < 200 )
13   rows  selected.


五、optimizer_index_caching参数示例
IN-LIST示例
SQL >   set  linesize  200  pagesize  9999
SQL >   explain  plan  for   select   *   from  t  where  rn  in  ( 1 , 2 , 3 , 4 , 5 , 6 , 7 , 100 , 130 , 200 , 240 , 2000 );
Explained.
SQL >   select   *   from   table (dbms_xplan.display);
PLAN_TABLE_OUTPUT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Plan hash  value :  3506647781
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| Id  |  Operation                     |  Name   |  Rows   | Bytes | Cost (%CPU)|  Time      |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
|    0  |  SELECT   STATEMENT              |       |     12  |    252  |      4    ( 0 )| 00:00: 43  |
|    1  |  INLIST ITERATOR             |       |       |       |            |          |
|    2  |    TABLE   ACCESS   BY   INDEX  ROWID| T     |     12  |    252  |      4    ( 0 )| 00:00: 43  |
| *    3  |     INDEX  RANGE SCAN          | T_IDX |     12  |       |      3    ( 0 )| 00:00: 31  |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Predicate Information (identified  by   operation  id):
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    3   -   access ( "RN" = 1   OR   "RN" = 2   OR   "RN" = 3   OR   "RN" = 4   OR   "RN" = 5   OR   "RN" = 6   OR
               "RN" = 7   OR   "RN" = 100   OR   "RN" = 130   OR   "RN" = 200   OR   "RN" = 240   OR   "RN" = 2000 )
16   rows  selected.
SQL >   alter   session   set  optimizer_index_caching = 10 ;
Session  altered.
SQL >    explain  plan  for   select   *   from  t  where  rn  in  ( 1 , 2 , 3 , 4 , 5 , 6 , 7 , 100 , 130 , 200 , 240 , 2000 );

Explained.

SQL >    select   *   from   table (dbms_xplan.display);

PLAN_TABLE_OUTPUT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Plan hash  value :  3506647781

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| Id  |  Operation                     |  Name   |  Rows   | Bytes | Cost (%CPU)|  Time      |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
|    0  |  SELECT   STATEMENT              |       |     12  |    252  |      3    ( 0 )| 00:00: 31  |
|    1  |  INLIST ITERATOR             |       |       |       |            |          |
|    2  |    TABLE   ACCESS   BY   INDEX  ROWID| T     |     12  |    252  |      3    ( 0 )| 00:00: 31  |
| *    3  |     INDEX  RANGE SCAN          | T_IDX |     12  |       |      2   (0) | 00:00: 19  |   --可以看到cost确实降低了
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

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

    3   -   access ( "RN" = 1   OR   "RN" = 2   OR   "RN" = 3   OR   "RN" = 4   OR   "RN" = 5   OR   "RN" = 6   OR
               "RN" = 7   OR   "RN" = 100   OR   "RN" = 130   OR   "RN" = 200   OR   "RN" = 240   OR   "RN" = 2000 )

16   rows  selected
嵌套循环连接示例：
SQL >   explain  plan  for   select   / * +  use_nl(a b)  * /   *   from  t a,t b  where  a.rn = b.rn  and  b. name = 'sss' ;
Explained.
SQL >    select   *   from   table (dbms_xplan.display);
PLAN_TABLE_OUTPUT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Plan hash  value :  752965310
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| Id  |  Operation                     |  Name   |  Rows   | Bytes | Cost (%CPU)|  Time      |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
|    0  |  SELECT   STATEMENT              |       |      1  |     42  |      8    ( 0 )| 00: 01 : 34  |
|    1  |  NESTED LOOPS                |       |       |       |            |          |
|    2  |   NESTED LOOPS               |       |      1  |     42  |      8    ( 0 )| 00: 01 : 34  |
| *    3  |     TABLE   ACCESS   FULL          | T     |      1  |     21  |      6    ( 0 )| 00: 01 : 10  |
| *    4  |     INDEX  RANGE SCAN          | T_IDX |      1  |       |      1    ( 0 )| 00:00: 12  |
|    5  |    TABLE   ACCESS   BY   INDEX  ROWID| T     |      1  |     21  |      2    ( 0 )| 00:00: 24  |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Predicate Information (identified  by   operation  id):
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    3   -  filter( "B" . "NAME" = 'sss' )
    4   -   access ( "A" . "RN" = "B" . "RN" )
18   rows  selected.
SQL >   alter   session   set  optimizer_index_caching = 10 ;
Session  altered.
SQL >   explain  plan  for   select   / * +  use_nl(a b)  * /   *   from  t a,t b  where  a.rn = b.rn  and  b. name = 'sss' ;
Explained.
SQL >   select   *   from   table (dbms_xplan.display);
PLAN_TABLE_OUTPUT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Plan hash  value :  752965310
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| Id  |  Operation                     |  Name   |  Rows   | Bytes | Cost (%CPU)|  Time      |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
|    0  |  SELECT   STATEMENT              |       |      1  |     42  |      7    ( 0 )| 00: 01 : 22  |
|    1  |  NESTED LOOPS                |       |       |       |            |          |
|    2  |   NESTED LOOPS               |       |      1  |     42  |      7   (0) | 00: 01 : 22  |  --可以看到cost确实降低了
| *    3  |     TABLE   ACCESS   FULL          | T     |      1  |     21  |      6    ( 0 )| 00: 01 : 10  |
| *    4  |     INDEX  RANGE SCAN          | T_IDX |      1  |       |      0    ( 0 )| 00:00: 01  |
|    5  |    TABLE   ACCESS   BY   INDEX  ROWID| T     |      1  |     21  |      1    ( 0 )| 00:00: 12  |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

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

    3   -  filter( "B" . "NAME" = 'sss' )
    4   -   access ( "A" . "RN" = "B" . "RN" )

18   rows  selected.



非in-list和嵌套循环操作，调整这个参数时，不会影响oracle优化器成本的运算，如下
SQL >   set  linesize  200  pagesize  999
SQL >   explain  plan  for   select   *   from  t  where  rn < 200 ;
Explained.
SQL >   select   *   from   table (dbms_xplan.display);
PLAN_TABLE_OUTPUT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Plan hash  value :  470836197
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| Id  |  Operation                    |  Name   |  Rows   | Bytes | Cost (%CPU)|  Time      |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
|    0  |  SELECT   STATEMENT             |       |    199  |   4179  |      3    ( 0 )| 00:00: 35  |
|    1  |   TABLE   ACCESS   BY   INDEX  ROWID| T     |    199  |   4179  |      3    ( 0 )| 00:00: 35  |
| *    2  |    INDEX  RANGE SCAN          | T_IDX |    199  |       |      2    ( 0 )| 00:00: 24  |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Predicate Information (identified  by   operation  id):
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    2   -   access ( "RN" < 200 )
14   rows  selected.
SQL >   alter   session   set  optimizer_index_caching = 1 ;
Session  altered.
SQL >   explain  plan  for   select   *   from  t  where  rn < 200 ;
Explained.
SQL >   select   *   from   table (dbms_xplan.display);
PLAN_TABLE_OUTPUT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Plan hash  value :  470836197
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| Id  |  Operation                    |  Name   |  Rows   | Bytes | Cost (%CPU)|  Time      |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
|    0  |  SELECT   STATEMENT             |       |    199  |   4179  |      3    ( 0 )| 00:00: 35  |
|    1  |   TABLE   ACCESS   BY   INDEX  ROWID| T     |    199  |   4179  |      3    ( 0 )| 00:00: 35  |
| *    2  |    INDEX  RANGE SCAN          | T_IDX |    199  |       |      2   (0) | 00:00: 24  |  --可以看到cost确实没有改变
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Predicate Information (identified  by   operation  id):
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    2   -   access ( "RN" < 200 )
14   rows  selected.




OPTIMIZER_INDEX_COST_ADJ
这个初始化参数代表一个百分比,取值范围在1到10000之间.
该参数表示索引扫描和全表扫描成本的比较。缺省值100表示索引扫描成本等价转换与全表扫描成本。

这些参数对于CBO的执行具有重大影响，其缺省值对于数据库来说通常需要调整。
一般来说对于OPTIMIZER_INDEX_CACHING可以设置为90左右
对于大多数OLTP系统，OPTIMIZER_INDEX_COST_ADJ可以设置在10到50之间。对于数据仓库和DSS系统，
可能不能简单的把OPTIMIZER_INDEX_COST_ADJ设置为50，通常我们需要反复调整取得一个合理值.
更为具体的可以根据统计信息,db file scattered reads/db file sequential reads来计算.

本文通过实验对该参数的使用作出探讨和说明.

我们看到optimizer_index_cost_adj的缺省值为100.

 

[oracle@jumper udump]$ sqlplus eygle/eygle

SQL*Plus: Release 9.2.0.3.0 - Production on Mon Jun 28 17:11:15 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production

SQL> show parameter optimizer_index_cost_adj

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_cost_adj integer 100
SQL>

创建测试表:

 

SQL> create table t as select * from dba_objects;

Table created.

SQL> create index ind_owner on t(owner);

Index created.

SQL> analyze table t compute statistics;

Table analyzed.

 

我们分别观察一下全表扫描和索引访问的成本:

 

SQL> set autotrace traceonly


SQL> select * from t where owner='EYGLE';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=476 Bytes=36652)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=14 Card=476 Bytes=36652)



SQL> select /*+ index(t ind_owner) */ * from t where owner='EYGLE';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=16 Card=476 Bytes=36652)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=16 Card=476 Bytes=36652)
2 1 INDEX (RANGE SCAN) OF 'IND_OWNER' (NON-UNIQUE) (Cost=2 Card=476)

 

Oracle在选择不同的访问路径时，会对全表扫描和索引扫描进行比较评估.

在比较的时候，Oracle会把索引扫描的成本转换为全表扫描的成本，和全表扫描的COST进行比较.这个转换需要一个转换因子.
就是optimizer_index_cost_adj:

optimizer_index_cost_adj * (Index Scan Cost) = 等价的 Full Scan Cost

这个 等价的 Full Scan Cost 就是来和全表扫描成本进行比较的.

而这个转换因子的临界值实际上就是Full Scan Cost 和 Index Scan Cost的比值.

即:


optimizer_index_cost_adj = Full Scan Cost / Index Scan Cost

 

SQL> set autotrace off
SQL> select (14/16)*100 from dual;

(14/16)*100
-----------
87.5

1 row selected. 

 

我们通过调整optimizer_index_cost_adj来看一下执行计划的变化:

 

SQL> set autotrace traceonly

SQL> alter session set optimizer_index_cost_adj = 87;

Session altered.

SQL> select * from t where owner='EYGLE';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=476 Bytes=36652)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=14 Card=476 Bytes=36652)
2 1 INDEX (RANGE SCAN) OF 'IND_OWNER' (NON-UNIQUE) (Cost=2 Card=476)

此时使用索引成本较低.等价全表扫描成本为:

87% * (Index Scan Cost) < Full Scan Cost

此时Oracle选择了索引.

SQL> alter session set optimizer_index_cost_adj = 88;

Session altered.

SQL> select * from t where owner='EYGLE';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=476 Bytes=36652)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=14 Card=476 Bytes=36652)

此时使用索引成本较高.等价全表扫描成本为:

88% * (Index Scan Cost) > Full Scan Cost

所以Oracle选择了全表扫描. 



 


参考文档:

http://www.evdbt.com/SearchIntelligenceCBO.doc

 












About Me

.............................................................................................................................................

● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除

● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址：http://www.cnblogs.com/lhrbest

● 本文pdf版、个人简介及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● DBA宝典今日头条号地址：http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

.............................................................................................................................................

● QQ群号：230161599（满）、618766405

● 微信群：可加我微信，我拉大家进群，非诚勿扰

● 联系我请加QQ好友（646634621），注明添加缘由

● 于 2017-11-01 09:00 ~ 2017-11-30 22:00 在魔都完成

● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

.............................................................................................................................................

● 小麦苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

● 小麦苗出版的数据库类丛书：http://blog.itpub.net/26736162/viewspace-2142121/

.............................................................................................................................................

使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号（xiaomaimiaolhr）及QQ群（DBA宝典），学习最实用的数据库技术。

   小麦苗的微信公众号      小麦苗的DBA宝典QQ群2     《DBA笔试面宝典》读者群       小麦苗的微店

.............................................................................................................................................

img_e3029f287d989cd04bd75432ecc1c172.png
 DBA笔试面试讲解群
 《DBA宝典》读者群 欢迎与我联系



文章标签：
索引
移动开发
SQL
关系型数据库
Oracle
小麦苗
目录
相关文章
snowofsummer
|
1月前
Adaptive Query Plans
Adaptive Query Plans
snowofsummer
12 0
不雨_亦潇潇
|
1月前
|
机器学习/深度学习 人工智能
【CatBoost报错解决】CatBoostError: Bad value for num feature[non default doc idx=0,feature idx=19]=
【CatBoost报错解决】CatBoostError: Bad value for num feature[non default doc idx=0,feature idx=19]=
不雨_亦潇潇
66 0
一个处女座的程序猿
|
索引
成功解决_catboost.CatBoostError: Invalid cat_features[4] = 8 value: index must be ＜ 8.
成功解决_catboost.CatBoostError: Invalid cat_features[4] = 8 value: index must be ＜ 8.
一个处女座的程序猿
274 0
六月的雨在钉钉
|
数据库
Data truncation: Out of range value for column ‘estimate_score‘
Data truncation: Out of range value for column ‘estimate_score‘
六月的雨在钉钉
634 0
德哥,digoal
|
存储 关系型数据库 Go
PostgreSQL 11 内核优化 - 降低vacuum cleanup阶段index scan概率 ( vacuum_cleanup_index_scale_factor , skip index vacuum cleanup stage)
PostgreSQL 11 内核优化 - 降低vacuum cleanup阶段index scan概率 ( vacuum_cleanup_index_scale_factor , skip index vacuum cleanup stage)
德哥,digoal
1234 0
颇忒脱
|
Prometheus Cloud Native
详解Prometheus range query中的step参数
详细介绍了Prometheus查询参数step的作用
颇忒脱
7924 0
小桥河西
|
大数据 测试技术
关于citus.limit_clause_row_fetch_count优化参数
小桥河西
2129 0
郭大瘦
|
PyTorch 算法框架/工具
A trap of parameter ‘size_average’ in pytorch 详解
pytorch的入门教程中有这样的一句： 1 2 3 4 5 loss = torch.nn.
郭大瘦
1757 0
科技小能手
|
索引
Know more about CBO Index Cost
科技小能手
1047 0
科技小先锋
|
SQL 关系型数据库 Oracle
_optimizer_cost_based_transformation隐含参数解决07445问题
科技小先锋
2922 0