12條語句學會oracle cbo計算(九)
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;
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;
--產生語句的執行計劃
--這裡我是在pl/sql developer,是因為不用象10053那麼麻煩就可以產生想要的幾個值用以對比.
explain plan for select * from scott.t_test1 where owner='SYSTEM';
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 456 44688 15 394952 15 1
TABLE ACCESS BY INDEX ROWID T_TEST1 456 44688 15 394952 15 1
INDEX RANGE SCAN IDX_T_TEST1_01 456 2 106293 2 1
*/
--查詢表的統計資料
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 .0000057966636827901 FREQUENCY
*/
--查詢索引的統計資料
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 2301 86335
*/
--查詢直方圖資料
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;
/*
COLUMN_NAME ENDPOINT_NUMBER CHAR_VALUE ENDPOINT_VALUE
OWNER 174 APEX_0 3.39127382666026E35
OWNER 204 CTXSYS 3.49594609802084E35
OWNER 206 DBSNMP 3.54421425584994E35
OWNER 221 EXFSYS 3.60058907050643E35
OWNER 223 FLOWS_ 3.65008529278141E35
OWNER 343 MDSYR? 4.01192665542225E35
OWNER 380 OLAPSY 4.11738089644256E35
OWNER 384 ORACLE 4.11859780070016E35
OWNER 395 ORDDAT 4.1186001805076E35
OWNER 396 ORDPLU 4.11860021777884E35
OWNER 556 ORDSYS 4.11860022722044E35
OWNER 557 OWBSYS 4.11961276313739E35
OWNER 2653 PUBLIB 4.17113006167251E35
OWNER 2654 SCOTT 4.32325845824965E35
OWNER 5134 SYR??? 4.32772349650604E35
OWNER 5364 SYSMAN 4.32772373559899E35
OWNER 5393 SYSTEM 4.32772375731125E35
OWNER 5414 WMSYS 4.53298175814007E35
OWNER 5495 XDA??? 4.58306556462839E35
*/
--查詢最佳化器引數
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','optimizer_index_cost_adj');
/*
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
optimizer_index_cost_adj 100 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
(4)HIGHVAL=dba_tab_co1umns.HIGH_VALUE
(5)LOWVAL=dba_tab_co1umns.LOW_VALUE
(6)COLNB=dba_tab_co1umns.NULLABLE
(9)OPTICA=最佳化器系統引數optimizer_index_cost_adj
(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
(45)= 的選擇率為:(BEN[n]-BEN[n- 1 ])/MAXBEN*DECODE(COLNB= Y,1 ,NNV/ALLROWS)
(73)CPUCOST = #CPUCYCLES /(CPUSPEED*SREADTIM)/1000
(75)RBBLKS = BLVL
(76)RLBLKS = CEIL(LBLKS*INDACCSEL)
(77)IOCOST = ROUND(BLVL + CEIL(LBLKS*INDACCSEL) * OPTICA/100)
(79)IOCOST=ROUND((BLVL + CEIL(LBLKS*INDACCSEL) +CEIL(CLUF*INDFLTSEL))*OPTICA/100)
--套用上面的公式及資料進行計算
表名:T_TEST1
(3)ALLROWS=dba_tab1es.NUM_ROWS=86335
列名:OWNER
(1)NDV=dba_tab_co1umns.num_distinct=23
(2)DENS=dba_tab_co1umns.DENSITY=.0000057966636827901
(4)HIGHVAL=dba_tab_co1umns.HIGH_VALUE=XDB
(5)LOWVAL=dba_tab_co1umns.LOW_VALUE=APEX_030200
(6)COLNB=dba_tab_co1umns.NULLABLE=Y
dba_tab_co1umns.HISTOGRAM=FREQUENCY
(9)OPTICA=最佳化器系統引數optimizer_index_cost_adj=100
(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
--dba_tab_co1umns.HISTOGRAM=FREQUENCY
BEN[n]=匹配的dba_tab_histograms.ENDPOINT_NUMBER,OWNER='SYSTEM',匹配的是5393
所以BEN[n-1]就是BEN[n]的前一個,=5364
MAXBEN是最大的dba_tab_histograms.ENDPOINT_NUMBER,=5495
(45)= 的選擇率為:(BEN[n]-BEN[n- 1 ])/MAXBEN*DECODE(COLNB= Y,1 ,NNV/ALLROWS)
SEL=(5393-5364)/5495*DECODE('Y','Y',1 ,86335/86335)
=0.00527752502274795
ROWS=ALLROWS*SEL=86335*0.00527752502274795=455.635122838944=456
--INDEX RANGE SCAN
BLVL=dba_indexes.BLEVEL=1
LBLKS=dba_indexes.LEAF_BLOCKS=203
INDACCSEL=SEL=0.00527752502274795
IDX_T_TEST1_01 1 203 23 2301 86335
(75)RBBLKS = BLVL=1
(76)RLBLKS = CEIL(LBLKS*INDACCSEL)=CEIL(203*0.00527752502274795)=2
(77)IOCOST = ROUND(BLVL + CEIL(LBLKS*INDACCSEL) * OPTICA/100)
= ROUND(1 + CEIL(203*0.00527752502274795) * 100/100)
=3
--TABLE ACCESS BY INDEX ROWID
CLUF=dba_indexes.CLUSTERING_FACTOR=2301
由於沒有其他過濾條件,所以INDFLTSEL=INDACCSEL=0.00527752502274795
(79)IOCOST=ROUND((BLVL + CEIL(LBLKS*INDACCSEL) +CEIL(CLUF*INDFLTSEL))*OPTICA/100)
=ROUND((1 + CEIL(203*0.00527752502274795) +CEIL(2301*0.00527752502274795))*100/100)
=16
(73)CPUCOST = 394952 /(3074.07407407407*12)/1000
= 0.0107065301204819
COST=IOCOST+CPUCOST=16+0.0107065301204819=16.0107065301204819=16
--可以看到,結果與執行計劃基本相同
ROWS=ALLROWS*SEL=86335*0.00527752502274795=455.635122838944=456
IOCOST[INDEX RANGE SCAN]=ROUND(1 + CEIL(203*0.00527752502274795) * 100/100)=3
IOCOST[TABLE ACCESS BY INDEX ROWID]=ROUND((1 + CEIL(203*0.00527752502274795) +CEIL(2301*0.00527752502274795))*100/100)=16
IOCOST=IOCOST[TABLE ACCESS BY INDEX ROWID]=16
CPUCOST = 394952 /(3074.07407407407*12)/1000=0.0107065301204819
COST=IOCOST+CPUCOST=16+0.0107065301204819=16.061376530120482=16
工作中,你可能會遇到統計資料正確,但就是不走你想要的執行計劃的情況,最後一般透過hint或sql_profile解決.一條sql語句的效能主要依賴於好的物理結構,準確的系統統計資料,準確的物件統計資料,合理的查詢最佳化器引數,合理的系統引數.這些因素也就是cost計算基本引數.因此產生的sql最佳化技術有很多,大概包含:改變訪問結構,修改sql語句,加hint,改變執行環境,sql profile,stored outlines,sql plan baseline.其實無外乎就是透過調整sql適應環境或者透過調整環境適應sql.如果清楚Cost值的演算法,就可以透過演算法的對比,找到問題的真正原因,更有針對性的去解決問題.
全文主要參考Jonathan Lewis的<>和黃瑋(fuyuncat)的<
準備用14篇來描述完,前2篇是統計資料,演算法公式說明,後12篇用12條語句分別去套用說明.
本篇例子的特徵是單表,索引範圍掃描,條件值常量,直方圖,單條件,排序,和上一篇差別是有直方圖
--產生測試資料
drop table scott.t_test1 purge;
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;
--產生語句的執行計劃
--這裡我是在pl/sql developer,是因為不用象10053那麼麻煩就可以產生想要的幾個值用以對比.
explain plan for select * from scott.t_test1 where owner='SYSTEM';
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 456 44688 15 394952 15 1
TABLE ACCESS BY INDEX ROWID T_TEST1 456 44688 15 394952 15 1
INDEX RANGE SCAN IDX_T_TEST1_01 456 2 106293 2 1
*/
--查詢表的統計資料
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 .0000057966636827901 FREQUENCY
*/
--查詢索引的統計資料
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 2301 86335
*/
--查詢直方圖資料
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;
/*
COLUMN_NAME ENDPOINT_NUMBER CHAR_VALUE ENDPOINT_VALUE
OWNER 174 APEX_0 3.39127382666026E35
OWNER 204 CTXSYS 3.49594609802084E35
OWNER 206 DBSNMP 3.54421425584994E35
OWNER 221 EXFSYS 3.60058907050643E35
OWNER 223 FLOWS_ 3.65008529278141E35
OWNER 343 MDSYR? 4.01192665542225E35
OWNER 380 OLAPSY 4.11738089644256E35
OWNER 384 ORACLE 4.11859780070016E35
OWNER 395 ORDDAT 4.1186001805076E35
OWNER 396 ORDPLU 4.11860021777884E35
OWNER 556 ORDSYS 4.11860022722044E35
OWNER 557 OWBSYS 4.11961276313739E35
OWNER 2653 PUBLIB 4.17113006167251E35
OWNER 2654 SCOTT 4.32325845824965E35
OWNER 5134 SYR??? 4.32772349650604E35
OWNER 5364 SYSMAN 4.32772373559899E35
OWNER 5393 SYSTEM 4.32772375731125E35
OWNER 5414 WMSYS 4.53298175814007E35
OWNER 5495 XDA??? 4.58306556462839E35
*/
--查詢最佳化器引數
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','optimizer_index_cost_adj');
/*
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
optimizer_index_cost_adj 100 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
(4)HIGHVAL=dba_tab_co1umns.HIGH_VALUE
(5)LOWVAL=dba_tab_co1umns.LOW_VALUE
(6)COLNB=dba_tab_co1umns.NULLABLE
(9)OPTICA=最佳化器系統引數optimizer_index_cost_adj
(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
(45)= 的選擇率為:(BEN[n]-BEN[n- 1 ])/MAXBEN*DECODE(COLNB= Y,1 ,NNV/ALLROWS)
(73)CPUCOST = #CPUCYCLES /(CPUSPEED*SREADTIM)/1000
(75)RBBLKS = BLVL
(76)RLBLKS = CEIL(LBLKS*INDACCSEL)
(77)IOCOST = ROUND(BLVL + CEIL(LBLKS*INDACCSEL) * OPTICA/100)
(79)IOCOST=ROUND((BLVL + CEIL(LBLKS*INDACCSEL) +CEIL(CLUF*INDFLTSEL))*OPTICA/100)
--套用上面的公式及資料進行計算
表名:T_TEST1
(3)ALLROWS=dba_tab1es.NUM_ROWS=86335
列名:OWNER
(1)NDV=dba_tab_co1umns.num_distinct=23
(2)DENS=dba_tab_co1umns.DENSITY=.0000057966636827901
(4)HIGHVAL=dba_tab_co1umns.HIGH_VALUE=XDB
(5)LOWVAL=dba_tab_co1umns.LOW_VALUE=APEX_030200
(6)COLNB=dba_tab_co1umns.NULLABLE=Y
dba_tab_co1umns.HISTOGRAM=FREQUENCY
(9)OPTICA=最佳化器系統引數optimizer_index_cost_adj=100
(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
--dba_tab_co1umns.HISTOGRAM=FREQUENCY
BEN[n]=匹配的dba_tab_histograms.ENDPOINT_NUMBER,OWNER='SYSTEM',匹配的是5393
所以BEN[n-1]就是BEN[n]的前一個,=5364
MAXBEN是最大的dba_tab_histograms.ENDPOINT_NUMBER,=5495
(45)= 的選擇率為:(BEN[n]-BEN[n- 1 ])/MAXBEN*DECODE(COLNB= Y,1 ,NNV/ALLROWS)
SEL=(5393-5364)/5495*DECODE('Y','Y',1 ,86335/86335)
=0.00527752502274795
ROWS=ALLROWS*SEL=86335*0.00527752502274795=455.635122838944=456
--INDEX RANGE SCAN
BLVL=dba_indexes.BLEVEL=1
LBLKS=dba_indexes.LEAF_BLOCKS=203
INDACCSEL=SEL=0.00527752502274795
IDX_T_TEST1_01 1 203 23 2301 86335
(75)RBBLKS = BLVL=1
(76)RLBLKS = CEIL(LBLKS*INDACCSEL)=CEIL(203*0.00527752502274795)=2
(77)IOCOST = ROUND(BLVL + CEIL(LBLKS*INDACCSEL) * OPTICA/100)
= ROUND(1 + CEIL(203*0.00527752502274795) * 100/100)
=3
--TABLE ACCESS BY INDEX ROWID
CLUF=dba_indexes.CLUSTERING_FACTOR=2301
由於沒有其他過濾條件,所以INDFLTSEL=INDACCSEL=0.00527752502274795
(79)IOCOST=ROUND((BLVL + CEIL(LBLKS*INDACCSEL) +CEIL(CLUF*INDFLTSEL))*OPTICA/100)
=ROUND((1 + CEIL(203*0.00527752502274795) +CEIL(2301*0.00527752502274795))*100/100)
=16
(73)CPUCOST = 394952 /(3074.07407407407*12)/1000
= 0.0107065301204819
COST=IOCOST+CPUCOST=16+0.0107065301204819=16.0107065301204819=16
--可以看到,結果與執行計劃基本相同
ROWS=ALLROWS*SEL=86335*0.00527752502274795=455.635122838944=456
IOCOST[INDEX RANGE SCAN]=ROUND(1 + CEIL(203*0.00527752502274795) * 100/100)=3
IOCOST[TABLE ACCESS BY INDEX ROWID]=ROUND((1 + CEIL(203*0.00527752502274795) +CEIL(2301*0.00527752502274795))*100/100)=16
IOCOST=IOCOST[TABLE ACCESS BY INDEX ROWID]=16
CPUCOST = 394952 /(3074.07407407407*12)/1000=0.0107065301204819
COST=IOCOST+CPUCOST=16+0.0107065301204819=16.061376530120482=16
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28539951/viewspace-1666011/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 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
- oracle中的條件語句Oracle
- 學習Rust 條件語句Rust
- 【cbo計算公式】CBO基本概念(一)公式
- oracle基於cbo成本計算方式說明Oracle
- CBO成本計算初探
- 【開發篇sql】 條件和表示式（九） group by語句SQL
- 草根學Python（五） 條件語句和迴圈語句Python
- Java 數學運算與條件語句全解析Java
- Python學習筆記3（條件語句+迴圈語句）Python筆記
- Python學習筆記（三）——條件語句、迴圈語句Python筆記
- shell程式設計之條件語句程式設計
- 實現四則運算的一條sql語句SQL
- Go 條件語句 - Go 學習記錄Go
- GO 條件語句Go
- javaScript條件語句JavaScript
- 02 shell程式設計之條件語句程式設計
- Oracle 使用一條insert語句完成多表插入Oracle
- day2-計算符和if語句
- 9，javase程式碼實戰-條件語句——switch語句根據使用者消費金額計算折扣(四)Java
- Swift學習筆記（二十七）——條件語句和Switch-case語句Swift筆記
- 【cbo計算公式】No Bind Peeking(五)公式
- Go：條件控制語句Go
- if條件語句sed命令
- Python-條件語句和迴圈語句Python
- Python 基礎起步 (九) 條件語句 if elif else 其實很簡單Python
- oracle語句Oracle