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;
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_merge(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 86336 16921856 4534 355023108 4544 55
MERGE JOIN 86336 16921856 4534 355023108 4544 55
SORT JOIN 86336 8460928 2267 177510937 2272 28
TABLE ACCESS FULL T_TEST1 86336 8460928 343 46104616 344 5
SORT JOIN 86337 8461026 2267 177512171 2272 28
TABLE ACCESS FULL T_TEST2 86337 8461026 343 46105046 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 in ('T_TEST1','T_TEST2');
/*
TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
T_TEST2 86337 1261 98
T_TEST1 86336 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 86336 2 87743 Y 0 5 .0000115826538176427 NONE
T_TEST2 OBJECT_ID 86337 2 87744 Y 0 5 .0000115825196613271 NONE
*/
--查詢各列平均長度之和
select table_name,sum(avg_col_len),count(1)
from dba_tab_col_statistics
where owner='SCOTT' and table_name in ( 'T_TEST1','T_TEST2')
group by table_name;
/*
TABLE_NAME SUM(AVG_COL_LEN) COUNT(1)
T_TEST2 100 15
T_TEST1 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
(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
(25)MREADTIM = IOSEEKTIM + MBRC * OPTBLKSIZ/IOTFRSPEED
(26)SAMSIZE=最佳化器系統引數_smm_max_size*1024
(27)SASIZE=最佳化器系統引數_smm_min_size*1024
(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
(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))
(91)IOCOST[SMJ]= IOCOST[Outer] + IOCOST[Inner]
--套用上面的公式及資料進行計算
表名:T_TEST1
(3)ALLROWS=dba_tab1es.NUM_ROWS=86336
列名:OBJECT_ID
(1)NDV=dba_tab_co1umns.num_distinct=86336
(2)DENS=dba_tab_co1umns.DENSITY=.0000115826538176427
(6)COLNB=dba_tab_co1umns.NULLABLE=Y
表名:T_TEST2
(3)ALLROWS=dba_tab1es.NUM_ROWS=86337
列名:OBJECT_ID
(1)NDV=dba_tab_co1umns.num_distinct=86337
(2)DENS=dba_tab_co1umns.DENSITY=.0000115825196613271
(6)COLNB=dba_tab_co1umns.NULLABLE=Y
(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
(25)MREADTIM = IOSEEKTIM + MBRC * OPTBLKSIZ/IOTFRSPEED=10+8*8192/4096=26
(26)SAMSIZE=最佳化器系統引數_smm_max_size*1024=57344*1024=58720256
(27)SASIZE=最佳化器系統引數_smm_min_size*1024=286*1024=292864
--TABLE ACCESS FULL
ROWS[Outer]=86336
ROWS[Inner]=86337
(72)IOCOST = (#BLKS/MBRC)*(IOSEEKTIM + MBRC*OPTBLKSIZE/IOTFRSPEED)/(IOSEEKTIM+OPTBLKSIZE/IOTFRSPEED)
IOCOST[Outer] = (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] = (1261/8)*(10+8*8192/4096)/(10+8192/4096)=341.520833333333
由於_optimizer_ceil_cost=true,_table_scan_cost_plus_one=true,所以微調為:
IOCOSTInner]=ceil(341.520833333333)+1=343
--SORT JOIN
--[Outer]
SROWNUM=ROWS[Outer]
(81)RROWSIZE = LEAST(ARL, SUM(ACL1~n))
=LEAST(98, 100)
=98
(82)SROWSIZE = RROWSIZE + 10 + CEIL(RROWSIZE/10)
=98+10+CEIL(98/10)
=118
(80)SDSIZE = SROWNUM*SROWSIZE=86336*118=10187648
(83)如果SDSIZE > SASIZE,說明排序需要寫入磁碟,否則IOCOST[Sort]=0
SDSIZE(10187648)>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,MBDRC)*80)*1024)/(( 57344+8192)*2.5))
=334
(85)INTRUNS = GREATEST(CEIL(SDSIZE/SAMSIZE), 2)
=GREATEST(CEIL(10187648/58720256), 2)
=2
(86)MERGES = CEIL(LOG(SORTWIDTH,INIRUNS))
=CEIL(LOG(334,2))
=1
(87)SORTBLKS = CEIL(SDSIZE/(OPTBLKSIZE-24))
=CEIL(10187648/(8192-24))
=1248
(88)PASSIO = CEIL(SORTBLKS*(MBDRC*MREADTIM/SREADTIM)/(MBDRC+1 )/(MBRC-1 ))*2 + CEIL(S0RTBLKS*(MBRC-1-MBDRC)/(MBDRC+1)/(MBRC-1))*2
=CEIL(1248*(7*MREADTIM/SREADTIM)/(7+1 )/(8-1 ))*2 + CEIL(1248*(8-1-7)/(7+1)/(8-1))*2
=676
(89)IOCOST[Sort in disk]= SORTBLKS + (PASSIO * MERGES)
=1248+(676 * 1)
=1924
IOCOST[Outer]=IOCOST[Sort]+IOCOST[TABLE ACCESS FULL]
=1924+343
=2267
--[Inner]
因為內部的表和外部的一樣,所以這裡省略計算方式,
IOCOST[Inter]=IOCOST[Outer]=2267
--MERGE JOIN
(69)JOINSEL= LEAST(COLSEL[Outer], COLSEL[Inner])
= LEAST(1/86336, 1/86337)
=1/86337
(70)CARD[Join] = CARD[Outer]*CARD[Inner]*JOINSEL
=86336*86337*1/86337
=86336
(91)IOCOST[SMJ]= IOCOST[Outer] + IOCOST[Inner]
=2267+2267
=4534
(73)CPUCOST = #CPUCYCLES /(CPUSPEED*SREADTIM)/1000
=355023108/(3074.07407407407*12)/1000
=9.62412039759037
COST=IOCOST+CPUCOST=IOCOST[SMJ]=CPUCOST=4534+9.62412039759037=4543.62412039759037=4544
--可以看到,結果與執行計劃基本相同
ROWS=CARD[Join]=86336
IOCOST[Outer]=2267
IOCOST[Inner]=2267
IOCOST[SMJ]=4534
CPUCOST = 9.62412039759037
COST=IOCOST[NLJ]+CPUCOST=4544
工作中,你可能會遇到統計資料正確,但就是不走你想要的執行計劃的情況,最後一般透過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;
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_merge(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 86336 16921856 4534 355023108 4544 55
MERGE JOIN 86336 16921856 4534 355023108 4544 55
SORT JOIN 86336 8460928 2267 177510937 2272 28
TABLE ACCESS FULL T_TEST1 86336 8460928 343 46104616 344 5
SORT JOIN 86337 8461026 2267 177512171 2272 28
TABLE ACCESS FULL T_TEST2 86337 8461026 343 46105046 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 in ('T_TEST1','T_TEST2');
/*
TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
T_TEST2 86337 1261 98
T_TEST1 86336 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 86336 2 87743 Y 0 5 .0000115826538176427 NONE
T_TEST2 OBJECT_ID 86337 2 87744 Y 0 5 .0000115825196613271 NONE
*/
--查詢各列平均長度之和
select table_name,sum(avg_col_len),count(1)
from dba_tab_col_statistics
where owner='SCOTT' and table_name in ( 'T_TEST1','T_TEST2')
group by table_name;
/*
TABLE_NAME SUM(AVG_COL_LEN) COUNT(1)
T_TEST2 100 15
T_TEST1 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
(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
(25)MREADTIM = IOSEEKTIM + MBRC * OPTBLKSIZ/IOTFRSPEED
(26)SAMSIZE=最佳化器系統引數_smm_max_size*1024
(27)SASIZE=最佳化器系統引數_smm_min_size*1024
(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
(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))
(91)IOCOST[SMJ]= IOCOST[Outer] + IOCOST[Inner]
--套用上面的公式及資料進行計算
表名:T_TEST1
(3)ALLROWS=dba_tab1es.NUM_ROWS=86336
列名:OBJECT_ID
(1)NDV=dba_tab_co1umns.num_distinct=86336
(2)DENS=dba_tab_co1umns.DENSITY=.0000115826538176427
(6)COLNB=dba_tab_co1umns.NULLABLE=Y
表名:T_TEST2
(3)ALLROWS=dba_tab1es.NUM_ROWS=86337
列名:OBJECT_ID
(1)NDV=dba_tab_co1umns.num_distinct=86337
(2)DENS=dba_tab_co1umns.DENSITY=.0000115825196613271
(6)COLNB=dba_tab_co1umns.NULLABLE=Y
(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
(25)MREADTIM = IOSEEKTIM + MBRC * OPTBLKSIZ/IOTFRSPEED=10+8*8192/4096=26
(26)SAMSIZE=最佳化器系統引數_smm_max_size*1024=57344*1024=58720256
(27)SASIZE=最佳化器系統引數_smm_min_size*1024=286*1024=292864
--TABLE ACCESS FULL
ROWS[Outer]=86336
ROWS[Inner]=86337
(72)IOCOST = (#BLKS/MBRC)*(IOSEEKTIM + MBRC*OPTBLKSIZE/IOTFRSPEED)/(IOSEEKTIM+OPTBLKSIZE/IOTFRSPEED)
IOCOST[Outer] = (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] = (1261/8)*(10+8*8192/4096)/(10+8192/4096)=341.520833333333
由於_optimizer_ceil_cost=true,_table_scan_cost_plus_one=true,所以微調為:
IOCOSTInner]=ceil(341.520833333333)+1=343
--SORT JOIN
--[Outer]
SROWNUM=ROWS[Outer]
(81)RROWSIZE = LEAST(ARL, SUM(ACL1~n))
=LEAST(98, 100)
=98
(82)SROWSIZE = RROWSIZE + 10 + CEIL(RROWSIZE/10)
=98+10+CEIL(98/10)
=118
(80)SDSIZE = SROWNUM*SROWSIZE=86336*118=10187648
(83)如果SDSIZE > SASIZE,說明排序需要寫入磁碟,否則IOCOST[Sort]=0
SDSIZE(10187648)>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,MBDRC)*80)*1024)/(( 57344+8192)*2.5))
=334
(85)INTRUNS = GREATEST(CEIL(SDSIZE/SAMSIZE), 2)
=GREATEST(CEIL(10187648/58720256), 2)
=2
(86)MERGES = CEIL(LOG(SORTWIDTH,INIRUNS))
=CEIL(LOG(334,2))
=1
(87)SORTBLKS = CEIL(SDSIZE/(OPTBLKSIZE-24))
=CEIL(10187648/(8192-24))
=1248
(88)PASSIO = CEIL(SORTBLKS*(MBDRC*MREADTIM/SREADTIM)/(MBDRC+1 )/(MBRC-1 ))*2 + CEIL(S0RTBLKS*(MBRC-1-MBDRC)/(MBDRC+1)/(MBRC-1))*2
=CEIL(1248*(7*MREADTIM/SREADTIM)/(7+1 )/(8-1 ))*2 + CEIL(1248*(8-1-7)/(7+1)/(8-1))*2
=676
(89)IOCOST[Sort in disk]= SORTBLKS + (PASSIO * MERGES)
=1248+(676 * 1)
=1924
IOCOST[Outer]=IOCOST[Sort]+IOCOST[TABLE ACCESS FULL]
=1924+343
=2267
--[Inner]
因為內部的表和外部的一樣,所以這裡省略計算方式,
IOCOST[Inter]=IOCOST[Outer]=2267
--MERGE JOIN
(69)JOINSEL= LEAST(COLSEL[Outer], COLSEL[Inner])
= LEAST(1/86336, 1/86337)
=1/86337
(70)CARD[Join] = CARD[Outer]*CARD[Inner]*JOINSEL
=86336*86337*1/86337
=86336
(91)IOCOST[SMJ]= IOCOST[Outer] + IOCOST[Inner]
=2267+2267
=4534
(73)CPUCOST = #CPUCYCLES /(CPUSPEED*SREADTIM)/1000
=355023108/(3074.07407407407*12)/1000
=9.62412039759037
COST=IOCOST+CPUCOST=IOCOST[SMJ]=CPUCOST=4534+9.62412039759037=4543.62412039759037=4544
--可以看到,結果與執行計劃基本相同
ROWS=CARD[Join]=86336
IOCOST[Outer]=2267
IOCOST[Inner]=2267
IOCOST[SMJ]=4534
CPUCOST = 9.62412039759037
COST=IOCOST[NLJ]+CPUCOST=4544
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28539951/viewspace-1668772/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle中的條件語句Oracle
- 【cbo計算公式】CBO基本概念(一)公式
- Java 數學運算與條件語句全解析Java
- 學習Rust 條件語句Rust
- 【cbo計算公式】No Bind Peeking(五)公式
- Python學習筆記3（條件語句+迴圈語句）Python筆記
- 條件語句
- shell程式設計之條件語句程式設計
- ORACLE常用語句：Oracle
- javaScript條件語句JavaScript
- GO 條件語句Go
- Go 條件語句 - Go 學習記錄Go
- 02 shell程式設計之條件語句程式設計
- 6. Oracle開發和應用—6.4. PL/SQL語法—6.4.4. 條件語句（分支語句）OracleSQL
- Oracle：select 或 inactive 會話語句產生鎖？Oracle會話
- 【cbo計算公式】Join 選擇率(六)公式
- Python-條件語句和迴圈語句Python
- Oracle基本SQL語句OracleSQL
- Oracle 建立序列語句Oracle
- day2-計算符和if語句
- 用python和計算機對話（計算機的語句）Python計算機
- Go：條件控制語句Go
- 04.條件語句
- Python if else條件語句Python
- 【cbo計算公式】單表選擇率(二)公式
- 12/3賓語從句
- ORACLE 資料庫 查詢語句與DML語句Oracle資料庫
- SQL查詢語句 (Oracle)SQLOracle
- PL/SQL 條件控制語句SQL
- Swift 條件語句講解Swift
- python-條件控制(if語句)Python
- 條件判斷語句 if case
- 計算節點會對收到的SQL 語句做解析、優化、執行並SQL優化
- ORACLE多表關聯UPDATE語句Oracle
- Oracle資料庫語句大全Oracle資料庫
- 列出oracle dbtime得sql語句OracleSQL
- oracle檢視物件DDL語句Oracle物件
- 【LOB】Oracle lob管理常用語句Oracle
- oracle語句練習--初級Oracle