12條語句學會oracle cbo計算(六)

456definy發表於2015-05-22
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 owner='SYSTEM' order by object_id;

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            3754    367892    430    67549918    432    6
  SORT    ORDER BY        3754    367892    430    67549918    432    6
    TABLE ACCESS    FULL    T_TEST1    3754    367892    343    27298256    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_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 sum(avg_col_len),count(1)
from dba_tab_col_statistics
where owner='SCOTT' and table_name = 'T_TEST1'
/*
SUM(AVG_COL_LEN)    COUNT(1)
100    15
*/

--查詢最佳化器引數
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
(8)ARL=dba_tables.AVG_ROW_LEN
(11)MBRC=最佳化器系統引數_db_fi1e_optimizer_read_count
(12)SAMINIO=最佳化器系統引數_smm_auto_min_io_size*1024
(14)OPTBLKSIZE=最佳化器系統引數_optimizer_b1ock_size
(16)MBDRC=SAMINIO/OPTBLKSIZE
(21)CPUSPEED=系統統計資料CPUSPEEDNW
(22)IOTFRSPEED=系統統計資料IOTFRSPEED
(23)IOSEEKTIM=系統統計資料IOSEEKTIM
(24)SREADTIM = IOSEEKTIM + OPTBLKSIZ/IOTFRSPEED
(26)SAMSIZE=最佳化器系統引數_smm_max_size*1024
(27)SASIZE=最佳化器系統引數_smm_min_size*1024
(29)=的選擇率為: GREATEST(1/NDV,DENS)*DECODE(COLNB= Y,1,NNV/ALLROWS)
(72)IOCOST = (#BLKS/MBRC)*(IOSEEKTIM + MBRC*OPTBLKSIZE/IOTFRSPEED)/(IOSEEKTIM+OPTBLKSIZE/IOTFRSPEED)
(73)CPUCOST = #CPUCYCLES /(CPUSPEED*SREADTIM)/1000
(80)SDSIZE = SROWNUM*SROWSIZE
(81)RROWSIZE = LEAST(ARL, SUM(ACL1~n))
(82)SROWSIZE = RROWSIZE + 10 + CEIL(RROWSIZE/10)
(83)如果SDSIZE > SASIZE,說明排序需要寫入磁碟,否則IOCOST[Sort]=0
(84)SORTWIDTH = FLOOR((SAMSIZE-((60*SAMSIZE/1024/320-40*(SAMSIZE/1024/320-1))+LOG(2,MBDRC)*80)*1024)/((SAMINIO+OPTBLKSIZE)*2.5))
(85)INTRUNS = GREATEST(CEIL(SDSIZE/SAMSIZE), 2)
(86)MERGES = CEIL(LOG(SORTWIDTH,INIRUNS))
(87)SORTBLKS = CEIL(SDSIZE/(OPTBLKSIZE-24))
(88)PASSIO = CEIL(SORTBLKS*(MBDRC*MREADTIM/SREADTIM)/(MBDRC+1 )/(MBRC-1 ))*2 + CEIL(S0RTBLKS*(MBRC-1-MBDRC)/(MBDRC+1)/(MBRC-1))*2
(89)IOCOST[Sort]= SORTBLKS + (PASSIO * MERGES))

--套用上面的公式及資料進行計算
表名:T_TEST1
(3)ALLROWS=dba_tab1es.NUM_ROWS=86335
條件列名:OWNER
(1)NDV=dba_tab_co1umns.num_distinct=23
(2)DENS=dba_tab_co1umns.DENSITY=.0434782608695652
(6)COLNB=dba_tab_co1umns.NULLABLE=Y
(8)ARL=dba_tables.AVG_ROW_LEN=98

(11)MBRC=最佳化器系統引數_db_fi1e_optimizer_read_count=8
(12)SAMINIO=最佳化器系統引數_smm_auto_min_io_size*1024=56*1024=57344
(14)OPTBLKSIZE=最佳化器系統引數_optimizer_b1ock_size=8192
(16)MBDRC=SAMINIO/OPTBLKSIZE=57344/8192=7
(21)CPUSPEED=系統統計資料CPUSPEEDNW=3074.07407407407   
(22)IOTFRSPEED=系統統計資料IOTFRSPEED=4096
(23)IOSEEKTIM=系統統計資料IOSEEKTIM=10
(24)SREADTIM = IOSEEKTIM + OPTBLKSIZ/IOTFRSPEED=10+8192/4096=12
(26)SAMSIZE=最佳化器系統引數_smm_max_size*1024=58720256
(27)SASIZE=最佳化器系統引數_smm_min_size*1024=292864
(29)=的選擇率為: GREATHST(1/NDV,DENS)*DECODE(COLNB,Y,1,NNV/ALLROWS)
            SEL=GREATEST(1/23,.0434782608695652)*DECODE('Y',Y,1,86335/86335)
               =0.0434782608695652
