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條語句分別去套用說明.
本篇是cpu代價模型下cost演算法公式說明:
COST=IOCOST+CPUCOST
CPUCOST = #CPUCYCLES/(CPUSPEED*SREADTIM)
Bytes =Rows*AVG_ROW_LEN
Time =CEIL(COST*SREADTIM/1000)
以下主要是關於Rows和IOCOST的演算法公式.
--公式前端的(xxx)編號用於在後12篇中引用時標記
--由於CBO演算法各版本變動很大,所以可能有誤
1.基礎資料來源
(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
(7)ACL=dba_tab_co1umns.AVG_COL_LEN
(8)ARL=dba_tab_co1umns.AVG_ROW_LEN
(9)OPTICA=最佳化器系統引數optimizer_index_cost_adj
(10)PPCT=最佳化器系統引數_optimizer_percent_parallel
(11)MBRC=最佳化器系統引數_db_fi1e_optimizer_read_count
(12)SAMINIO=最佳化器系統引數_smm_auto_min_io_size*1024
(13)SAMAXIO=最佳化器系統引數_smm_auto_max_io_size*1024
(14)OPTBLKSIZE=最佳化器系統引數_optimizer_b1ock_size
(15)SMPXMAX=最佳化器系統引數_smm_px_max_size
(16)MBDRC=SAMINIO/OPTBLKSIZE
(17)DOP=並行度
負載模式(NOWORKLOAD):
(18)CPUSPEED=系統統計資料CPUSPEED
(19)SREADTIM=系統統計資料SREADTIM
(20)MREADTIM=系統統計資料MREADTIM
無負載模式(WORKLOAD):
(21)CPUSPEED=系統統計資料CPUSPEEDNW
(22)IOTFRSPEED=系統統計資料IOTFRSPEED
(23)IOSEEKTIM=系統統計資料IOSEEKTIM
(24)SREADTIM = IOSEEKTIM + OPTBLKSIZ/IOTFRSPEED
(25)MREADTIM = IOSEEKTIM + MBRC * OPTBLKSIZ/IOTFRSPEED
workarea_size_po1icy=auto時
(26)SAMSIZE=最佳化器系統引數_smm_max_size*1024=58720256
(27)SASIZE=最佳化器系統引數_smm_min_size*1024=292864
workarea_size_po1icy=manua1時
(28)SAMSIZE=最佳化器系統引數sort_area_size
2.單表單條件選擇率
--以下選擇的率的計算,都是條件中的值在範圍內的情況下;值不在範圍內的,會根據偏離的程度而遞減.
a.條件值為常量或繫結變數有值,無直方圖
(29)=的選擇率為: GREATHST(1/NDV,DENS)*DECODE(COLNB= Y,1,NNV/ALLROWS)
(30)<>,!= 的選擇率為: 1- GREATEST(1/NDV,DENS)*DECODH(COLNB= Y,1 ,NNV/ALLROWS)
(31)>,
(32)<=,>= 的選擇率為: ((BVAL- LOWVAL)/(HIGHVAL- LOWVAL)*DECODE+1/NNV)(COLNB=Y,1,NNV/ALLROWS)
(33)NOT LIKE 的選擇率為: 1-GRHATEST(1/20,1/NDV,DENS)*DECODE(COLNB= Y,1 ,NNV/ALLROWS)
(34)IN 的選擇率為: INNUM*GREATEST(1 /NDV,DENS)*DECODE(COLNB= Y,1 ,NNV/ALLROWS)
(35)NOT LIKE的選擇率為: 1-(BVAL-LOWVAL)/(HTGHVAL-LOWVAT.)*DECODE(COLNB=Y,1,NNV/ALLROWS)
(36)BETWEEN AND 的選擇率為: (BVAL[h]-BVAL[1])/(HIGHVAL-LOWVAL)*DECODE(COLNB=Y,1,NNV/ALLROWS)
b.繫結變數無值,無直方圖
(37)= 的選擇率為:GREATHST(1/NDV,DENS)*DECODE(COLNB= Y,1,NNV/ALLROWS)
(38)<>,!=的選擇率為: 1- GREATEST(1/NDV,DENS)*DECODH(COLNB= Y,1 ,NNV/ALLROWS)
(39)>,
(40)<=,>=的選擇率為: (GREATEST(I/20,1/NDV,DENS)+1/NNV)*DECODE(COLNB- Y,1,NNV/ALLROWS)
(41)NOT LIKE的選擇率為: 1-GRHATEST(1/20,1/NDV,DENS)*DECODE(COLNB- Y,1,NNV/ALLROWS)
(42)IN 的選擇率為:INNUM*GREATEST(1 /NDVf DENS)*DECODE(COLNB= Y,1 ,NNV/ALLROWS)
(43)NOT IN 的選擇率為:(1-GREATEST(1/NDV,DHNS)*DHCODH(COLNB= Y,1,NNV/ALLROWS))^INNUM
(44)BETWEEN AND 的選擇率為:GREATEST(1/20,1/NDV,DENS)*DECODE(COLNB= Y,1,NNV/ALLROWS))^2
c.頻率柱狀圖選擇率
(45)= 的選擇率為:(BEN[n]-BEN[n- 1 ])/MAXBEN*DECODE(COLNB= Y,1 ,NNV/ALLROWS)
(46)<>,!= 的選擇率為:1- ( BEN[n]-BEN[n- 1 ])/MAXBEN*DHCODE(COLNB= Y,1,NNV/ALLROWS)
(47)
(48)<= 的選擇率為:(BEN[n]+0.5)/MAXBEN*DECODE(COLNB=Y,1 ,NNV/ALLROWS)
(49)> 的選擇率為:1 ~(BEN[n]+0.5)/MAXBEN*DECODE(COLNB= Y,1 ,NNV/ALLROWS)
(50)>= 的選擇率為:1-(BEN[n- 1 ]+0.5)/MAXBEN*DECODE(COLNB= Y,1,NNV/ALLROWS)
(51)LIKE 的選擇率為: (DBEN[1]+DBEN[2]+...+DBHN[i])/MAXBEN*DECODE(COLNB= Y,1,NNV/ALLROWS)
(52)NOT LIKE 的選擇率為:1-(DBEN[1 ]+DBENt2]+...+DBEN[i])/MAXBEN*DHCODE(COLNB= Y,1,NNV/ALLROWS)
(53)IN 的選擇率為:(DBHN[1 ]+DBEN[2]+...+DBHN[i])/MAXBHN*DHCODH(COLNB= Y,1 ,NNV/ALLROWS)
(54)NOT IN 的選擇率為:1-(DBHN[1 ]+DBHN[2]+...+DBHN[i])/MAXBEN*DECODE(COLNB= Y,1,(NNV/ALLROWS)
(55)BETWEEN AND 的選擇率為:(BEN[h]~BHN[1])/MAXBEN*DECODH(COLNB= Y,1 ,NNV/ALLROWS)
d.高平衡柱狀圖選擇率
(56)= 的選擇率為:DECODE(BEN[n]-BEN[n- 1] > i ,(BEN[n BEN[n- 1])/BKTNUM，GREATEST(1 /NDV,DENS))*DECODE(COLNB= Y,1 ,NNV/ALLROWS)
(57)<>,!=的選擇率為: 1 - DECODE(BEN[n]-BEN[n- 1 ]>1 ，(BEN[n] -BEN[n—1])/BKTNUM，GREATEST(1/NDV,DENS))*DFCODE(COLNB- Y,1 ,NNV/ALLROWS)
(58)
(59)<=的選擇率為: (BEN[n]+POPADJ+ RANGEADJ)/BKTNUM*DECODE(COLNB= Y,1 ,NNV/ALLROWS)
(60)> 的選擇率為:1 -(BEN [n ]+POPADJ+ RAN GEADJ)/BKTNUM *DECODH(COLNB= Y,1 ,NNV/ALLROWS)
(61)>=的選擇率為: 1 -(BEN[n- 1 ]+POPADJ+ RANGEADJ)/BKTNUM*DECODE(COLNB* Y,1 ,NNV/ALLROWS)
(62)IN的選擇率為: EQOPSEL[1 ]+EQOPSEL[2]+...+EQOPSEL[i]
(63)NOT IN 的選擇率為:1-EQOPSEL[1]+EQOPSEL[2]+...+EQOPSEL[i]
(64)BETWEEN AND,LIKE的選擇率為: BEN[h]-BEN[1]+POPADJ+ RANGEADJ)/BKTNUM*DECODE(COLNB= Y,1 ,NNV/ALLROWS)
(65)NOT LIKE的選擇率為: 1-(BEN[h]~BEN[1]+POPADJ+ RANGEADJ)/BKTNUM*DECODE(COLNB= Y,1 ,NNV/ALLROWS)
3.單表多條件過濾組合的選擇率
(66)A AND B 的選擇率為:OPSEL[a] * OPSEL[b]
(67)A OR B 的選擇率為:OPSEL[a] + OPSEL[b]- OPSEL[a]OPSEL[b]
(68)NOT A 的選擇率為:1-OPSEL[a]
4.兩表關聯選擇率
(69)JOINSEL= LEASTEST(COLSEL[Outer], COLSEL[Inner])
(70)CARD[Join] = CARD[Outer]*CARD[Inner]*JOINSEL
5.單表多資料塊讀操作代價計算
負載模式(NOWORKLOAD):
(71)IOCOST = (#BLKS/MBRC)*MREADTIM/SREADTIM
無負載模式(WORKLOAD):
(72)IOCOST = (#BLKS/MBRC)*(IOSEEKTIM + MBRC*OPTBLKSIZE/IOTFRSPEED)/(IOSEEKTIM+OPTBLKSIZE/IOTFRSPEED)
(73)CPUCOST = #CPUCYCLES /(CPUSPEED*SREADTIM)
6.單表單資料塊讀操作代價計算
(1)IOCOST = SRDCOST = #SRDS
索引範圍掃描
(74)#SRDS = RBBLKS + RLBLKS
(75)RBBLKS = BLVL
(76)RLBLKS = CEIL(LBLKS*INDACCSEL)
(77)IOCOST = ROUND((BLVL + CEIL(LBLKS*INDACCSEL) * OPTICA/100)
索引唯一掃描
(78)IOCOST = BLVL
由索引ROWID訪問表
(79)IOCOST=ROUND((BLVL + CEIL(LBLKS*INDACCSEL) +CEIL(CLUF*INDFLTSEL))*OPTICA/100)
7.單表排序操作代價計算
(80)SDSIZE = SROWNUM*SROWSIZE
(81)RROWSIZE = LEAST(AVGRLEN, 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))
8.兩表關聯代價計算
a.巢狀迴圈關聯代價計算
(90)IOCOST[NLJ] = IOCOST[Outer] + CARD[Outer]* IOCOST[Inner]
b.排序合併關聯代價計算
(91)IOCOST[SMJ]= IOCOST[Outer] + IOCOST[Inner]
c.雜湊關聯代價計算
(92)SACL[Outer]= ACL[OuterColl]+...ACL[OuterColn]
(93)SACL[Inner]= ACL[InnerColl]+...ACL[InnerColn]
(94)HSIZE[Outer]= CARD[Outer]*(LEAST(ARL[Outer], SACL[Outer])+12)
(95)HSIZE[Inner] = CARD[Inner]*(LEAST(ARL[Inner], SACL[Inner])+12)
(96)MAXHASHAREA = CEIL(SMMAX*2/OPTBLKSIZE)
(97)HASHAREA = CEIL(LEAST(GREATEST(SAMAXIO*4/OPTBLKSIZE,SMMIN*2/OPTBLKSIZE),MAXHASHAERA*0.08))
(98)當HSIZE[Outer]>HASHAREA*OPTBLKSIZE,說明hash需要寫入磁碟,否則IOCOST[Hash]=0
(99)BULDFRAGS = TRUNC(HSIZE[Outer]/OPTBLKSIZE+ 1)
(100)PROBEFRAGS = TRUNC(HSIZE[Inner]/OPTBLKSIZE+ 1)
(101)HASHBLKS = BULDFRAGS + PROBEFRAGS
(102)IOCOST[Hash] = CEIL(2*HASHBLKS/(MBRC-1)*(MREADTIM/SREADTIM+MBRC/TRUNC(SAMAXIO/OPTBLKSIZE) - (1+MREADTIM/SREADTIM/TRUNC(SAMAXIO/OPTBLKSIZE))))
(103)IOCOST[HJ]= IOCOST[Outer] + IOCOST[Inner] + IOCOST[Hash]
9.單表並行代價計算
當PPCT為預設值時
(104)IOCOST[Paralle1] = IOCOST[Serial]*10/9/DOP
當PPCT不為預設值時
(105)IOCOST[Paralle1]= GREATEST(0,1-PPCT)*IOCOST[Serial]+ PPCT*IOCOST[Serial]*10/9/DOP
10.兩表關聯並行代價計算
(106)SACL[Outer]= ACL[OuterColl]+...ACL[OuterColn]
(107)SACL[Inner]= ACL[InnerColl]+...ACL[InnerColn]
(108)HSIZE[Outer]= CARD[Outer]*(LEAST(ARL[Outer], SACL[Outer])+12)
(109)HSIZE[Inner]= CARD[Inner]*(LEAST(ARL[Outer], SACL[Inner])+12)
(110)#PTNS = GREATEST( DOP[Outer], DOP[Inner])
(111)MAXHASHAREA= GREATEST(160,SMINIO*4/OPTBLKSIZE,CEIL(SMPXMAX*2/#PTNS/OPTBLKSIZE),SMMAX/(160* 1024))
(112)HASHAREA= CEIL(GREATEST(SMINIO*4/OPTBLKSIZE,MAXHASHAERA*0.2))
(113)當HSIZE[Outer]>HASHAREA*OPTBLKSIZE*#PTNS,說明hash需要寫入磁碟,否則IOCOST[Hash]=0
(114)IOCOST[Outer](Paralle1)= ROUND(IOCOST[Outer](Seria1)*10/9/DOP[Outer])
(115)IOCOST[Inner](Paralle1) = ROUND(IOCOST[Inner](Seria1)*10/9/DOP[Inner])
(116)IOCOST[Hash] = CEIL(2*HASHBLKS/(MBRC-1 )*(MREADTIM/SREADTIM+MBRC/TRUNC(SAMINIO/OPTBLKSIZE)-(1 + MREADTIM/SREADTIM/TRUNC(SAMINIO/OPTBLKSIZE))))
(117)IOCOST[HJ]= IOCOST[Outer](Parallel)+IOCOST[Inner](Parallel)+IOCOST[Hash]
工作中,你可能會遇到統計資料正確,但就是不走你想要的執行計劃的情況,最後一般透過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條語句分別去套用說明.
本篇是cpu代價模型下cost演算法公式說明:
COST=IOCOST+CPUCOST
CPUCOST = #CPUCYCLES/(CPUSPEED*SREADTIM)
Bytes =Rows*AVG_ROW_LEN
Time =CEIL(COST*SREADTIM/1000)
以下主要是關於Rows和IOCOST的演算法公式.
--公式前端的(xxx)編號用於在後12篇中引用時標記
--由於CBO演算法各版本變動很大,所以可能有誤
1.基礎資料來源
(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
(7)ACL=dba_tab_co1umns.AVG_COL_LEN
(8)ARL=dba_tab_co1umns.AVG_ROW_LEN
(9)OPTICA=最佳化器系統引數optimizer_index_cost_adj
(10)PPCT=最佳化器系統引數_optimizer_percent_parallel
(11)MBRC=最佳化器系統引數_db_fi1e_optimizer_read_count
(12)SAMINIO=最佳化器系統引數_smm_auto_min_io_size*1024
(13)SAMAXIO=最佳化器系統引數_smm_auto_max_io_size*1024
(14)OPTBLKSIZE=最佳化器系統引數_optimizer_b1ock_size
(15)SMPXMAX=最佳化器系統引數_smm_px_max_size
(16)MBDRC=SAMINIO/OPTBLKSIZE
(17)DOP=並行度
負載模式(NOWORKLOAD):
(18)CPUSPEED=系統統計資料CPUSPEED
(19)SREADTIM=系統統計資料SREADTIM
(20)MREADTIM=系統統計資料MREADTIM
無負載模式(WORKLOAD):
(21)CPUSPEED=系統統計資料CPUSPEEDNW
(22)IOTFRSPEED=系統統計資料IOTFRSPEED
(23)IOSEEKTIM=系統統計資料IOSEEKTIM
(24)SREADTIM = IOSEEKTIM + OPTBLKSIZ/IOTFRSPEED
(25)MREADTIM = IOSEEKTIM + MBRC * OPTBLKSIZ/IOTFRSPEED
workarea_size_po1icy=auto時
(26)SAMSIZE=最佳化器系統引數_smm_max_size*1024=58720256
(27)SASIZE=最佳化器系統引數_smm_min_size*1024=292864
workarea_size_po1icy=manua1時
(28)SAMSIZE=最佳化器系統引數sort_area_size
2.單表單條件選擇率
--以下選擇的率的計算,都是條件中的值在範圍內的情況下;值不在範圍內的,會根據偏離的程度而遞減.
a.條件值為常量或繫結變數有值,無直方圖
(29)=的選擇率為: GREATHST(1/NDV,DENS)*DECODE(COLNB= Y,1,NNV/ALLROWS)
(30)<>,!= 的選擇率為: 1- GREATEST(1/NDV,DENS)*DECODH(COLNB= Y,1 ,NNV/ALLROWS)
(31)>,
(32)<=,>= 的選擇率為: ((BVAL- LOWVAL)/(HIGHVAL- LOWVAL)*DECODE+1/NNV)(COLNB=Y,1,NNV/ALLROWS)
(33)NOT LIKE 的選擇率為: 1-GRHATEST(1/20,1/NDV,DENS)*DECODE(COLNB= Y,1 ,NNV/ALLROWS)
(34)IN 的選擇率為: INNUM*GREATEST(1 /NDV,DENS)*DECODE(COLNB= Y,1 ,NNV/ALLROWS)
(35)NOT LIKE的選擇率為: 1-(BVAL-LOWVAL)/(HTGHVAL-LOWVAT.)*DECODE(COLNB=Y,1,NNV/ALLROWS)
(36)BETWEEN AND 的選擇率為: (BVAL[h]-BVAL[1])/(HIGHVAL-LOWVAL)*DECODE(COLNB=Y,1,NNV/ALLROWS)
b.繫結變數無值,無直方圖
(37)= 的選擇率為:GREATHST(1/NDV,DENS)*DECODE(COLNB= Y,1,NNV/ALLROWS)
(38)<>,!=的選擇率為: 1- GREATEST(1/NDV,DENS)*DECODH(COLNB= Y,1 ,NNV/ALLROWS)
(39)>,
(40)<=,>=的選擇率為: (GREATEST(I/20,1/NDV,DENS)+1/NNV)*DECODE(COLNB- Y,1,NNV/ALLROWS)
(41)NOT LIKE的選擇率為: 1-GRHATEST(1/20,1/NDV,DENS)*DECODE(COLNB- Y,1,NNV/ALLROWS)
(42)IN 的選擇率為:INNUM*GREATEST(1 /NDVf DENS)*DECODE(COLNB= Y,1 ,NNV/ALLROWS)
(43)NOT IN 的選擇率為:(1-GREATEST(1/NDV,DHNS)*DHCODH(COLNB= Y,1,NNV/ALLROWS))^INNUM
(44)BETWEEN AND 的選擇率為:GREATEST(1/20,1/NDV,DENS)*DECODE(COLNB= Y,1,NNV/ALLROWS))^2
c.頻率柱狀圖選擇率
(45)= 的選擇率為:(BEN[n]-BEN[n- 1 ])/MAXBEN*DECODE(COLNB= Y,1 ,NNV/ALLROWS)
(46)<>,!= 的選擇率為:1- ( BEN[n]-BEN[n- 1 ])/MAXBEN*DHCODE(COLNB= Y,1,NNV/ALLROWS)
(47)
(48)<= 的選擇率為:(BEN[n]+0.5)/MAXBEN*DECODE(COLNB=Y,1 ,NNV/ALLROWS)
(49)> 的選擇率為:1 ~(BEN[n]+0.5)/MAXBEN*DECODE(COLNB= Y,1 ,NNV/ALLROWS)
(50)>= 的選擇率為:1-(BEN[n- 1 ]+0.5)/MAXBEN*DECODE(COLNB= Y,1,NNV/ALLROWS)
(51)LIKE 的選擇率為: (DBEN[1]+DBEN[2]+...+DBHN[i])/MAXBEN*DECODE(COLNB= Y,1,NNV/ALLROWS)
(52)NOT LIKE 的選擇率為:1-(DBEN[1 ]+DBENt2]+...+DBEN[i])/MAXBEN*DHCODE(COLNB= Y,1,NNV/ALLROWS)
(53)IN 的選擇率為:(DBHN[1 ]+DBEN[2]+...+DBHN[i])/MAXBHN*DHCODH(COLNB= Y,1 ,NNV/ALLROWS)
(54)NOT IN 的選擇率為:1-(DBHN[1 ]+DBHN[2]+...+DBHN[i])/MAXBEN*DECODE(COLNB= Y,1,(NNV/ALLROWS)
(55)BETWEEN AND 的選擇率為:(BEN[h]~BHN[1])/MAXBEN*DECODH(COLNB= Y,1 ,NNV/ALLROWS)
d.高平衡柱狀圖選擇率
(56)= 的選擇率為:DECODE(BEN[n]-BEN[n- 1] > i ,(BEN[n BEN[n- 1])/BKTNUM，GREATEST(1 /NDV,DENS))*DECODE(COLNB= Y,1 ,NNV/ALLROWS)
(57)<>,!=的選擇率為: 1 - DECODE(BEN[n]-BEN[n- 1 ]>1 ，(BEN[n] -BEN[n—1])/BKTNUM，GREATEST(1/NDV,DENS))*DFCODE(COLNB- Y,1 ,NNV/ALLROWS)
(58)
(59)<=的選擇率為: (BEN[n]+POPADJ+ RANGEADJ)/BKTNUM*DECODE(COLNB= Y,1 ,NNV/ALLROWS)
(60)> 的選擇率為:1 -(BEN [n ]+POPADJ+ RAN GEADJ)/BKTNUM *DECODH(COLNB= Y,1 ,NNV/ALLROWS)
(61)>=的選擇率為: 1 -(BEN[n- 1 ]+POPADJ+ RANGEADJ)/BKTNUM*DECODE(COLNB* Y,1 ,NNV/ALLROWS)
(62)IN的選擇率為: EQOPSEL[1 ]+EQOPSEL[2]+...+EQOPSEL[i]
(63)NOT IN 的選擇率為:1-EQOPSEL[1]+EQOPSEL[2]+...+EQOPSEL[i]
(64)BETWEEN AND,LIKE的選擇率為: BEN[h]-BEN[1]+POPADJ+ RANGEADJ)/BKTNUM*DECODE(COLNB= Y,1 ,NNV/ALLROWS)
(65)NOT LIKE的選擇率為: 1-(BEN[h]~BEN[1]+POPADJ+ RANGEADJ)/BKTNUM*DECODE(COLNB= Y,1 ,NNV/ALLROWS)
3.單表多條件過濾組合的選擇率
(66)A AND B 的選擇率為:OPSEL[a] * OPSEL[b]
(67)A OR B 的選擇率為:OPSEL[a] + OPSEL[b]- OPSEL[a]OPSEL[b]
(68)NOT A 的選擇率為:1-OPSEL[a]
4.兩表關聯選擇率
(69)JOINSEL= LEASTEST(COLSEL[Outer], COLSEL[Inner])
(70)CARD[Join] = CARD[Outer]*CARD[Inner]*JOINSEL
5.單表多資料塊讀操作代價計算
負載模式(NOWORKLOAD):
(71)IOCOST = (#BLKS/MBRC)*MREADTIM/SREADTIM
無負載模式(WORKLOAD):
(72)IOCOST = (#BLKS/MBRC)*(IOSEEKTIM + MBRC*OPTBLKSIZE/IOTFRSPEED)/(IOSEEKTIM+OPTBLKSIZE/IOTFRSPEED)
(73)CPUCOST = #CPUCYCLES /(CPUSPEED*SREADTIM)
6.單表單資料塊讀操作代價計算
(1)IOCOST = SRDCOST = #SRDS
索引範圍掃描
(74)#SRDS = RBBLKS + RLBLKS
(75)RBBLKS = BLVL
(76)RLBLKS = CEIL(LBLKS*INDACCSEL)
(77)IOCOST = ROUND((BLVL + CEIL(LBLKS*INDACCSEL) * OPTICA/100)
索引唯一掃描
(78)IOCOST = BLVL
由索引ROWID訪問表
(79)IOCOST=ROUND((BLVL + CEIL(LBLKS*INDACCSEL) +CEIL(CLUF*INDFLTSEL))*OPTICA/100)
7.單表排序操作代價計算
(80)SDSIZE = SROWNUM*SROWSIZE
(81)RROWSIZE = LEAST(AVGRLEN, 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))
8.兩表關聯代價計算
a.巢狀迴圈關聯代價計算
(90)IOCOST[NLJ] = IOCOST[Outer] + CARD[Outer]* IOCOST[Inner]
b.排序合併關聯代價計算
(91)IOCOST[SMJ]= IOCOST[Outer] + IOCOST[Inner]
c.雜湊關聯代價計算
(92)SACL[Outer]= ACL[OuterColl]+...ACL[OuterColn]
(93)SACL[Inner]= ACL[InnerColl]+...ACL[InnerColn]
(94)HSIZE[Outer]= CARD[Outer]*(LEAST(ARL[Outer], SACL[Outer])+12)
(95)HSIZE[Inner] = CARD[Inner]*(LEAST(ARL[Inner], SACL[Inner])+12)
(96)MAXHASHAREA = CEIL(SMMAX*2/OPTBLKSIZE)
(97)HASHAREA = CEIL(LEAST(GREATEST(SAMAXIO*4/OPTBLKSIZE,SMMIN*2/OPTBLKSIZE),MAXHASHAERA*0.08))
(98)當HSIZE[Outer]>HASHAREA*OPTBLKSIZE,說明hash需要寫入磁碟,否則IOCOST[Hash]=0
(99)BULDFRAGS = TRUNC(HSIZE[Outer]/OPTBLKSIZE+ 1)
(100)PROBEFRAGS = TRUNC(HSIZE[Inner]/OPTBLKSIZE+ 1)
(101)HASHBLKS = BULDFRAGS + PROBEFRAGS
(102)IOCOST[Hash] = CEIL(2*HASHBLKS/(MBRC-1)*(MREADTIM/SREADTIM+MBRC/TRUNC(SAMAXIO/OPTBLKSIZE) - (1+MREADTIM/SREADTIM/TRUNC(SAMAXIO/OPTBLKSIZE))))
(103)IOCOST[HJ]= IOCOST[Outer] + IOCOST[Inner] + IOCOST[Hash]
9.單表並行代價計算
當PPCT為預設值時
(104)IOCOST[Paralle1] = IOCOST[Serial]*10/9/DOP
當PPCT不為預設值時
(105)IOCOST[Paralle1]= GREATEST(0,1-PPCT)*IOCOST[Serial]+ PPCT*IOCOST[Serial]*10/9/DOP
10.兩表關聯並行代價計算
(106)SACL[Outer]= ACL[OuterColl]+...ACL[OuterColn]
(107)SACL[Inner]= ACL[InnerColl]+...ACL[InnerColn]
(108)HSIZE[Outer]= CARD[Outer]*(LEAST(ARL[Outer], SACL[Outer])+12)
(109)HSIZE[Inner]= CARD[Inner]*(LEAST(ARL[Outer], SACL[Inner])+12)
(110)#PTNS = GREATEST( DOP[Outer], DOP[Inner])
(111)MAXHASHAREA= GREATEST(160,SMINIO*4/OPTBLKSIZE,CEIL(SMPXMAX*2/#PTNS/OPTBLKSIZE),SMMAX/(160* 1024))
(112)HASHAREA= CEIL(GREATEST(SMINIO*4/OPTBLKSIZE,MAXHASHAERA*0.2))
(113)當HSIZE[Outer]>HASHAREA*OPTBLKSIZE*#PTNS,說明hash需要寫入磁碟,否則IOCOST[Hash]=0
(114)IOCOST[Outer](Paralle1)= ROUND(IOCOST[Outer](Seria1)*10/9/DOP[Outer])
(115)IOCOST[Inner](Paralle1) = ROUND(IOCOST[Inner](Seria1)*10/9/DOP[Inner])
(116)IOCOST[Hash] = CEIL(2*HASHBLKS/(MBRC-1 )*(MREADTIM/SREADTIM+MBRC/TRUNC(SAMINIO/OPTBLKSIZE)-(1 + MREADTIM/SREADTIM/TRUNC(SAMINIO/OPTBLKSIZE))))
(117)IOCOST[HJ]= IOCOST[Outer](Parallel)+IOCOST[Inner](Parallel)+IOCOST[Hash]
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28539951/viewspace-1655227/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 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
- 【cbo計算公式】單表選擇率(二)公式
- 學習Rust 條件語句Rust
- 【cbo計算公式】CBO基本概念(一)公式
- oracle基於cbo成本計算方式說明Oracle
- CBO成本計算初探
- 草根學Python（五） 條件語句和迴圈語句Python
- 『忘了再學』Shell流程控制 — 34、if條件判斷語句（二）
- Java 數學運算與條件語句全解析Java
- Oracle11gr2 審計語句增強（二）Oracle
- Python學習筆記3（條件語句+迴圈語句）Python筆記
- Python學習筆記（三）——條件語句、迴圈語句Python筆記
- shell程式設計之條件語句程式設計
- Oracle分頁查詢語句（二）Oracle
- 實現四則運算的一條sql語句SQL
- Go 條件語句 - Go 學習記錄Go
- GO 條件語句Go
- javaScript條件語句JavaScript
- oracle之PLSql語言（二）sql語句的使用OracleSQL
- 02 shell程式設計之條件語句程式設計
- Oracle 使用一條insert語句完成多表插入Oracle
- day2-計算符和if語句
- 9，javase程式碼實戰-條件語句——switch語句根據使用者消費金額計算折扣(四)Java
- Swift學習筆記（二十七）——條件語句和Switch-case語句Swift筆記
- c語言基礎語法二——迴圈控制條件語句（完節）C語言
- 【cbo計算公式】No Bind Peeking(五)公式