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;
drop table scott.t_test2 purge;
create table scott.t_test1 as select * from dba_objects;

create table scott.t_test2 as select * from dba_objects;

begin
dbms_stats.gather_table_stats('scott','t_test1');
end;
begin
dbms_stats.gather_table_stats('scott','t_test2');
end;

--產生語句的執行計劃
--這裡我是在pl/sql developer,是因為不用象10053那麼麻煩就可以產生想要的幾個值用以對比.
explain plan for select /*+use_nl(a,b)*/* from scott.t_test1 a,scott.t_test2 b where a.object_id=b.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           86334   16921464   29485204   3980404884066   29593107   355118
NESTED LOOPS           86334   16921464   29485204   3980404884066   29593107   355118
    TABLE ACCESS   FULL   T_TEST1   86334   8460732   343   46103756   344   5
    TABLE ACCESS   FULL   T_TEST2   1   98   342   46104186   343   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 in ('T_TEST1','T_TEST2');
/*
TABLE_NAME   NUM_ROWS   BLOCKS   AVG_ROW_LEN
T_TEST2     86335        1261         98
T_TEST1     86334        1261         98
*/

--查詢列的統計資料
select rpad(table_name, 12, ' ') table_name,
       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 in ('T_TEST1','T_TEST2')
   and column_name = 'OBJECT_ID';
/*
TABLE_NAME   COLUMN_NAME   NUM_DISTINCT   LOW_VALUE   HIGH_VALUE   NULLABLE   NUM_NULLS   AVG_COL_LEN   DENSITY   HISTOGRAM
T_TEST1        OBJECT_ID     86334     2                 87737        Y          0          5        .0000115829221395974   NONE
T_TEST2        OBJECT_ID     86335     2                 87738        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');
/*
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
(69)JOINSEL= LEASTEST(COLSEL[Outer], COLSEL[Inner])
(70)CARD[Join] = CARD[Outer]*CARD[Inner]*JOINSEL
(72)IOCOST = (#BLKS/MBRC)*(IOSEEKTIM + MBRC*OPTBLKSIZE/IOTFRSPEED)/(IOSEEKTIM+OPTBLKSIZE/IOTFRSPEED)
(73)CPUCOST = #CPUCYCLES /(CPUSPEED*SREADTIM)/1000
(90)IOCOST[NLJ] = IOCOST[Outer] + CARD[Outer]* IOCOST[Inner]

--套用上面的公式及資料進行計算
表名:T_TEST1
(3)ALLROWS=dba_tab1es.NUM_ROWS=86334
列名:OBJECT_ID
(1)NDV=dba_tab_co1umns.num_distinct=86334
(2)DENS=dba_tab_co1umns.DENSITY=.0000115829221395974
(6)COLNB=dba_tab_co1umns.NULLABLE=Y

表名:T_TEST2
(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
--TABLE ACCESS   FULL
因為兩個全表掃描都沒有過濾條件,所以
ROWS[Outer]=ALLROWS=86334
ROWS[Inner]=ALLROWS=86335
(72)IOCOST = (#BLKS/MBRC)*(IOSEEKTIM + MBRC*OPTBLKSIZE/IOTFRSPEED)/(IOSEEKTIM+OPTBLKSIZE/IOTFRSPEED)
IOCOST[Outer] = (#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[Outer]=ceil(341.520833333333)+1=343
IOCOST[Inner] = (#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[Inner]=ceil(341.520833333333)+1=343

--NESTED LOOPS
(69)JOINSEL= LEASTEST(COLSEL[Outer], COLSEL[Inner])
COLSEL[Outer]=1/NDV=1/86334
COLSEL[Inner]=1/NDV=1/86335
JOINSEL=LEAST(1/86334,1/86335)=1/86335
(70)CARD[Join] = CARD[Outer]*CARD[Inner]*JOINSEL
CARD[Outer]=ROWS[Outer]
CARD[Inner]=ROWS[Inner]
CARD[Join]= CARD[Outer]*CARD[Inner]*JOINSEL
          =86334*86335*=1/86335
          =86334
(90)IOCOST[NLJ] = IOCOST[Outer] + CARD[Outer]* IOCOST[Inner]
                =343+86334*343
                =29612905
(73)CPUCOST =3980404884066 /(4096*12)/1000
            =80981.5446790771
COST=IOCOST+CPUCOST=IOCOST[NLJ]+CPUCOST=29612905+80981.5446790771=29693886.5446791=29693887

--可以看到,結果與執行計劃基本相同
ROWS=CARD[Join]=86334
IOCOST[Outer]=343
IOCOST[Inner]=343
IOCOST[NLJ]=29612905
CPUCOST = 80981.5446790771
COST=IOCOST[NLJ]+CPUCOST=29693887

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

相關文章