ROWS=ALLROWS*SEL=86335*0.0434782608695652=3753.69565217391=3754

    --TABLE ACCESS    FULL
(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.520833333333)+1=343
(73)CPUCOST = #CPUCYCLES /(CPUSPEED*SREADTIM)/1000
            =27298256/(3074.07407407407*12)/1000
            =0.740012963855423
COST=IOCOST+CPUCOST=343+0.740012963855423=343.740012963855423=344

    --SORT    ORDER BY
(81)RROWSIZE = LEAST(ARL, SUM(ACL1~n))
             =LEAST(98, 100)
             =98
(82)SROWSIZE = RROWSIZE + 10 + CEIL(RROWSIZE/10)
             =98+10+CEIL(98/10)
             =118
SROWNUM=ROWS
(80)SDSIZE = SROWNUM*SROWSIZE
           =3754*118
           =442972
(83)如果SDSIZE > SASIZE,說明排序需要寫入磁碟,否則IOCOST[Sort]=0
SDSIZE(442972)>SASIZE(292864)
(84)SORTWIDTH = FLOOR((SAMSIZE-((60*SAMSIZE/1024/320-40*(SAMSIZE/1024/320-1))+LOG(2,MBDRC)*80)*1024)/((SAMINIO+OPTBLKSIZE)*2.5))
              =FLOOR((58720256-((60*58720256/1024/320-40*(58720256/1024/320-1))+LOG(2,7)*80)*1024)/((57344+8192)*2.5))
              =334
(85)INTRUNS = GREATEST(CEIL(SDSIZE/SAMSIZE), 2)
            =GREATEST(CEIL(442972/58720256), 2)
            =2
(86)MERGES = CEIL(LOG(SORTWIDTH,INIRUNS))
           =CEIL(LOG(334,2))
           =1
(87)SORTBLKS = CEIL(SDSIZE/(OPTBLKSIZE-24))
             =CEIL(442972/(8192-24))
             =55
(88)PASSIO = CEIL(SORTBLKS*(MBDRC*MREADTIM/SREADTIM)/(MBDRC+1 )/(MBRC-1 ))*2 + CEIL(S0RTBLKS*(MBRC-1-MBDRC)/(MBDRC+1)/(MBRC-1))*2
           =CEIL(55*(7*26/12)/(7+1 )/(8-1 ))*2 + CEIL(55*(8-1-7)/(7+1)/(8-1))*2
           =30
(89)IOCOST[Sort]= SORTBLKS + (PASSIO * MERGES)
                = 55 + (30 * 1)
                =85
IOCOST=IOCOST[TABLE ACCESS    FULL]+IOCOST[Sort]=343+85=428
CPUCOST=#CPUCYCLES /(CPUSPEED*SREADTIM)/1000
       = 67549918/(3074.07407407407*12)/1000
       =1.83117247590362
COST= IOCOST+CPUCOST=428+1.83117247590362=429.83117247590362=430

--可以看到,結果與執行計劃基本相同.
ROWS=ALLROWS*SEL=86335*0.0434782608695652=3753.69565217391=3754
IOCOST[TABLE ACCESS    FULL]=ceil(341.520833333333)+1=343
IOCOST[Sort]=55 + (30 * 1)=85
IOCOST=IOCOST[TABLE ACCESS    FULL]+IOCOST[Sort]=343+85=428
CPUCOST = 67549918/(3074.07407407407*12)/1000 =1.83117247590362
COST=IOCOST+CPUCOST=428+1.83117247590362=429.83117247590362=430

--從排序成本的計算,可以看出,排序主要所消耗的成本在IO成本和決定排序成本的幾個關鍵引數.

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/30255758/viewspace-1666852/,如需轉載,請註明出處,否則將追究法律責任。

相關文章