        工作中,你可能會遇到統計資料正確,但就是不走你想要的執行計劃的情況,最後一般透過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條語句分別去套用說明.
本篇是cost計算中要用到的統計資料說明:

1.系統統計資料
a.查詢方法:
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
*/
b.說明:
當CPUSPEED或MREADTIM或SREADTIM為空時,為無負載模式(NOWORKLOAD),否則為負載模式(WORKLOAD).
CPUSPEEDNW:CPU在無負栽模式下的處理速度,即每秒鐘可以完成的機器指令數(或者說轉數,Cycles),單位為百萬次,10g中預設值為1,llg中預設值為100
IOSEEKTIM:IO定址時間,即10定址銪要的時間,單位為毫秒,預設值為10
IOTFRSPEED:IO傳輸速度,即每亳秒傳輸的位元組數,預設值為4096
負載模式(WORKLOAD)用的到引數:
CPUSPEED:CPU負載模式下的處理速度,即每秒鐘可以完成的機器指令數,單位為百萬次
MBRC:系統設定多資料塊讀的資料塊數
SREADTIM:單資料塊讀的平均讀取時間,單位為毫秒
MREADTIM:多資料塊讀的平均讀取時間,單位為毫秒
MAXTHR:系統的最大吞吐量,單位為每秒位元組數
SLAVETHR:承個並行服務程式的鋃大吞吐a,單位為毎秒位元組數

2.最佳化器系統引數
a.查詢方法:
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
*/
b.說明:
db_file_multiblock_read_count:決定了最佳化器進行代價估算時和語句實際執行過程中採用多資料塊讀的最大資料塊數量
_db_file_optimizer_read_count:是最佳化器做代價估算時的參考引數
sort_area_size:手動管理策略下,排序區的大小及最大大小
sort_area_retained_size:手動管理策略下,排序區的保留大小
_optimizer_cost_model:當其值為FIXED時,CPUSPEED或CPUSPEEDNW不會採用的系統統計資料,而是採用固定值100000
_optimizer_cache_stats:最佳化器做代價估算時將快取統計資料考慮進去
_table_scan_cost_plus_one:對計算進行微調,其含義是在全表掃描代價估算結果上再加一,預設值為TRUE
workarea_size_policy:工作區(包括排序區和雜湊區)大小的管理方式,分auto和mannul,預設auto自動管理
_smm_auto_min_io_size:工作區一次直接讀寫到臨時磁碟的最小大小,單位為KB
_smm_auto_max_io_size:工作區一次直接讀寫到臨時磁碟的最大大小,單位為KB
_smm_min_size:自動管理策略下,排序區的大小,單位為KB
_smm_max_size:自動管理策略下,排序區的最大大小,單位為KB
_smm_px_max_size:並行模式下總的工作區大小,並行服務程式使用的總的工作區大小不能超過該值
_optimizer_percent_parallel:控制最佳化器在估算時，採用並行度代價估算的比例
_optimizer_block_size:最佳化器參考的資料塊大小
_optimizer_ceil_cost:對計算進行微調,其含義是在全表掃描代價估算結果ceil

建一張表為下面說明與物件相關的統計資料:
create table scott.t_test1 as select * from dba_objects;
create index scott.idx_t_test1_01 on scott.t_test1(owner);
begin
dbms_stats.gather_table_stats('scott','t_test1',method_opt => 'for columns owner size 100');
end;

3.表統計資料
a.查詢方法:
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     690648       10125        98
*/
b.說明:
TABLE_NAME:表名
NUM_ROWS:表(或者分割槽、子分割槽)中的資料記錄數
EMPTY_BLOCKS:表(或者分割槽、子分割槽)中平均空閒資料塊數
AVG_ROW_LEN:表(或者分割槽、子分割槽)的資料記錄平均長度,單位為位元組

4.欄位統計資料
a.查詢方法:
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, 15, ' ') density,
       histogram
from dba_tab_columns
where owner = 'SCOTT'
   and table_name = 'T_TEST1'
   and column_name = 'OWNER';
