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的<>和黄玮(fuyuncat)的<>,特别黄玮(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>:1;

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            4317    423066    343    41010476    344    5

TABLE ACCESS    FULL    T_TEST1    4317    423066    343    41010476    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                  87726         Y           0           5         .0000115827879770661    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

(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

(39)>,

(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

列名:OBJECT_ID

(1)NDV=dba_tab_co1umns.num_distinct=86335

(2)DENS=dba_tab_co1umns.DENSITY=.0000115827879770661

(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

(39)>,

SEL=GREATEST(1/20,1/86335,.0000115827879770661)*DECODE('Y','Y',1,86335/86335)

=0.05

ROWS=ALLROWS*SEL=86335*0.05=4316.75=4317

(72)IOCOST = (#BLKS/MBRC)*(IOSEEKTIM + MBRC*OPTBLKSIZE/IOTFRSPEED)/(IOSEEKTIM+OPTBLKSIZE/IOTFRSPEED)

=(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)

= 41010476/(3074.07407407407*12)/1000

= 1.11172977108434

COST=IOCOST+CPUCOST=343+1.11172977108434=344.11172977108434=344

--可以看到,结果与执行计划基本相同

ROWS=ALLROWS*SEL=86335*0.05=4316.75=4317

IOCOST=ceil(341.726274845226)+1=343

CPUCOST = 1010476/(3074.07407407407*12)/1000=1.11172977108434

COST=343+1.11172977108434=344.11172977108434=344