optimizer_index_caching和optimizer_index_cost_adj两个参数说明

时间：2021-12-08 03:50:39

转载： 原文地址：http://blog.csdn.net/oradh/article/details/29576981


一、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.

标签：

相关文章