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

optimizer_index_caching和optimizer_index_cost_adj两个参数说明

2017-12-01 1666
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 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笔试面宝典》读者群       小麦苗的微店

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


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



文章标签：
索引
移动开发
SQL
关系型数据库
Oracle
小麦苗
目录
相关文章
唐家四少官微
|
Windows
修改Windows服务的配置
修改Windows服务的配置
唐家四少官微
623 0
蓝易云
|
5月前
|
SQL Oracle 关系型数据库
Oracle的cursor_sharing参数详解
总的来说，cursor_sharing参数是一个非常重要的参数，它可以影响Oracle数据库的性能和稳定性。因此，我们需要根据我们的应用程序的特性和需求，以及我们的性能和稳定性的需求，来选择最合适的值。
蓝易云
226 23
何雨晨
|
11月前
|
SQL Oracle 关系型数据库
Oracle SQL：了解执行计划和性能调优
Oracle SQL：了解执行计划和性能调优
何雨晨
233 1
warmhearted
|
Ubuntu Linux 编译器
Linux通过/proc/version文件
`/proc/version`文件在Linux系统中提供当前内核版本详情，属于伪文件系统 `/proc`，展示内核、硬件和进程信息。通过`cat /proc/version`可查看，如`Linux version 5.4.0-80-generic...`，显示内核版本、编译日期等。但此文件不包含发行版信息，查询发行版详情可查看`/etc/os-release`或用`lsb_release`命令。
warmhearted
539 6
Ganos全空间数据库
|
消息中间件 存储 Kafka
Lindorm Ganos轨迹点快速聚合能力简介
本文介绍了Ganos时空数据库在Lindorm流引擎上的全新能力与最佳实践，帮助客户解决车辆网场景中轨迹点实时聚合生成轨迹线的能力。Lindorm Ganos实现了Lindorm宽表、流、计算等引擎在时空领域的打通，支持原生时空类型与多种时空算子，支持多种不同的时空索引，不仅可用于传统的周边查询，还面向了历史轨迹的查询分析、实时地理围栏查询、点面查询等更加复杂的业务需求。
Ganos全空间数据库
134671 10
姚远OracleACE
|
SQL 关系型数据库 MySQL
MySQL因为hung住，自动重新启动，InnoDB: Semaphore wait has lasted ＞ 600 seconds
从错误日志中检查，有两个信号量等待时间超长，MySQL自动crash了
姚远OracleACE
662 0
yuanzhengme
|
关系型数据库 MySQL
MySQL【问题 02】报错 1709 - Index column size too large. The maximum column size is 767 bytes. 可能是最简单的方法
MySQL【问题 02】报错 1709 - Index column size too large. The maximum column size is 767 bytes. 可能是最简单的方法
yuanzhengme
412 0
小麦苗
|
Oracle Unix 关系型数据库
[INS-06006] Passwordless SSH connectivity not set up between the following node(s)
[INS-06006] Passwordless SSH connectivity not set up between the following node(s)
小麦苗
1791 0
liuyunshengsir
|
SQL 关系型数据库 MySQL
MySQL 中启用大页（Huge Pages）提高性能
在 MySQL 中启用大页（Huge Pages）可以提高性能，还可以减少内存的使用。大页能够减少操作系统的页表，由此可减轻 CPU 负担和内存空间，从而减少了内存管理开销。下面是在 Linux 系统中启用 MySQL 大页的步骤： 1. 配置 Linux 系统大页：在物理或虚拟机上启用大页，请使用以下命令后设置了足够的大页： ```shell echo 2048 > /proc/sys/vm/nr_hugepages ``` 此处示例启用了 2048 个大页。请注意，此操作需要 root 用户权限。 2. 通过运行下列命令可检查您的系统是否已经启用了大页： ```shell gre
liuyunshengsir
1200 0
咸鱼运维杂谈
|
运维 监控 网络协议
Windows 产生大量 TIME_WAIT 连接
Windows 产生大量 TIME_WAIT 连接
咸鱼运维杂谈
514 0