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條語句分別去套用說明.
本篇是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:結束點值
全文主要參考Jonathan Lewis的<>和黃瑋(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:結束點值
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28539951/viewspace-1654660/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 12條語句學會oracle cbo計算(七)Oracle
- 12條語句學會oracle cbo計算(五)Oracle
- 12條語句學會oracle cbo計算(四)Oracle
- 12條語句學會oracle cbo計算(三)Oracle
- 12條語句學會oracle cbo計算(二)Oracle
- 12條語句學會oracle cbo計算(十四)Oracle
- 12條語句學會oracle cbo計算(十三)Oracle
- 12條語句學會oracle cbo計算(十一)Oracle
- 12條語句學會oracle cbo計算(十)Oracle
- 12條語句學會oracle cbo計算(九)Oracle
- 12條語句學會oracle cbo計算(六)Oracle
- 12條語句學會oracle cbo計算(十二)Oracle
- 【cbo計算公式】CBO基本概念(一)公式
- 實現四則運算的一條sql語句SQL
- oracle中的條件語句Oracle
- Oracle 使用一條insert語句完成多表插入Oracle
- 學習Rust 條件語句Rust
- oracle基於cbo成本計算方式說明Oracle
- CBO成本計算初探
- 草根學Python（五） 條件語句和迴圈語句Python
- 從一條語句說起 談談中國的計算機程式教育問題 (轉)計算機
- 使用CREATETABLE語句宣告一個計算列(轉)
- Java 數學運算與條件語句全解析Java
- 一條SQL語句的書寫SQL
- 一條很 巧妙的 SQL 語句SQL
- 一條sql語句的優化SQL優化
- 一條SQL語句的旅行之路SQL
- Python學習筆記3（條件語句+迴圈語句）Python筆記
- Python學習筆記（三）——條件語句、迴圈語句Python筆記
- 用一條mysql語句插入多條資料MySql
- 一條簡單SQL語句的構成及語句解析SQL
- 一條SQL語句的執行計劃變化探究SQL
- [20120104]穩定一條sql語句的執行計劃.txtSQL
- shell程式設計之條件語句程式設計
- [20151203]一條sql語句的優化.txtSQL優化
- [20120319]一條sql語句的優化.txtSQL優化
- Go 條件語句 - Go 學習記錄Go
- 一條更新語句的執行流程