12条语句学会oracle cbo计算(十三)

lubh

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 /*+parallel(a,2)*/* from scott.t_test1 a;
　　
　　SELECT lpad(' ', 2 * (LEVEL - 1)) || operation operation,
　　       options,
　　       object_name,
　　       cardinality,
　　       bytes,
　　       io_cost,
　　       cpu_cost,
　　       cost,
　　       time
　　  FROM plan_table
　　 START WITH>
　　CONNECT BY PRIOR>
　　/*
　　OPERATION    OPTIONS    OBJECT_NAME    CARDINALITY    BYTES    IO_COST    CPU_COST    COST    TIME
　　SELECT STATEMENT            86335    8460830    191    20624472    191    3
　　  PX COORDINATOR                                
　　    PX SEND    QC (RANDOM)    :TQ10000    86335    8460830    191    20624472    191    3
　　      PX BLOCK    ITERATOR        86335    8460830    191    20624472    191    3
　　        TABLE ACCESS    FULL    T_TEST1    86335    8460830    191    20624472    191    3
　　*/
　　
　　--查询表的统计数据
　　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         .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                                    
　　*/
　　
　　--需要应用第二篇中的公式:
　　(3)ALLROWS=dba_tab1es.NUM_ROWS
　　(11)MBRC=优化器系统参数_db_fi1e_optimizer_read_count=8
　　(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
　　(72)IOCOST = (#BLKS/MBRC)*(IOSEEKTIM + MBRC*OPTBLKSIZE/IOTFRSPEED)/(IOSEEKTIM+OPTBLKSIZE/IOTFRSPEED)
　　(73)CPUCOST =CPUCOST = #CPUCYCLES /(CPUSPEED*SREADTIM)/1000           
　　(104)IOCOST[Paralle1] = IOCOST[Serial]*10/9/DOP
　　
　　--套用上面的公式及数据进行计算
　　表名:T_TEST1
　　(3)ALLROWS=dba_tab1es.NUM_ROWS=86335
　　(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
　　(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              
　　
　　(104)IOCOST[Paralle1] = IOCOST[Serial]*10/9/DOP
　　                      =343 *10/9/2
　　                      =190.555555555556
　　(73)CPUCOST =CPUCOST = #CPUCYCLES /(CPUSPEED*SREADTIM)/1000
　　            = 20624472 /(3074.07407407407*12)/1000
　　            =0.559097132530121
　　COST=IOCOST[Paralle1]+CPUCOST=190.555555555556+0.559097132530121=191
　　
　　--可以看到,结果与执行计划基本相同
　　ROWS=86335
　　IOCOST[Serial]=343
　　IOCOST[Paralle1]=190.555555555556
　　CPUCOST = 0.559097132530121
　　COST=IOCOST[Paralle1]+CPUCOST=191