--low_value,high_value是根據不同的欄位型別透過utl_raw.cast_to_xxx轉換後的.
/*
COLUMN_NAME   NUM_DISTINCT   LOW_VALUE   HIGH_VALUE   NULLABLE   NUM_NULLS   AVG_COL_LEN   DENSITY   HISTOGRAM
OWNER          23         APEX_030200       XDB          Y          0          6        .00000566390003   FREQUENCY
*/
b.說明:
COLUMN_NAME:欄位名
NUM_DISTINCT:欄位中的唯一值
LOW_VALUE:欄位的最小數值
HIGH_VALUE:欄位的最大數值
NULLABLE:可否為空
NUM_NULLS:欄位中的空值數
DENSITY:欄位的密度(即平均每個唯一值在該欄位中的重複資料數)
HISTOGRAM:直方圖型別

5.索引統計資料
a.查詢方法:
select index_name,
       rpad(blevel, 8, ' ') blevel,
       rpad(leaf_blocks, 8, ' ') leaf_blocks,
       rpad(distinct_keys, 8, ' ') distinct_keys,
       rpad(clustering_factor, 15, ' ') clustering_factor,
       num_rows
from dba_indexes
where owner = 'SCOTT'
   and table_name = 'T_TEST1';
/*
INDEX_NAME   BLEVEL   LEAF_BLOCKS   DISTINCT_KEYS   CLUSTERING_FACTOR   NUM_ROWS
IDX_T_TEST1_01   1          203        23         2302              86332
*/
b.說明:
index_name:索引名
BLEVEL:索引(或者本地分割槽索引)樹的支節點層數
LEAF_BLOCKS:索引(或者本地分割槽索引)的葉子資料塊數
DISTINCT_EYS:索引(或者本地分割槽索引)的唯一鍵值數
CLUSTERING_FACTOR:索引(或者本地分割槽索引)的族集因子(簇集因子反映了每個鍵值所指向的表的資料塊數直接的連續性,數值越低,這些資料塊之間越連續;反之,數值越低,說明這些資料塊分佈越分散)
NUM_ROWS:被索引的資料記錄數

6.直方圖統計資料
a.查詢方法:
select column_name,
       endpoint_number,
       utl_raw.cast_to_varchar2(substr(lpad(to_char(endpoint_value,
                                                    'fmxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'),
                                            30,
                                            '0'),
                                       1,
                                       12)) char_value,
       endpoint_value
from dba_tab_histograms
where owner = 'SCOTT' and table_name = 'T_TEST1' and column_name = 'OWNER'
order by endpoint_number;
--endpoint_value是根據不同的欄位型別透過utl_raw.cast_to_xxx轉換後的.
/*
COLUMN_NAME   ENDPOINT_NUMBER   CHAR_VALUE   ENDPOINT_VALUE
OWNER   165   APEX_0   3.39127382666026E35
OWNER   192   CTXSYS   3.49594609802084E35
OWNER   197   DBSNMP   3.54421425584994E35
OWNER   220   EXFSYS   3.60058907050643E35
OWNER   221   FLOWS_   3.65008529278141E35
OWNER   347   MDSYR?   4.01192665542225E35
OWNER   389   OLAPSY   4.11738089644256E35
OWNER   390   ORACLE   4.11859780070016E35
OWNER   408   ORDDAT   4.1186001805076E35
OWNER   410   ORDPLU   4.11860021777884E35
OWNER   575   ORDSYS   4.11860022722044E35
OWNER   576   OWBSYR   4.11961276313737E35
OWNER   577   OWBSYS   4.11961276313739E35
OWNER   2806   PUBLIB   4.17113006167251E35
OWNER   2807   SCOTT   4.32325845824965E35
OWNER   5268   SYR???   4.32772349650604E35
OWNER   5499   SYSMAN   4.32772373559899E35
OWNER   5530   SYSTEM   4.32772375731125E35
OWNER   5545   WMSYS   4.53298175814007E35
OWNER   5624   XDA???   4.58306556462839E35
*/
b.說明:
COLUMN_NAME:欄位名
ENDPOINT_NUMBER:結束點數
CHAR_VALUE:轉換後的結束點值
ENDPOINT_VALUE:結束點值

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

相關文章