12条语句学会oracle cbo计算(四)
浏览数：15 /
时间：2015年06月12日
工作中,你可能会遇到统计数据正确,但就是不走你想要的执行计划的情况,最后一般通过hint或sql_profile解决.一条sql语句的性能主要依赖于好的物理结构,准确的系统统计数据,准确的对象统计数据,合理的查询优化器参数,合理的系统参数.这些因素也就是cost计算基本参数.因此产生的sql优化技术有很多,大概包含:改变访问结构,修改sql语句,加hint,改变执行环境,sql profile,stored outlines,sql plan baseline.其实无外乎就是通过调整sql适应环境或者通过调整环境适应sql.如果清楚Cost值的算法,就可以通过算法的对比,找到问题的真正原因,更有针对性的去解决问题.
全文主要参考Jonathan Lewis的<<基于成本的Oracle优化法则>>和黄玮(fuyuncat)的<<Oracle高性能SQL引擎剖析-SQL优化与调优机制详解>>,特别黄玮(fuyuncat)的这本,是非常值得去学习的.
准备用14篇来描述完,前2篇是统计数据,算法公式说明,后12篇用12条语句分别去套用说明.
本篇例子的特征是单表,全表扫描,条件值常量,无直方图,多条件,和上一篇差别是多条件.
--产生测试数据
drop table scott.t_test1 purge;
create table scott.t_test1 as select * from dba_objects;
begin
dbms_stats.gather_table_stats(‘scott‘,‘t_test1‘);
end;
--产生语句的执行计划
--这里我是在pl/sql developer,是因为不用象10053那么麻烦就可以产生想要的几个值用以对比.
explain plan for select * from scott.t_test1 where object_id>40 and owner=‘SCOTT‘;
commit;
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 3752 367696 343 32440361 344 5
TABLE ACCESS FULL T_TEST1 3752 367696 343 32440361 344 5
*/
--查询表的统计数据
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_number(low_value), 15, ‘ ‘) low_value,
rpad(utl_raw.cast_to_number(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 =‘OBJECT_ID‘;
/*
COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE NULLABLE NUM_NULLS AVG_COL_LEN DENSITY HISTOGRAM
OBJECT_ID 86335 2 87725 Y 0 5 .0000115827879770661 NONE
*/
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 .0434782608695652 NONE
*/
--查询优化器参数
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‘);
/*
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
_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
(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
(29)=的选择率为: GREATEST(1/NDV,DENS)*DECODE(COLNB= Y,1,NNV/ALLROWS)
(31)>,<,LIKE的选择率为: (BVAL- LOWVAL)/(HIGHVAL- LOWVAL)*DECODE(COLNB=Y,1,NNV/ALLROWS)
(66)A AND B 的选择率为:OPSEL[a] * OPSEL[b]
(72)IOCOST = (#BLKS/MBRC)*(IOSEEKTIM + MBRC*OPTBLKSIZE/IOTFRSPEED)/(IOSEEKTIM+OPTBLKSIZE/IOTFRSPEED)
(73)CPUCOST = #CPUCYCLES /(CPUSPEED*SREADTIM)
--套用上面的公式及数据进行计算
表名:T_TEST1
(3)ALLROWS=dba_tab1es.NUM_ROWS=86335
列名:T_TEST1.OBJECT_ID
(1)NDV=dba_tab_co1umns.num_distinct=86335
(2)DENS=dba_tab_co1umns.DENSITY=.0000115827879770661
(4)HIGHVAL=dba_tab_co1umns.HIGH_VALUE=87725
(5)LOWVAL=dba_tab_co1umns.LOW_VALUE=2
(6)COLNB=dba_tab_co1umns.NULLABLE=Y
列名:T_TEST1.OWNER
(1)NDV=dba_tab_co1umns.num_distinct=23
(2)DENS=dba_tab_co1umns.DENSITY=.0434782608695652
(4)HIGHVAL=dba_tab_co1umns.HIGH_VALUE=XDB
(5)LOWVAL=dba_tab_co1umns.LOW_VALUE=APEX_030200
(6)COLNB=dba_tab_co1umns.NULLABLE=Y
(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
(29)=的选择率为: GREATEST(1/NDV,DENS)*DECODE(COLNB= Y,1,NNV/ALLROWS)
OPSEL[owner]= GREATEST(1/23,.0434782608695652)*DECODE(‘Y‘,‘Y‘,1,86335/86335)
= 0.0434782608695652
(31)>,<,LIKE的选择率为: (BVAL- LOWVAL)/(HIGHVAL- LOWVAL)*DECODE(COLNB=Y,1,NNV/ALLROWS)
OPSEL[object_id]=(87725- 40)/(87725- 2)*DECODE(‘Y‘,‘Y‘,1,86335/86335)
= 0.999566818280269
(66)A AND B 的选择率为:OPSEL[a] * OPSEL[b]
SEL=OPSEL[object_id]*OPSEL[owner]
=0.0434782608695652*0.999566818280269
=0.0434594268817508
ROWS=ALLROWS*SEL=86335*0.0434594268817508=3752.06961983596=3752
(72)IOCOST = (#BLKS/MBRC)*(IOSEEKTIM + MBRC*OPTBLKSIZE/IOTFRSPEED)/(IOSEEKTIM+OPTBLKSIZE/IOTFRSPEED)
--#BLKS为表的块数
= (1261/8)*(10+8*8192/4096)/(10+8192/4096)
=341.520833333333
由于_optimizer_ceil_cost=true,_table_scan_cost_plus_one=true,所以微调为:
IOCOST=ceil(341.726274845226)+1=343
(73)CPUCOST = #CPUCYCLES /(CPUSPEED*SREADTIM)
= 32440361/(3074.07407407407*12)/1000
=0.879407376506025
COST=IOCOST+CPUCOST=343+0.879407376506025=343.879407376506025=344
--可以看到,结果与执行计划基本相同
ROWS=ALLROWS*SEL=86335*0.0434594268817508=3752.06961983596=3752
IOCOST=ceil(341.726274845226)+1=343
CPUCOST = 32440361/(3074.07407407407*12)/1000=0.879407376506025
COST=343+0.879407376506025=343.879407376506025=344
全文主要参考Jonathan Lewis的<<基于成本的Oracle优化法则>>和黄玮(fuyuncat)的<<Oracle高性能SQL引擎剖析-SQL优化与调优机制详解>>,特别黄玮(fuyuncat)的这本,是非常值得去学习的.
准备用14篇来描述完,前2篇是统计数据,算法公式说明,后12篇用12条语句分别去套用说明.
本篇例子的特征是单表,全表扫描,条件值常量,无直方图,多条件,和上一篇差别是多条件.
--产生测试数据
drop table scott.t_test1 purge;
create table scott.t_test1 as select * from dba_objects;
begin
dbms_stats.gather_table_stats(‘scott‘,‘t_test1‘);
end;
--产生语句的执行计划
--这里我是在pl/sql developer,是因为不用象10053那么麻烦就可以产生想要的几个值用以对比.
explain plan for select * from scott.t_test1 where object_id>40 and owner=‘SCOTT‘;
commit;
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 3752 367696 343 32440361 344 5
TABLE ACCESS FULL T_TEST1 3752 367696 343 32440361 344 5
*/
--查询表的统计数据
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_number(low_value), 15, ‘ ‘) low_value,
rpad(utl_raw.cast_to_number(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 =‘OBJECT_ID‘;
/*
COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE NULLABLE NUM_NULLS AVG_COL_LEN DENSITY HISTOGRAM
OBJECT_ID 86335 2 87725 Y 0 5 .0000115827879770661 NONE
*/
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 .0434782608695652 NONE
*/
--查询优化器参数
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‘);
/*
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
_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
(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
(29)=的选择率为: GREATEST(1/NDV,DENS)*DECODE(COLNB= Y,1,NNV/ALLROWS)
(31)>,<,LIKE的选择率为: (BVAL- LOWVAL)/(HIGHVAL- LOWVAL)*DECODE(COLNB=Y,1,NNV/ALLROWS)
(66)A AND B 的选择率为:OPSEL[a] * OPSEL[b]
(72)IOCOST = (#BLKS/MBRC)*(IOSEEKTIM + MBRC*OPTBLKSIZE/IOTFRSPEED)/(IOSEEKTIM+OPTBLKSIZE/IOTFRSPEED)
(73)CPUCOST = #CPUCYCLES /(CPUSPEED*SREADTIM)
--套用上面的公式及数据进行计算
表名:T_TEST1
(3)ALLROWS=dba_tab1es.NUM_ROWS=86335
列名:T_TEST1.OBJECT_ID
(1)NDV=dba_tab_co1umns.num_distinct=86335
(2)DENS=dba_tab_co1umns.DENSITY=.0000115827879770661
(4)HIGHVAL=dba_tab_co1umns.HIGH_VALUE=87725
(5)LOWVAL=dba_tab_co1umns.LOW_VALUE=2
(6)COLNB=dba_tab_co1umns.NULLABLE=Y
列名:T_TEST1.OWNER
(1)NDV=dba_tab_co1umns.num_distinct=23
(2)DENS=dba_tab_co1umns.DENSITY=.0434782608695652
(4)HIGHVAL=dba_tab_co1umns.HIGH_VALUE=XDB
(5)LOWVAL=dba_tab_co1umns.LOW_VALUE=APEX_030200
(6)COLNB=dba_tab_co1umns.NULLABLE=Y
(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
(29)=的选择率为: GREATEST(1/NDV,DENS)*DECODE(COLNB= Y,1,NNV/ALLROWS)
OPSEL[owner]= GREATEST(1/23,.0434782608695652)*DECODE(‘Y‘,‘Y‘,1,86335/86335)
= 0.0434782608695652
(31)>,<,LIKE的选择率为: (BVAL- LOWVAL)/(HIGHVAL- LOWVAL)*DECODE(COLNB=Y,1,NNV/ALLROWS)
OPSEL[object_id]=(87725- 40)/(87725- 2)*DECODE(‘Y‘,‘Y‘,1,86335/86335)
= 0.999566818280269
(66)A AND B 的选择率为:OPSEL[a] * OPSEL[b]
SEL=OPSEL[object_id]*OPSEL[owner]
=0.0434782608695652*0.999566818280269
=0.0434594268817508
ROWS=ALLROWS*SEL=86335*0.0434594268817508=3752.06961983596=3752
(72)IOCOST = (#BLKS/MBRC)*(IOSEEKTIM + MBRC*OPTBLKSIZE/IOTFRSPEED)/(IOSEEKTIM+OPTBLKSIZE/IOTFRSPEED)
--#BLKS为表的块数
= (1261/8)*(10+8*8192/4096)/(10+8192/4096)
=341.520833333333
由于_optimizer_ceil_cost=true,_table_scan_cost_plus_one=true,所以微调为:
IOCOST=ceil(341.726274845226)+1=343
(73)CPUCOST = #CPUCYCLES /(CPUSPEED*SREADTIM)
= 32440361/(3074.07407407407*12)/1000
=0.879407376506025
COST=IOCOST+CPUCOST=343+0.879407376506025=343.879407376506025=344
--可以看到,结果与执行计划基本相同
ROWS=ALLROWS*SEL=86335*0.0434594268817508=3752.06961983596=3752
IOCOST=ceil(341.726274845226)+1=343
CPUCOST = 32440361/(3074.07407407407*12)/1000=0.879407376506025
COST=343+0.879407376506025=343.879407376506025=344
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
