验证db_file_multiblock_read_count 和 MBRC对全表扫描时io cost的影响
前几天测试了optimizer_index_cost_adj和optimizer_index_caching对CBO生成执行计划的影响，但这两个参数只使用于索引扫描，今天做一组试验测试一下影响全表扫描的两个参数
OS：HP-UX B.11.11
DB: 10205
以下是《troubleshooting oracle performance》一书提供的公式
Io_cost = blocks/(1.6765 * db_file_multiblock_read_count(0.6581次方)) –非工作量统计信息
Io_cost= blocks/mbrc * mreadtim/sreadtim –工作量统计信息
查看aux_stat$，此时只有非工作量统计信息
SQL> select * from aux_stats$;
SNAME PNAME PVAL1 PVAL2
------------------------------ ------------------------------ ---------- ------------------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 06-15-2008 08:41
SYSSTATS_INFO DSTOP 06-15-2008 08:41
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 211.452141
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
--创建测试表
SQL> create table tmp_t as select owner,lpad('x',999,'x') name from dba_objects;
Table created.
SQL> select count(*) from tmp_t;
COUNT(*)
----------
16796
SQL> select owner,count(*) from tmp_t group by owner having count(*) >=500 order by 2;
OWNER COUNT(*)
------------------------------ ----------
SYSTEM 500
JUSTIN_STAGE 547
PUBLIC 2361
JUSTIN_RS 4108
SYS 7016
--表tmp_t上的owner列数据分布极度不均匀，在该列上创建索引并收集直方图
SQL> create index ind_t on tmp_t(owner);
Index created.
SQL> exec dbms_stats.gather_table_stats('SYS','TMP_T',estimate_percent => 100,method_opt =>'for all columns size skewonly',cascade => true);
PL/SQL procedure successfully completed.
SQL> select NUM_BUCKETS from dba_tab_columns where TABLE_NAME='TMP_T' and column_name ='OWNER';
NUM_BUCKETS
-----------
24
在非工作量统计信息下测试
案例1
optimizer_index_caching = 0
optimizer_index_cost_adj = 100
db_file_multiblock_read_count = 16
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='JUSTIN_RS';
4108 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1035233137
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4108 | 4035K| 403 (2)| 00:00:06 |
|* 1 | TABLE ACCESS FULL| TMP_T | 4108 | 4035K| 403 (2)| 00:00:06 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='JUSTIN_RS')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1457 consistent gets
0 physical reads
0 redo size
73135 bytes sent via SQL*Net to client
3495 bytes received via SQL*Net from client
275 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4108 rows processed
SQL> set autotrace off
SQL> select spid from v$process where addr=(select paddr from v$session where sid=(select sid from v$mystat where rownum=1));
SPID
------------
10283
跟踪文件摘录
SINGLE TABLE ACCESS PATH
-----------------------------------------
BEGIN Single Table Cardinality Estimation
-----------------------------------------
Column (#1): OWNER(VARCHAR2)
AvgLen: 7.00 NDV: 24 Nulls: 0 Density: 8.9307e-05
Histogram: Freq #Bkts: 24 UncompBkts: 16796 EndPtVals: 24
Table: TMP_T Alias: TMP_T
Card: Original: 16796 Rounded: 4108 Computed: 4108.00 Non Adjusted: 4108.00
-----------------------------------------
END Single Table Cardinality Estimation
-----------------------------------------
Access Path: TableScan
Cost: 403.11 Resp: 403.11 Degree: 0
Cost_io: 398.00 Cost_cpu: 15132816
Resp_io: 398.00 Resp_cpu: 15132816
Access Path: index (AllEqRange)
Index: IND_T
resc_io: 472.00 resc_cpu: 6119449
ix_sel: 0.24458 ix_sel_with_filters: 0.24458
Cost: 474.07 Resp: 474.07 Degree: 1
Best:: AccessPath: TableScan
Cost: 403.11 Degree: 1 Resp: 403.11 Card: 4108.00 Bytes: 0
当db_file_multiblock_read_count设置为16时，全表扫描的cost为403，索引扫描为474，因此选择了全表扫描
案例2
optimizer_index_caching = 0
optimizer_index_cost_adj = 100
db_file_multiblock_read_count = 4
SQL> alter session set db_file_multiblock_read_count=4;
Session altered.
SQL> select spid from v$process where addr=(select paddr from v$session where sid=(select sid from v$mystat where rownum=1));
SPID
------------
10773
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='JUSTIN_RS';
4108 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2532620847
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4108 | 4035K| 474 (1)| 00:00:07 |
| 1 | TABLE ACCESS BY INDEX ROWID| TMP_T | 4108 | 4035K| 474 (1)| 00:00:07 |
|* 2 | INDEX RANGE SCAN | IND_T | 4108 | | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='JUSTIN_RS')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
886 consistent gets
0 physical reads
0 redo size
73135 bytes sent via SQL*Net to client
3495 bytes received via SQL*Net from client
275 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4108 rows processed
SQL> alter session set events '10053 trace name context off';
Session altered.
SQL> set autotrace off
当db_file_multiblock_read_count由16下调至4时，同样的sql却选择了索引扫描
跟踪文件摘录
-----------------------------------------
BEGIN Single Table Cardinality Estimation
-----------------------------------------
Column (#1): OWNER(VARCHAR2)
AvgLen: 7.00 NDV: 24 Nulls: 0 Density: 8.9307e-05
Histogram: Freq #Bkts: 24 UncompBkts: 16796 EndPtVals: 24
Table: TMP_T Alias: TMP_T
Card: Original: 16796 Rounded: 4108 Computed: 4108.00 Non Adjusted: 4108.00
-----------------------------------------
END Single Table Cardinality Estimation
-----------------------------------------
Access Path: TableScan
Cost: 564.11 Resp: 564.11 Degree: 0
Cost_io: 559.00 Cost_cpu: 15132816
Resp_io: 559.00 Resp_cpu: 15132816
Access Path: index (AllEqRange)
Index: IND_T
resc_io: 472.00 resc_cpu: 6119449
ix_sel: 0.24458 ix_sel_with_filters: 0.24458
Cost: 474.07 Resp: 474.07 Degree: 1
Best:: AccessPath: IndexRange Index: IND_T
Cost: 474.07 Degree: 1 Resp: 474.07 Card: 4108.00 Bytes: 0
当db_file_multiblock_read_count设置为4时，全表扫描io cost为559，而索引扫描并为改动依旧为474
小结：当系统只有非工作量统计信息时，CBO在计算全表扫描io cost时使用db_file_multiblock_read_count作分母，即db_file_multiblock_read_count越大其cost越低，CBO更倾向于使用全表扫描；
收集工作量统计信息
测试mbrc对全表扫描的影响
SQL> exec dbms_stats.gather_system_stats(gathering_mode =>'interval',interval => 10)；
SQL> select * from aux_stats$;
SNAME PNAME PVAL1 PVAL2
------------------------------ ------------------------------ ---------- --------------------------------------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 08-01-2012 00:40
SYSSTATS_INFO DSTOP 08-01-2012 00:50
SYSSTATS_INFO FLAGS 0
SYSSTATS_MAIN CPUSPEEDNW 211.452141
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM 14.118
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED 216
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
13 rows selected.
因为现在系统很空闲，所以MBRC和MREADTIM都没有被统计，而当mbrc/sreadtim/mreadtim不可用时，CBO则会忽略工作量统计信息
因此需要手工设置这些值
SQL> exec dbms_stats.set_system_stats(pname =>'MBRC', pvalue => 8);
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.set_system_stats(pname =>'MREADTIM', pvalue => 40);
PL/SQL procedure successfully completed.
SQL> select * from aux_stats$;
SNAME PNAME PVAL1 PVAL2
------------------------------ ------------------------------ ---------- --------------------------------------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 08-01-2012 01:19
SYSSTATS_INFO DSTOP 08-01-2012 01:19
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 211.452141
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM 14.118
SYSSTATS_MAIN MREADTIM 40
SYSSTATS_MAIN CPUSPEED 216
SYSSTATS_MAIN MBRC 8
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
Io_cost= blocks/mbrc * mreadtim/sreadtim
案例3
optimizer_index_caching = 0
optimizer_index_cost_adj = 100
SREADTIM = 14.118
MREADTIM = 40
MBRC = 8
--因为收集了工作量统计信息，计算全表扫描io时不会再使用db_file_multiblock_read_count，因此不将其考虑在内
SQL> select spid from v$process where addr=(select paddr from v$session where sid=(select sid from v$mystat where rownum=1));
SPID
------------
23784
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='CDW_RS';
4108 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1035233137
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4108 | 4035K| 431 (2)| 00:00:07 |
|* 1 | TABLE ACCESS FULL| TMP_T | 4108 | 4035K| 431 (2)| 00:00:07 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='CDW_RS')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1457 consistent gets
0 physical reads
0 redo size
73135 bytes sent via SQL*Net to client
3495 bytes received via SQL*Net from client
275 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4108 rows processed
SQL> alter session set events '10053 trace name context off';
Session altered.
SQL> set autotrace off
跟踪文件摘录
***********************
Table Stats::
Table: TMP_T Alias: TMP_T
#Rows: 16796 #Blks: 1200 AvgRowLen: 1006.00
Index Stats::
Index: IND_T Col#: 1
LVLS: 1 #LB: 20 #DK: 24 LB/K: 1.00 DB/K: 79.00 CLUF: 1907.00
***************************************
SINGLE TABLE ACCESS PATH
-----------------------------------------
BEGIN Single Table Cardinality Estimation
-----------------------------------------
Column (#1): OWNER(VARCHAR2)
AvgLen: 7.00 NDV: 24 Nulls: 0 Density: 8.9307e-05
Histogram: Freq #Bkts: 24 UncompBkts: 16796 EndPtVals: 24
Table: TMP_T Alias: TMP_T
Card: Original: 16796 Rounded: 4108 Computed: 4108.00 Non Adjusted: 4108.00
-----------------------------------------
END Single Table Cardinality Estimation
-----------------------------------------
Access Path: TableScan
Cost: 430.96 Resp: 430.96 Degree: 0
Cost_io: 426.00 Cost_cpu: 15132816
Resp_io: 426.00 Resp_cpu: 15132816
Access Path: index (AllEqRange)
Index: IND_T
resc_io: 472.00 resc_cpu: 6119449
ix_sel: 0.24458 ix_sel_with_filters: 0.24458
Cost: 474.01 Resp: 474.01 Degree: 1
Best:: AccessPath: TableScan
Cost: 430.96 Degree: 1 Resp: 430.96 Card: 4108.00 Bytes: 0
全表扫描的io cost为426，而索引扫描为474，因此选择全表扫描
Full table scan = blocks/mbrc * mreadtim/sreadtim = 1200/8 * 40/14.118 = 425，该公式还是比较准确的
案例4
修改mbrc的值，由8改为4
optimizer_index_caching = 0
optimizer_index_cost_adj = 100
SREADTIM = 14.118
MREADTIM = 40
MBRC = 4
SQL> exec dbms_stats.set_system_stats(pname =>'MBRC', pvalue => 4);
PL/SQL procedure successfully completed.
SQL> select * from aux_stats$;
SNAME PNAME PVAL1 PVAL2
------------------------------ ------------------------------ ---------- --------------------------------------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 08-01-2012 01:39
SYSSTATS_INFO DSTOP 08-01-2012 01:39
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 211.452141
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM 14.118
SYSSTATS_MAIN MREADTIM 40
SYSSTATS_MAIN CPUSPEED 216
SYSSTATS_MAIN MBRC 4
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
13 rows selected.
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='CDW_RS';
4108 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2532620847
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4108 | 4035K| 474 (1)| 00:00:07 |
| 1 | TABLE ACCESS BY INDEX ROWID| TMP_T | 4108 | 4035K| 474 (1)| 00:00:07 |
|* 2 | INDEX RANGE SCAN | IND_T | 4108 | | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='CDW_RS')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1457 consistent gets
0 physical reads
0 redo size
73135 bytes sent via SQL*Net to client
3495 bytes received via SQL*Net from client
275 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4108 rows processed
SQL> alter session set events '10053 trace name context off';
Session altered.
SQL> set autotrace off
跟踪文件摘录
SINGLE TABLE ACCESS PATH
-----------------------------------------
BEGIN Single Table Cardinality Estimation
-----------------------------------------
Column (#1): OWNER(VARCHAR2)
AvgLen: 7.00 NDV: 24 Nulls: 0 Density: 8.9307e-05
Histogram: Freq #Bkts: 24 UncompBkts: 16796 EndPtVals: 24
Table: TMP_T Alias: TMP_T
Card: Original: 16796 Rounded: 4108 Computed: 4108.00 Non Adjusted: 4108.00
-----------------------------------------
END Single Table Cardinality Estimation
-----------------------------------------
Access Path: TableScan
Cost: 855.96 Resp: 855.96 Degree: 0
Cost_io: 851.00 Cost_cpu: 15132816
Resp_io: 851.00 Resp_cpu: 15132816
Access Path: index (AllEqRange)
Index: IND_T
resc_io: 472.00 resc_cpu: 6119449
ix_sel: 0.24458 ix_sel_with_filters: 0.24458
Cost: 474.01 Resp: 474.01 Degree: 1
Best:: AccessPath: IndexRange Index: IND_T
Cost: 474.01 Degree: 1 Resp: 474.01 Card: 4108.00 Bytes: 0
--mbrc作为分母出现在io cost计算公式中，将其由8改为4意味着全表扫描的io cost会变成原来的两倍，io cost = 425 * 2 =850 非常接近实际值
总结：
当系统没有工作量统计信息时，CBO使用db_file_multiblock_read_count计算全表扫描的io cost;
收集工作量统计信息后，则使用mbrc代替；
且两者都是作为分母出现，即值越大相应的io cost越小
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/15480802/viewspace-739468/ ，如需转载，请注明出处，否则将追究法律责任。
