optimizer_index_cost_adj和optimizer_index_caching对CBO的影响
DB：10205
OS: HP UX B.11.11
目的：
测试optimizer_index_cost_adj和optimizer_index_caching对CBO生成执行计划的影响，前者的取值范围0-10000，默认为100；后者则为0-100，默认为0；
关于这两个参数的资料比较少，只找到如下一些；
《oracle高效设计》大致记录如下：optimizer_index_caching告诉数据库buffer cache中索引块的百分比，值越高则意味cache中的索引块数越多，其相应的io cost越小，optimizer_index_cost_adj则相反；
由冯大辉等人翻译的《oracle性能诊断艺术》则有着更详细的描述，书中提供了两个计算索引io代价公式
io_cost = (blevel + (leaf_blocks + clustering_factor) * selectivity) * optimizer_index_cost_adj/100
io_cost = {(blevel + leaf_blocks*selectivity) * (1 – optimizer_index_caching/100) + clustering_factor * selectivity} * (optimizer_index_cost_adj/100)
很直观的描述了两个参数对CBO的影响，且optimizer_index_cost_adj对CBO的影响非常大，而optimizer_index_caching则相对小很多
下面做试验测试一下
设置三组测试案例，参数分别设置为
1 optimizer_index_cost_adj =100，optimizer_index_caching =0
2 optimizer_index_cost_adj =10，optimizer_index_caching =0
3 optimizer_index_cost_adj =10 ，optimizer_index_caching =100
创建测试表
SQL> create table tmp_t as select object_id,owner from dba_objects;
Table created.
SQL> select count(*) from tmp_t;
COUNT(*)
----------
41187
SQL> select count(*) from tmp_t where wner='PUBLIC';
COUNT(*)
----------
16036
SQL> create index tmp_index on tmp_t(owner);
Index created.
SQL> exec dbms_stats.gather_table_stats('SYS','TMP_T',estimate_percent => 100);
PL/SQL procedure successfully completed.
设置10053跟踪
1
optimizer_index_cost_adj = 100
optimizer_index_caching = 0
SQL> alter session set events '10053 trace name context forever, level 12';
Session altered.
SQL> set autotrace traceonly
SQL> select * from tmp_t where wner='PUBLIC';
16036 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1035233137
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16036 | 187K| 23 (14)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TMP_T | 16036 | 187K| 23 (14)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='PUBLIC')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1121 consistent gets
0 physical reads
0 redo size
354186 bytes sent via SQL*Net to client
12251 bytes received via SQL*Net from client
1071 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
16036 rows processed
SQL> alter session set events '10053 trace name context off';
Session altered.
跟踪文件
SYSTEM STATISTICS INFORMATION
*****************************
Using NOWORKLOAD Stats—使用的非工作量统计信息
CPUSPEED: 199 millions instruction/sec
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: TMP_T Alias: TMP_T
#Rows: 41187 #Blks: 49 AvgRowLen: 12.00
Index Stats::
Index: TMP_INDEX Col#: 2
LVLS: 1 #LB: 52 #DK: 57 LB/K: 1.00 DB/K: 5.00 CLUF: 290.00
***************************************
SINGLE TABLE ACCESS PATH
-----------------------------------------
BEGIN Single Table Cardinality Estimation
-----------------------------------------
Column (#2): OWNER(VARCHAR2)
AvgLen: 8.00 NDV: 57 Nulls: 0 Density: 1.2140e-05
Histogram: Freq #Bkts: 57 UncompBkts: 41187 EndPtVals: 57
Table: TMP_T Alias: TMP_T
Card: Original: 41187 Rounded: 16036 Computed: 16036.00 Non Adjusted: 16036.00
-----------------------------------------
END Single Table Cardinality Estimation
-----------------------------------------
Access Path: TableScan
Cost: 23.42 Resp: 23.42 Degree: 0
Cost_io: 20.00 Cost_cpu: 9538541
Resp_io: 20.00 Resp_cpu: 9538541
Access Path: index (AllEqRange)
Index: TMP_INDEX
resc_io: 134.00 resc_cpu: 7239716
ix_sel: 0.38935 ix_sel_with_filters: 0.38935
Cost: 136.60 Resp: 136.60 Degree: 1
Best:: AccessPath: TableScan
Cost: 23.42 Degree: 1 Resp: 23.42 Card: 16036.00 Bytes: 0
当两个参数设为默认值时，sql选择了意料之中的全表扫描，依据上文提到的公式计算一下
io_cost = (blevel + (leaf_blocks + clustering_factor) * selectivity) * optimizer_index_cost_adj/100 = (1 + (52 + 290) * 0.38935 ) * 1= 134.1
io_cost = {(blevel + leaf_blocks*selectivity) * (1 – optimizer_index_caching/100) + clustering_factor * selectivity} * (optimizer_index_cost_adj/100)
=(( 1 + 52 * 0.38935 ) * 1 + 290 * 0.38935 ) * 1 = 134.1
与CBO计算结果大致一样
2
optimizer_index_cost_adj = 10
optimizer_index_caching = 0
将optimizer_index_cost_adj由100改为10，依据上述公式，indexScan的io cost应该为案例1中的10%
SQL> set autotrace traceonly
SQL> alter session set events '10053 trace name context forever, level 12';
Session altered.
SQL> select * from tmp_t where wner='PUBLIC';
16036 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1018746618
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16036 | 187K| 14 (8)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TMP_T | 16036 | 187K| 14 (8)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TMP_INDEX | 16036 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='PUBLIC')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2192 consistent gets
0 physical reads
0 redo size
354186 bytes sent via SQL*Net to client
12251 bytes received via SQL*Net from client
1071 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
16036 rows processed
SQL> alter session set events '10053 trace name context off';
Session altered.
跟踪文件
Access Path: TableScan
Cost: 23.42 Resp: 23.42 Degree: 0
Cost_io: 20.00 Cost_cpu: 9538541
Resp_io: 20.00 Resp_cpu: 9538541
Access Path: index (AllEqRange)
Index: TMP_INDEX
resc_io: 134.00 resc_cpu: 7239716
ix_sel: 0.38935 ix_sel_with_filters: 0.38935
Cost: 13.66 Resp: 13.66 Degree: 1
Best:: AccessPath: IndexRange Index: TMP_INDEX
Cost: 13.66 Degree: 1 Resp: 13.66 Card: 16036.00 Bytes: 0
将参数代入公式
io_cost = (blevel + (leaf_blocks + clustering_factor) * selectivity) * optimizer_index_cost_adj/100 = (1 + (52 + 290) * 0.38935 ) * 1/10= 13.4
io_cost = {(blevel + leaf_blocks*selectivity) * (1 – optimizer_index_caching/100) + clustering_factor * selectivity} * (optimizer_index_cost_adj/100)
=(( 1 + 52 * 0.38935 ) * 1 + 290 * 0.38935 ) * 1/10 = 13.4
实际结果和预料的一样，indexScan的cost下降为原来的十分之一，此时低于tableScan，故CBO选择了索引扫描
3
optimizer_index_cost_adj = 10
optimizer_index_caching = 100
Table Stats::
Table: TMP_T Alias: TMP_T
#Rows: 41187 #Blks: 49 AvgRowLen: 12.00
Index Stats::
Index: TMP_INDEX Col#: 2
LVLS: 1 #LB: 52 #DK: 57 LB/K: 1.00 DB/K: 5.00 CLUF: 290.00
…….
Access Path: TableScan
Cost: 23.42 Resp: 23.42 Degree: 0
Cost_io: 20.00 Cost_cpu: 9538541
Resp_io: 20.00 Resp_cpu: 9538541
Access Path: index (AllEqRange)
Index: TMP_INDEX
resc_io: 134.00 resc_cpu: 7239716
ix_sel: 0.38935 ix_sel_with_filters: 0.38935
Cost: 13.66 Resp: 13.66 Degree: 1
Best:: AccessPath: IndexRange Index: TMP_INDEX
Cost: 13.66 Degree: 1 Resp: 13.66 Card: 16036.00 Bytes: 0
选择索引所用的cost根案例2的一样，没有发生变化
计算cost
io_cost = (blevel + (leaf_blocks + clustering_factor) * selectivity) * optimizer_index_cost_adj/100 = (1 + (52 + 290) * 0.38935 ) * 1/10= 13.4
io_cost = {(blevel + leaf_blocks*selectivity) * (1 – optimizer_index_caching/100) + clustering_factor * selectivity} * (optimizer_index_cost_adj/100)
=(( 1 + 52 * 0.38935 ) * 0 + 290 * 0.38935 ) * 1/10 = 11.2
CBO在计算索引的io时，似乎更倾向于公式1，更改optimizer_index_caching对其影响微乎其微
其实更改这两个参数唯一可能影响的是CBO生成执行计划时到底选择全表扫描还是索引扫描，并不能更改两种访问路径的实际逻辑读，因此使用时应该倍加小心才对，至少目前我还没见过生产库上的应用案例。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/15480802/viewspace-736602/ ，如需转载，请注明出处，否则将追究法律责任。
