12条语句学会oracle cbo计算(九)
12条语句学会oracle cbo计算(九)
工作中,你可能会遇到统计数据正确,但就是不走你想要的执行计划的情况,最后一般通过hint或sql_profile解决.一条sql语句的性能主要依赖于好的物理结构,准确的系统统计数据,准确的对象统计数据,合理的查询优化器参数,合理的系统参数.这些因素也就是cost计算基本参数.因此产生的sql优化技术有很多,大概包含:改变访问结构,修改sql语句,加hint,改变执行环境,sql profile,stored outlines,sql plan baseline.其实无外乎就是通过调整sql适应环境或者通过调整环境适应sql.如果清楚Cost值的算法,就可以通过算法的对比,找到问题的真正原因,更有针对性的去解决问题.
全文主要参考Jonathan Lewis的<<基于成本的Oracle优化法则>>和黄玮(fuyuncat)的<>,特别黄玮(fuyuncat)的这本,是非常值得去学习的.
准备用14篇来描述完,前2篇是统计数据,算法公式说明,后12篇用12条语句分别去套用说明.
本篇例子的特征是单表,索引范围扫描,条件值常量,直方图,单条件,排序,和上一篇差别是有直方图
--产生测试数据
drop table scott.t_test1 purge;
create table scott.t_test1 as select * from dba_objects;
create index scott.idx_t_test1_01 on scott.t_test1(owner);
begin
dbms_stats.gather_table_stats('scott','t_test1',method_opt => 'for columns owner size 100');
end;
--产生语句的执行计划
--这里我是在pl/sql developer,是因为不用象10053那么麻烦就可以产生想要的几个值用以对比.
explain plan for select * from scott.t_test1 where owner='SYSTEM';
SELECT lpad(' ', 2 * (LEVEL - 1)) || operation operation,
options,
object_name,
cardinality,
bytes,
io_cost,
cpu_cost,
cost,
time
FROM plan_table
START WITH id = 0
CONNECT BY PRIOR id = parent_id;
/*
OPERATION OPTIONS OBJECT_NAME CARDINALITY BYTES IO_COST CPU_COST COST TIME
SELECT STATEMENT 456 44688 15 394952 15 1
TABLE ACCESS BY INDEX ROWID T_TEST1 456 44688 15 394952 15 1
INDEX RANGE SCAN IDX_T_TEST1_01 456 2 106293 2 1
*/
--查询表的统计数据
select rpad(table_name, 10, ' ') table_name,
rpad(num_rows, 10, ' ') num_rows,
rpad(blocks, 10, ' ') blocks,
avg_row_len
from dba_tables
where owner = 'SCOTT'
and table_name = 'T_TEST1';
/*
TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
T_TEST1 86335 1261 98
*/
--查询列的统计数据
select rpad(column_name, 12, ' ') column_name,
rpad(num_distinct, 8, ' ') num_distinct,
rpad(utl_raw.cast_to_varchar2(low_value), 15, ' ') low_value,
rpad(utl_raw.cast_to_varchar2(high_value), 10, ' ') high_value,
rpad(nullable, 8, ' ') nullable,
rpad(num_nulls, 8, ' ') num_nulls,
rpad(avg_col_len, 6, ' ') avg_col_len,
rpad(density, 20, ' ') density,
histogram
from dba_tab_columns
where owner = 'SCOTT'
and table_name = 'T_TEST1'
and column_name ='OWNER';
/*
COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE NULLABLE NUM_NULLS AVG_COL_LEN DENSITY HISTOGRAM
OWNER 23 APEX_030200 XDB Y 0 6 .0000057966636827901 FREQUENCY
*/
--查询索引的统计数据
select index_name,
rpad(blevel, 8, ' ') blevel,
rpad(leaf_blocks, 8, ' ') leaf_blocks,
rpad(distinct_keys, 8, ' ') distinct_keys,
rpad(clustering_factor, 15, ' ') clustering_factor,
num_rows
from dba_indexes
where owner = 'SCOTT'
and table_name = 'T_TEST1';
/*
INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS
IDX_T_TEST1_01 1 203 23 2301 86335
*/
--查询直方图数据
select column_name,
endpoint_number,
utl_raw.cast_to_varchar2(substr(lpad(to_char(endpoint_value,
'fmxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'),
30,
'0'),
1,
12)) char_value,
endpoint_value
from dba_tab_histograms
where owner = 'SCOTT' and table_name = 'T_TEST1' and column_name = 'OWNER'
order by endpoint_number;
/*
COLUMN_NAME ENDPOINT_NUMBER CHAR_VALUE ENDPOINT_VALUE
OWNER 174 APEX_0 3.39127382666026E35
OWNER 204 CTXSYS 3.49594609802084E35
OWNER 206 DBSNMP 3.54421425584994E35
OWNER 221 EXFSYS 3.60058907050643E35
OWNER 223 FLOWS_ 3.65008529278141E35
OWNER 343 MDSYR? 4.01192665542225E35
OWNER 380 OLAPSY 4.11738089644256E35
OWNER 384 ORACLE 4.11859780070016E35
OWNER 395 ORDDAT 4.1186001805076E35
OWNER 396 ORDPLU 4.11860021777884E35
OWNER 556 ORDSYS 4.11860022722044E35
OWNER 557 OWBSYS 4.11961276313739E35
OWNER 2653 PUBLIB 4.17113006167251E35
OWNER 2654 SCOTT 4.32325845824965E35
OWNER 5134 SYR??? 4.32772349650604E35
OWNER 5364 SYSMAN 4.32772373559899E35
OWNER 5393 SYSTEM 4.32772375731125E35
OWNER 5414 WMSYS 4.53298175814007E35
OWNER 5495 XDA??? 4.58306556462839E35
*/
--查询优化器参数
select rpad(name,40,' ') name,rpad(value,20,' ') value,isdefault
from (select nam.ksppinm name,
val.KSPPSTVL value,
--nam.ksppdesc description,
val.ksppstdf isdefault
from sys.x$ksppi nam, sys.x$ksppcv val
where nam.inst_id = val.inst_id
and nam.indx = val.indx)
where name in
('_db_file_optimizer_read_count', 'db_file_multiblock_read_count',
'_optimizer_block_size', '_table_scan_cost_plus_one',
'_optimizer_ceil_cost', '_optimizer_cost_model',
'_optimizer_cache_stats', '_smm_auto_min_io_size',
'_smm_auto_max_io_size', '_smm_min_size', '_smm_max_size',
'_smm_px_max_size', 'sort_area_retained_size', 'sort_area_size',
'workarea_size_policy','_optimizer_percent_parallel','optimizer_index_cost_adj');
/*
NAME VALUE ISDEFAULT
db_file_multiblock_read_count 116 TRUE
_db_file_optimizer_read_count 8 TRUE
sort_area_size 65536 TRUE
sort_area_retained_size 0 TRUE
_optimizer_cost_model CHOOSE TRUE
_optimizer_cache_stats FALSE TRUE
optimizer_index_cost_adj 100 TRUE
_table_scan_cost_plus_one TRUE TRUE
workarea_size_policy AUTO TRUE
_smm_auto_min_io_size 56 TRUE
_smm_auto_max_io_size 248 TRUE
_smm_min_size 286 TRUE
_smm_max_size 57344 TRUE
_smm_px_max_size 143360 TRUE
_optimizer_percent_parallel 101 TRUE
_optimizer_block_size 8192 TRUE
_optimizer_ceil_cost TRUE TRUE
*/
--查询系统统计数据
select rpad(pname, '20', ' ') pname,
rpad(pval1, '20', ' ') pval1,
rpad(pval2, '20', ' ') pval2
from SYS.AUX_STATS$
where sname = 'SYSSTATS_MAIN';
/*
PNAME PVAL1 PVAL2
CPUSPEED
CPUSPEEDNW 3074.07407407407
IOSEEKTIM 10
IOTFRSPEED 4096
MAXTHR
MBRC
MREADTIM
SLAVETHR
SREADTIM
*/
--需要应用第二篇中的公式:
(1)NDV=dba_tab_co1umns.num_distinct
(2)DENS=dba_tab_co1umns.DENSITY
(3)ALLROWS=dba_tab1es.NUM_ROWS
(4)HIGHVAL=dba_tab_co1umns.HIGH_VALUE
(5)LOWVAL=dba_tab_co1umns.LOW_VALUE
(6)COLNB=dba_tab_co1umns.NULLABLE
(9)OPTICA=优化器系统参数optimizer_index_cost_adj
(11)MBRC=优化器系统参数_db_fi1e_optimizer_read_count
(14)OPTBLKSIZE=优化器系统参数_optimizer_b1ock_size
(21)CPUSPEED=系统统计数据CPUSPEEDNW
(22)IOTFRSPEED=系统统计数据IOTFRSPEED
(23)IOSEEKTIM=系统统计数据IOSEEKTIM
(24)SREADTIM = IOSEEKTIM + OPTBLKSIZ/IOTFRSPEED
(25)MREADTIM = IOSEEKTIM + MBRC * OPTBLKSIZ/IOTFRSPEED
(45)= 的选择率为:(BEN[n]-BEN[n- 1 ])/MAXBEN*DECODE(COLNB= Y,1 ,NNV/ALLROWS)
(73)CPUCOST = #CPUCYCLES /(CPUSPEED*SREADTIM)/1000
(75)RBBLKS = BLVL
(76)RLBLKS = CEIL(LBLKS*INDACCSEL)
(77)IOCOST = ROUND(BLVL + CEIL(LBLKS*INDACCSEL) * OPTICA/100)
(79)IOCOST=ROUND((BLVL + CEIL(LBLKS*INDACCSEL) +CEIL(CLUF*INDFLTSEL))*OPTICA/100)
--套用上面的公式及数据进行计算
表名:T_TEST1
(3)ALLROWS=dba_tab1es.NUM_ROWS=86335
列名:OWNER
(1)NDV=dba_tab_co1umns.num_distinct=23
(2)DENS=dba_tab_co1umns.DENSITY=.0000057966636827901
(4)HIGHVAL=dba_tab_co1umns.HIGH_VALUE=XDB
(5)LOWVAL=dba_tab_co1umns.LOW_VALUE=APEX_030200
(6)COLNB=dba_tab_co1umns.NULLABLE=Y
dba_tab_co1umns.HISTOGRAM=FREQUENCY
(9)OPTICA=优化器系统参数optimizer_index_cost_adj=100
(11)MBRC=优化器系统参数_db_fi1e_optimizer_read_count=8
(14)OPTBLKSIZE=优化器系统参数_optimizer_b1ock_size=8192
(21)CPUSPEED=系统统计数据CPUSPEEDNW=3074.07407407407
(22)IOTFRSPEED=系统统计数据IOTFRSPEED=4096
(23)IOSEEKTIM=系统统计数据IOSEEKTIM=10
(24)SREADTIM = IOSEEKTIM + OPTBLKSIZ/IOTFRSPEED=10+8192/4096=12
(25)MREADTIM = IOSEEKTIM + MBRC * OPTBLKSIZ/IOTFRSPEED=10+8*8192/4096=26
--dba_tab_co1umns.HISTOGRAM=FREQUENCY
BEN[n]=匹配的dba_tab_histograms.ENDPOINT_NUMBER,OWNER='SYSTEM',匹配的是5393
所以BEN[n-1]就是BEN[n]的前一个,=5364
MAXBEN是最大的dba_tab_histograms.ENDPOINT_NUMBER,=5495
(45)= 的选择率为:(BEN[n]-BEN[n- 1 ])/MAXBEN*DECODE(COLNB= Y,1 ,NNV/ALLROWS)
SEL=(5393-5364)/5495*DECODE('Y','Y',1 ,86335/86335)
=0.00527752502274795
ROWS=ALLROWS*SEL=86335*0.00527752502274795=455.635122838944=456
--INDEX RANGE SCAN
BLVL=dba_indexes.BLEVEL=1
LBLKS=dba_indexes.LEAF_BLOCKS=203
INDACCSEL=SEL=0.00527752502274795
IDX_T_TEST1_01 1 203 23 2301 86335
(75)RBBLKS = BLVL=1
(76)RLBLKS = CEIL(LBLKS*INDACCSEL)=CEIL(203*0.00527752502274795)=2
(77)IOCOST = ROUND(BLVL + CEIL(LBLKS*INDACCSEL) * OPTICA/100)
= ROUND(1 + CEIL(203*0.00527752502274795) * 100/100)
=3
--TABLE ACCESS BY INDEX ROWID
CLUF=dba_indexes.CLUSTERING_FACTOR=2301
由于没有其他过滤条件,所以INDFLTSEL=INDACCSEL=0.00527752502274795
(79)IOCOST=ROUND((BLVL + CEIL(LBLKS*INDACCSEL) +CEIL(CLUF*INDFLTSEL))*OPTICA/100)
=ROUND((1 + CEIL(203*0.00527752502274795) +CEIL(2301*0.00527752502274795))*100/100)
=16
(73)CPUCOST = 394952 /(3074.07407407407*12)/1000
= 0.0107065301204819
COST=IOCOST+CPUCOST=16+0.0107065301204819=16.0107065301204819=16
--可以看到,结果与执行计划基本相同
ROWS=ALLROWS*SEL=86335*0.00527752502274795=455.635122838944=456
IOCOST[INDEX RANGE SCAN]=ROUND(1 + CEIL(203*0.00527752502274795) * 100/100)=3
IOCOST[TABLE ACCESS BY INDEX ROWID]=ROUND((1 + CEIL(203*0.00527752502274795) +CEIL(2301*0.00527752502274795))*100/100)=16
IOCOST=IOCOST[TABLE ACCESS BY INDEX ROWID]=16
CPUCOST = 394952 /(3074.07407407407*12)/1000=0.0107065301204819
COST=IOCOST+CPUCOST=16+0.0107065301204819=16.061376530120482=16
工作中,你可能会遇到统计数据正确,但就是不走你想要的执行计划的情况,最后一般通过hint或sql_profile解决.一条sql语句的性能主要依赖于好的物理结构,准确的系统统计数据,准确的对象统计数据,合理的查询优化器参数,合理的系统参数.这些因素也就是cost计算基本参数.因此产生的sql优化技术有很多,大概包含:改变访问结构,修改sql语句,加hint,改变执行环境,sql profile,stored outlines,sql plan baseline.其实无外乎就是通过调整sql适应环境或者通过调整环境适应sql.如果清楚Cost值的算法,就可以通过算法的对比,找到问题的真正原因,更有针对性的去解决问题.
全文主要参考Jonathan Lewis的<<基于成本的Oracle优化法则>>和黄玮(fuyuncat)的<
准备用14篇来描述完,前2篇是统计数据,算法公式说明,后12篇用12条语句分别去套用说明.
本篇例子的特征是单表,索引范围扫描,条件值常量,直方图,单条件,排序,和上一篇差别是有直方图
--产生测试数据
drop table scott.t_test1 purge;
create table scott.t_test1 as select * from dba_objects;
create index scott.idx_t_test1_01 on scott.t_test1(owner);
begin
dbms_stats.gather_table_stats('scott','t_test1',method_opt => 'for columns owner size 100');
end;
--产生语句的执行计划
--这里我是在pl/sql developer,是因为不用象10053那么麻烦就可以产生想要的几个值用以对比.
explain plan for select * from scott.t_test1 where owner='SYSTEM';
SELECT lpad(' ', 2 * (LEVEL - 1)) || operation operation,
options,
object_name,
cardinality,
bytes,
io_cost,
cpu_cost,
cost,
time
FROM plan_table
START WITH id = 0
CONNECT BY PRIOR id = parent_id;
/*
OPERATION OPTIONS OBJECT_NAME CARDINALITY BYTES IO_COST CPU_COST COST TIME
SELECT STATEMENT 456 44688 15 394952 15 1
TABLE ACCESS BY INDEX ROWID T_TEST1 456 44688 15 394952 15 1
INDEX RANGE SCAN IDX_T_TEST1_01 456 2 106293 2 1
*/
--查询表的统计数据
select rpad(table_name, 10, ' ') table_name,
rpad(num_rows, 10, ' ') num_rows,
rpad(blocks, 10, ' ') blocks,
avg_row_len
from dba_tables
where owner = 'SCOTT'
and table_name = 'T_TEST1';
/*
TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
T_TEST1 86335 1261 98
*/
--查询列的统计数据
select rpad(column_name, 12, ' ') column_name,
rpad(num_distinct, 8, ' ') num_distinct,
rpad(utl_raw.cast_to_varchar2(low_value), 15, ' ') low_value,
rpad(utl_raw.cast_to_varchar2(high_value), 10, ' ') high_value,
rpad(nullable, 8, ' ') nullable,
rpad(num_nulls, 8, ' ') num_nulls,
rpad(avg_col_len, 6, ' ') avg_col_len,
rpad(density, 20, ' ') density,
histogram
from dba_tab_columns
where owner = 'SCOTT'
and table_name = 'T_TEST1'
and column_name ='OWNER';
/*
COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE NULLABLE NUM_NULLS AVG_COL_LEN DENSITY HISTOGRAM
OWNER 23 APEX_030200 XDB Y 0 6 .0000057966636827901 FREQUENCY
*/
--查询索引的统计数据
select index_name,
rpad(blevel, 8, ' ') blevel,
rpad(leaf_blocks, 8, ' ') leaf_blocks,
rpad(distinct_keys, 8, ' ') distinct_keys,
rpad(clustering_factor, 15, ' ') clustering_factor,
num_rows
from dba_indexes
where owner = 'SCOTT'
and table_name = 'T_TEST1';
/*
INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS
IDX_T_TEST1_01 1 203 23 2301 86335
*/
--查询直方图数据
select column_name,
endpoint_number,
utl_raw.cast_to_varchar2(substr(lpad(to_char(endpoint_value,
'fmxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'),
30,
'0'),
1,
12)) char_value,
endpoint_value
from dba_tab_histograms
where owner = 'SCOTT' and table_name = 'T_TEST1' and column_name = 'OWNER'
order by endpoint_number;
/*
COLUMN_NAME ENDPOINT_NUMBER CHAR_VALUE ENDPOINT_VALUE
OWNER 174 APEX_0 3.39127382666026E35
OWNER 204 CTXSYS 3.49594609802084E35
OWNER 206 DBSNMP 3.54421425584994E35
OWNER 221 EXFSYS 3.60058907050643E35
OWNER 223 FLOWS_ 3.65008529278141E35
OWNER 343 MDSYR? 4.01192665542225E35
OWNER 380 OLAPSY 4.11738089644256E35
OWNER 384 ORACLE 4.11859780070016E35
OWNER 395 ORDDAT 4.1186001805076E35
OWNER 396 ORDPLU 4.11860021777884E35
OWNER 556 ORDSYS 4.11860022722044E35
OWNER 557 OWBSYS 4.11961276313739E35
OWNER 2653 PUBLIB 4.17113006167251E35
OWNER 2654 SCOTT 4.32325845824965E35
OWNER 5134 SYR??? 4.32772349650604E35
OWNER 5364 SYSMAN 4.32772373559899E35
OWNER 5393 SYSTEM 4.32772375731125E35
OWNER 5414 WMSYS 4.53298175814007E35
OWNER 5495 XDA??? 4.58306556462839E35
*/
--查询优化器参数
select rpad(name,40,' ') name,rpad(value,20,' ') value,isdefault
from (select nam.ksppinm name,
val.KSPPSTVL value,
--nam.ksppdesc description,
val.ksppstdf isdefault
from sys.x$ksppi nam, sys.x$ksppcv val
where nam.inst_id = val.inst_id
and nam.indx = val.indx)
where name in
('_db_file_optimizer_read_count', 'db_file_multiblock_read_count',
'_optimizer_block_size', '_table_scan_cost_plus_one',
'_optimizer_ceil_cost', '_optimizer_cost_model',
'_optimizer_cache_stats', '_smm_auto_min_io_size',
'_smm_auto_max_io_size', '_smm_min_size', '_smm_max_size',
'_smm_px_max_size', 'sort_area_retained_size', 'sort_area_size',
'workarea_size_policy','_optimizer_percent_parallel','optimizer_index_cost_adj');
/*
NAME VALUE ISDEFAULT
db_file_multiblock_read_count 116 TRUE
_db_file_optimizer_read_count 8 TRUE
sort_area_size 65536 TRUE
sort_area_retained_size 0 TRUE
_optimizer_cost_model CHOOSE TRUE
_optimizer_cache_stats FALSE TRUE
optimizer_index_cost_adj 100 TRUE
_table_scan_cost_plus_one TRUE TRUE
workarea_size_policy AUTO TRUE
_smm_auto_min_io_size 56 TRUE
_smm_auto_max_io_size 248 TRUE
_smm_min_size 286 TRUE
_smm_max_size 57344 TRUE
_smm_px_max_size 143360 TRUE
_optimizer_percent_parallel 101 TRUE
_optimizer_block_size 8192 TRUE
_optimizer_ceil_cost TRUE TRUE
*/
--查询系统统计数据
select rpad(pname, '20', ' ') pname,
rpad(pval1, '20', ' ') pval1,
rpad(pval2, '20', ' ') pval2
from SYS.AUX_STATS$
where sname = 'SYSSTATS_MAIN';
/*
PNAME PVAL1 PVAL2
CPUSPEED
CPUSPEEDNW 3074.07407407407
IOSEEKTIM 10
IOTFRSPEED 4096
MAXTHR
MBRC
MREADTIM
SLAVETHR
SREADTIM
*/
--需要应用第二篇中的公式:
(1)NDV=dba_tab_co1umns.num_distinct
(2)DENS=dba_tab_co1umns.DENSITY
(3)ALLROWS=dba_tab1es.NUM_ROWS
(4)HIGHVAL=dba_tab_co1umns.HIGH_VALUE
(5)LOWVAL=dba_tab_co1umns.LOW_VALUE
(6)COLNB=dba_tab_co1umns.NULLABLE
(9)OPTICA=优化器系统参数optimizer_index_cost_adj
(11)MBRC=优化器系统参数_db_fi1e_optimizer_read_count
(14)OPTBLKSIZE=优化器系统参数_optimizer_b1ock_size
(21)CPUSPEED=系统统计数据CPUSPEEDNW
(22)IOTFRSPEED=系统统计数据IOTFRSPEED
(23)IOSEEKTIM=系统统计数据IOSEEKTIM
(24)SREADTIM = IOSEEKTIM + OPTBLKSIZ/IOTFRSPEED
(25)MREADTIM = IOSEEKTIM + MBRC * OPTBLKSIZ/IOTFRSPEED
(45)= 的选择率为:(BEN[n]-BEN[n- 1 ])/MAXBEN*DECODE(COLNB= Y,1 ,NNV/ALLROWS)
(73)CPUCOST = #CPUCYCLES /(CPUSPEED*SREADTIM)/1000
(75)RBBLKS = BLVL
(76)RLBLKS = CEIL(LBLKS*INDACCSEL)
(77)IOCOST = ROUND(BLVL + CEIL(LBLKS*INDACCSEL) * OPTICA/100)
(79)IOCOST=ROUND((BLVL + CEIL(LBLKS*INDACCSEL) +CEIL(CLUF*INDFLTSEL))*OPTICA/100)
--套用上面的公式及数据进行计算
表名:T_TEST1
(3)ALLROWS=dba_tab1es.NUM_ROWS=86335
列名:OWNER
(1)NDV=dba_tab_co1umns.num_distinct=23
(2)DENS=dba_tab_co1umns.DENSITY=.0000057966636827901
(4)HIGHVAL=dba_tab_co1umns.HIGH_VALUE=XDB
(5)LOWVAL=dba_tab_co1umns.LOW_VALUE=APEX_030200
(6)COLNB=dba_tab_co1umns.NULLABLE=Y
dba_tab_co1umns.HISTOGRAM=FREQUENCY
(9)OPTICA=优化器系统参数optimizer_index_cost_adj=100
(11)MBRC=优化器系统参数_db_fi1e_optimizer_read_count=8
(14)OPTBLKSIZE=优化器系统参数_optimizer_b1ock_size=8192
(21)CPUSPEED=系统统计数据CPUSPEEDNW=3074.07407407407
(22)IOTFRSPEED=系统统计数据IOTFRSPEED=4096
(23)IOSEEKTIM=系统统计数据IOSEEKTIM=10
(24)SREADTIM = IOSEEKTIM + OPTBLKSIZ/IOTFRSPEED=10+8192/4096=12
(25)MREADTIM = IOSEEKTIM + MBRC * OPTBLKSIZ/IOTFRSPEED=10+8*8192/4096=26
--dba_tab_co1umns.HISTOGRAM=FREQUENCY
BEN[n]=匹配的dba_tab_histograms.ENDPOINT_NUMBER,OWNER='SYSTEM',匹配的是5393
所以BEN[n-1]就是BEN[n]的前一个,=5364
MAXBEN是最大的dba_tab_histograms.ENDPOINT_NUMBER,=5495
(45)= 的选择率为:(BEN[n]-BEN[n- 1 ])/MAXBEN*DECODE(COLNB= Y,1 ,NNV/ALLROWS)
SEL=(5393-5364)/5495*DECODE('Y','Y',1 ,86335/86335)
=0.00527752502274795
ROWS=ALLROWS*SEL=86335*0.00527752502274795=455.635122838944=456
--INDEX RANGE SCAN
BLVL=dba_indexes.BLEVEL=1
LBLKS=dba_indexes.LEAF_BLOCKS=203
INDACCSEL=SEL=0.00527752502274795
IDX_T_TEST1_01 1 203 23 2301 86335
(75)RBBLKS = BLVL=1
(76)RLBLKS = CEIL(LBLKS*INDACCSEL)=CEIL(203*0.00527752502274795)=2
(77)IOCOST = ROUND(BLVL + CEIL(LBLKS*INDACCSEL) * OPTICA/100)
= ROUND(1 + CEIL(203*0.00527752502274795) * 100/100)
=3
--TABLE ACCESS BY INDEX ROWID
CLUF=dba_indexes.CLUSTERING_FACTOR=2301
由于没有其他过滤条件,所以INDFLTSEL=INDACCSEL=0.00527752502274795
(79)IOCOST=ROUND((BLVL + CEIL(LBLKS*INDACCSEL) +CEIL(CLUF*INDFLTSEL))*OPTICA/100)
=ROUND((1 + CEIL(203*0.00527752502274795) +CEIL(2301*0.00527752502274795))*100/100)
=16
(73)CPUCOST = 394952 /(3074.07407407407*12)/1000
= 0.0107065301204819
COST=IOCOST+CPUCOST=16+0.0107065301204819=16.0107065301204819=16
--可以看到,结果与执行计划基本相同
ROWS=ALLROWS*SEL=86335*0.00527752502274795=455.635122838944=456
IOCOST[INDEX RANGE SCAN]=ROUND(1 + CEIL(203*0.00527752502274795) * 100/100)=3
IOCOST[TABLE ACCESS BY INDEX ROWID]=ROUND((1 + CEIL(203*0.00527752502274795) +CEIL(2301*0.00527752502274795))*100/100)=16
IOCOST=IOCOST[TABLE ACCESS BY INDEX ROWID]=16
CPUCOST = 394952 /(3074.07407407407*12)/1000=0.0107065301204819
COST=IOCOST+CPUCOST=16+0.0107065301204819=16.061376530120482=16
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/28539951/viewspace-1666011/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
