《Oracle執(zhí)行計劃詳細解讀》課件

Oracle執(zhí)行計劃解讀

培訓教師：謝高興

時間：2006.4.27

BEGINOracle執(zhí)行計劃解讀培訓教師：謝高興時間SQL執(zhí)行過程1,解析SQL2,執(zhí)行3,顯示結果集4,轉換字段數(shù)據檢查安全性檢查SQL語法查詢重新書寫創(chuàng)建執(zhí)行計劃捆綁執(zhí)行計劃執(zhí)行執(zhí)行計劃讀取結果集SQL執(zhí)行過程1,解析SQL2,執(zhí)行3,顯示結果集4,轉換字執(zhí)行計劃執(zhí)行計劃:Oracle內部的機器級代碼,決定如何訪問存儲器，得到需要的結果集。執(zhí)行計劃的主要內容：訪問方式，訪問順序。執(zhí)行計劃執(zhí)行計劃:Oracle內部的機器級代碼,決定得到執(zhí)行計劃的方式1.Explain（解釋）2.Autotrace(自動跟蹤)3.其他工具基本格式：explain

plan

set

STATEMENT_ID='testplan'ForSelect…..(Select,insert,update等數(shù)據操作語句均可)基本使用方式：SQL>setautotraceon;(SQLPLUS中使用)得到執(zhí)行計劃的方式1.Explain（解釋）2.Autotr準備：創(chuàng)建Plan_table表createtableplan_table(statement_idvarchar2(30),timestampdate,remarksvarchar2(80),operationvarchar2(30),optionsvarchar2(30),object_nodevarchar2(128),object_ownervarchar2(30),object_namevarchar2(30),object_instanceinteger,object_typevarchar2(30),optimizervarchar2(255),search_columnsinteger,idinteger,parent_idinteger,positioninteger,costinteger,cardinalityinteger,bytesinteger,other_tagvarchar2(255),partition_startvarchar2(255),partition_stopvarchar2(255),partition_idinteger,otherlong,distributionvarchar2(30));準備：創(chuàng)建Plan_table表createtablepAUTOTRACESQL>setautotraceon;SQL>select*fromdual;D-XExecutionPlan----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE10TABLEACCESS(FULL)OF'DUAL'Statistics----------------------------------------------------------0recursivecalls0dbblockgets3consistentgets0physicalreads0redosize372bytessentviaSQL*Nettoclient511bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)1rowsprocessedSQL>統(tǒng)計信息執(zhí)行計劃結果數(shù)據AUTOTRACESQL>setautotraceo使用Explain1,刪除上次解析數(shù)據2,執(zhí)行解析delete

from

plan_table

where

STATEMENT_ID='testplan';explainplansetSTATEMENT_ID='testplan'forselect*fromdual;3,顯示執(zhí)行計劃selectlpad('',5*(level-1))||operationoperation,options,object_name,cost,positionfromplan_tablestartwithid=0andSTATEMENT_ID='testplan'connectbypriorid=parent_id;使用Explain1,刪除上次解析數(shù)據2,執(zhí)行解析delet第一個執(zhí)行計劃對應SQL語句：select*fromdual;執(zhí)行計劃:第一個執(zhí)行計劃對應SQL語句：select*from怎樣看執(zhí)行計劃執(zhí)行計劃其實是一棵樹，層次最深的最先執(zhí)行，層次相同，上面的先執(zhí)行。顯示時已經按照層次縮進，因此從最里面的看起。最后一組就是驅動表。例：驅動表

selectcount(*)

from

prm_adjustpricemain,prm_adjustprice_bdetail

wheremain.cadjpriceid=detail.CADJPRICEIDandmain.DADJPRICEDATE>=

'2004-04-18'

anddetail.CINVENTORYIDin

(selectpk_invmandocfrom

bd_invmandoc

where

bd_invmandoc.SEALFLAG='N');怎樣看執(zhí)行計劃執(zhí)行計劃其實是一棵樹，層次最深的最先執(zhí)行，層次表訪問方式散列獲?。喝頀呙瑁篟OWID訪問：讀取表的每一條記錄，順序地從第一個數(shù)據塊開始知道結尾標志。ROWID包含記錄的數(shù)據塊號和數(shù)據塊中的偏移量。因此它是獲取一條記錄的最快的方法。使用散列算法得到符號關鍵值的來確定記錄所在的數(shù)據塊。它能減少數(shù)據讀入量，但是存在重新定位記錄的問題，只能在靜態(tài)表中使用。表訪問方式散列獲?。喝頀呙瑁篟OWID訪問：讀取表的每一條索引訪問方式快速全索引掃描：索引掃描：從索引中讀取一個或多個ROWID。索引值通常按照升序方式掃描。有些查詢可以不掃描表只掃描索引就能得到期望的查詢結果。所有查詢字段都在索引中指定;查詢返回大于索引所有記錄數(shù)的10%;進行Count(*)操作。Oracle索引索引訪問方式快速全索引掃描：索引掃描：從索引中讀取一個或多個表連接操作11,嵌套循環(huán)連接-NESTEDLOOP兩個表，一個小的內部表和一個外部表一般情況下速度較快，特別是中間結果集非常小的情況下速度快。外部表索引內部表外部表表連接操作11,嵌套循環(huán)連接-NESTEDLOOP兩個表，表連接操作22,散列連接-HASHJOIN兩個表，一個較小的驅動表和一個大表中間結果集非常大的情況下速度較快。Hash_Area_SizeRAM驅動表大表散列訪問ROWIDRAM溢出使用臨時表空間表連接操作22,散列連接-HASHJOIN兩個表，一個較小表連接操作33,排序合并連接-MERGEJOIN兩個表都使用全表掃描，分別進行排序，然后再合并成查詢的結果集。極少情況適合。只有包含兩個表的決大多數(shù)記錄的查詢適合。A表全表掃描B表全表掃描A表排序B表排序輸出結果集合并表連接操作33,排序合并連接-MERGEJOIN兩個表都使Step1:全表掃描對查詢影響最大的就是全表掃描-TableAccessFull.計執(zhí)行劃中所有的全表掃描都值得懷疑.除非是數(shù)據量非常小的表可能引起全表掃描的原因：表上沒有索引沒有WHERE條件對索引字段使用了內置函數(shù)，如To(Dcredate)=‘2005-02-15’Like操作符而參數(shù)以“%”開始表記錄非常少Step1:全表掃描對查詢影響最大的就是全表掃描-TableStep2:驅動表－1驅動表最內層的驅動表是不是能在執(zhí)行過程中得到最小的中間數(shù)據集？例如：查詢銷售訂單明細，涉及三個表，訂單主表，訂單附表,存貨基本檔案表。條件中包含：訂單日期等于某天和存貨編碼等于某個值。方案一：如果以訂單主表為驅動表，可以使用日期上的索引過濾出所有的當天的訂單，然后關聯(lián)附表，最后再關聯(lián)到存貨檔案表，過濾出相應存貨。訂單附表訂單主表存貨檔案表日期索引主表PK索引存貨主鍵索引Step2:驅動表－1驅動表最內層的驅動表是不是能在執(zhí)行過程Step2:驅動表－2方案二：如果以存貨檔案為驅動表，可以使用編碼上的索引過濾出所有存貨，然后關聯(lián)訂單附表，得到所有存貨符合條件的訂單附表記錄，最后再用訂單附表上的主表主鍵關聯(lián)主表，判斷訂單主表上的日期是否符合條件，最后得到結果集。訂單附表訂單主表存貨檔案表編碼索引存貨ID索引主表主鍵索引結論：如果按照存貨去過濾，得到訂單附表上符合條件記錄會較少，認為方案二更優(yōu)。Step2:驅動表－2方案二：如果以存貨檔案為驅動表，可以使Step3:不該建的索引錯誤索引：對于只有少數(shù)一個可能值的列，不應該建索引。如單據狀態(tài)，單據主表上的部門人員，附表上的倉庫，庫存組織等。索引不是越多越好。數(shù)據的插入，刪除和修改都需要維護索引表，也是有成本的。建議建索引的列。所有可能用做查詢條件的日期列,客戶列；附表上的主表主鍵列，存貨列，來源單據列；基本檔案的編碼列等。Step3:不該建的索引錯誤索引：對于只有少數(shù)一個可能值的列Step4:復合索引列順序對復合索引，應將重復值少的列作為先導列例如訂單附表上有來源單據類型(srctype)和來源單據ID(srcid),建立索引的順序應該是srcid,srctype.如果將srctype列作為先導列，可能在查詢時，沒有srcid的條件也使用這個索引掃描，其結果是掃描了大半個表，比全表掃描還慢。Step4:復合索引列順序對復合索引，應將重復值少的列作為先Step5:非最優(yōu)索引對一個表存在多個索引的列作為條件，解析中可能使用了非最優(yōu)索引使用提示，指定使用某個索引(存在SQL兼容問題，不推薦)將索引盡可能多的使用條件使用內置函數(shù)或運算使不想使用的條件列失效。整型數(shù)據列+0字符型加trim函數(shù)例如：在進行調撥的時候需要判斷這個倉庫中此存貨不存在沒有記帳的出庫單

Selectcount(*)fromXXXwherepk_inv=:1andstatus=0這個查詢會用到存貨索引，但是速度還是慢。但是仔細分析會發(fā)現(xiàn)，其實實際運行中沒記帳的出庫單比例很少，如果在存貨索引上再加上狀態(tài)列，可以過濾更少的數(shù)據，速度將大大提高。Step5:非最優(yōu)索引對一個表存在多個索引的列作為條件，解析Step6:數(shù)據非均勻分布對于某些SQL可能存在時快時慢，這個即可能是數(shù)據的分布不均導致舉例：做銷售訂單時，去判斷此客戶是否存在未結算訂單。SQL語句：這個語句在做多數(shù)客戶的時候沒有感覺，但是在做某一兩個客戶時速度奇慢，查其執(zhí)行計劃，也使用了主表上的客戶索引。Select..Fromso_order,so_order_b,..whereso_order.pk=so_order_b.pkandccustomerid=:1and……..最后發(fā)現(xiàn)情況是這樣的：企業(yè)是做批發(fā)業(yè)務的，政策法規(guī)上不允許直接銷售給個人，內部職工需要購買時都是按照統(tǒng)一個特定的客戶開票，因此數(shù)據庫中數(shù)據嚴重不均，此客戶的訂單數(shù)量大于總數(shù)的10%。一進行這樣的查詢就嚴重占用數(shù)據庫資源，導致系統(tǒng)相應速度慢。解決的辦法：這種行為都是現(xiàn)金交易根本不需要進行檢查，查詢的結果永遠是空。程序繞過這個檢查就行了。Step6:數(shù)據非均勻分布對于某些SQL可能存在時快時慢，這Step7:EXIST查詢基本格式例：selectcount(*)

from

prm_adjustpricemain,prm_adjustprice_bdetail

wheremain.cadjpriceid=detail.CADJPRICEIDandmain.cadjpriceidin

('1','2')

and

exists

(select

*

from

bd_invmandoc

where

bd_invmandoc.SEALFLAG='N‘

anddetail.CINVENTORYID=bd_invmandoc.pk_invmandoc);不要將主表的其他條件寫在子查詢中，這樣可能使外層查詢沒有條件而進行全表掃描。如果想按照子查詢中的表作為驅動表進行查詢，需要改造成IN子句。Select..From主表where主表條件andexists(Select*from子表where子表條件and主表關聯(lián)字段＝子表PK)Step7:EXIST查詢基本格式例：selectcoStep8:IN查詢基本格式例：

selectcount(*)

from

prm_adjustpricemain,prm_adjustprice_bdetail

wheremain.cadjpriceid=detail.CADJPRICEIDandmain.cadjpriceidin

('1','2')

anddetail.CINVENTORYIDin

(selectpk_invmandoc

from

bd_invmandoc

where

bd_invmandoc.SEALFLAG='N');如果不是想按照子查詢中的表作為驅動表進行查詢，建議改造成EXIST子句，特別是自查詢存在較多重復數(shù)據時。Select..From主表where主表條件and主表關聯(lián)字段in(Select子表PKfrom子表where子表條件)Step8:IN查詢基本格式例：selectcount(Step9:NOTIN盡量不要使用NOTINNOTIN都可以改造成NOTEXISTSNOTIN中子查詢如果對某個記錄返回空，整個結果集都會為空Step9:NOTIN盡量不要使用NOTINNOTINStep10:視圖視圖不要存在視圖套視圖情況視圖中不要使用Distinct視圖中希望作為條件的關聯(lián)列和結果列，不要進行運算。如月份的加減。不要使用條件太復雜的視圖。Step10:視圖視圖不要存在視圖套視圖情況視圖中不要使用DStepOver當所有的調整都無效時：那就是算法的問題！StepOver當所有的調整都無效時：那就是算法的問題！實例采購計劃查詢時，先查詢出采購計劃，然后查詢執(zhí)行數(shù)據，再追加到采購計劃數(shù)據中。查詢執(zhí)行數(shù)據SQL如下：

select

planbill.cplanbid,sum(

po_planexe.npraysnum),sum……from

(SELECTpo_plan_b.cplanbid,po_plan_h.pk_corp,po_plan_b.cobj1id,po_plan_b.cobj2id,po_plan_b.cobj3id,po_plan_h.dstartdate,po_plan_h.denddate,aa.invclasscodeFROMpo_plan_h,po_plan_b,bd_invclaawherepo_plan_h.cplanhid=po_plan_b.cplanhidandpo_plan_h.dr=0andpo_plan_b.dr=0andaa.pk_invcl(+)=po_plan_b.cobj2idandpo_plan_h.dprocessdate<='2005-04-19')planbill,

po_planexe,

bd_invclinvclass

where

po_planexe.pk_corp=planbill.pk_corpand

po_planexe.dr=

0

and

(

po_planexe.cdeptid=planbill.cobj1idorplanbill.cobj1idis

null)

and

(

po_planexe.cinvmandocid=planbill.cobj3idorplanbill.cobj3idis

null

)

andinvclass.pk_invcl=

po_planexe.cinvclassid

and

((invclass.invclasscodelikeplanbill.invclasscode||'%'

andplanbill.invclasscodeis

not

null

)

orplanbill.cobj2idis

null

)

and

po_planexe.dstartdate>=planbill.dstartdate

and

po_planexe.denddate<=planbill.denddate

group

byplanbill.cplanbid;實例采購計劃查詢時，先查詢出采購計劃，然后查詢執(zhí)行數(shù)據，再追實例-續(xù)1各表數(shù)據關系如下：采購計劃主表采購計劃子表執(zhí)行情況表cobjid1cobjid2cobjid3部門ID存貨分類ID存貨ID存貨分類表實例-續(xù)1各表數(shù)據關系如下：采購計劃主表采購計劃子表執(zhí)行情況實例-續(xù)2查看執(zhí)行計劃如下：－成本優(yōu)化器模式下的計劃，全是全表掃描實例-續(xù)2查看執(zhí)行計劃如下：－成本優(yōu)化器模式下的計劃，全是全實例-續(xù)3第一反應：加規(guī)則提示select

/*+rule*/planbill.cplanbid,sum…….好像有提高，只有一個FullScan了。但是這個計劃執(zhí)行起來更差，原因是：內部驅動表bd_invcl到執(zhí)行計劃表沒有用到任何條件，結果集是兩個表的完全關聯(lián)實例-續(xù)3第一反應：加規(guī)則提示實例-續(xù)4看看內部的子查詢情況SELECT

po_plan_b.cplanbid,…,aa.invclasscode

FROM

po_plan_h,

po_plan_b,

bd_invclaa

where

po_plan_h.cplanhid=

po_plan_b.cplanhidand

po_plan_h.dr=

0

and

po_plan_b.dr=

0

andaa.pk_invcl(

+

)

=

po_plan_b.cobj2id

and

po_plan_h.dprocessdate<=

'2005-04-19'

實例-續(xù)4看看內部的子查詢情況實例-續(xù)5發(fā)現(xiàn)計劃表上相應日期列沒有索引。子表上的主表字段也沒有索引。加上索引。CREATEINDEXPO_PLAN_H_IDX1ONPO_PLAN_H(DPROCESSDATE);CREATEINDEXIDX_PO_PLAN_B_1ONPO_PLAN_B(CPLANHID);內部子查詢已經沒有太大問題。但是整個查詢仍然沒有起色。實例-續(xù)5發(fā)現(xiàn)計劃表上相應日期列沒有索引。子表上的主表字段也實例-續(xù)6在內部也都用上了索引，但是效果仍不好，速度照樣慢.看到執(zhí)行表使用的索引是I_MPP_PLANEXEX_4索引列（DSTARTDATE,DENDDATE）-刪除先關聯(lián)了執(zhí)行表，然后再關聯(lián)最外層的存貨分類表，對數(shù)據沒有起到過濾作用實例-續(xù)6在內部也都用上了索引，但是效果仍不好，速度照樣慢.實例-續(xù)7仔細分析一下外圍關聯(lián)條件：

where

po_planexe.pk_corp=planbill.pk_corpand

po_planexe.dr=

0

and

(

po_planexe.cdeptid=planbill.cobj1idorplanbill.cobj1idis

null)

and

(

po_planexe.cinvmandocid=planbill.cobj3idorplanbill.cobj3idis

null

)

andinvclass.pk_invcl=

po_planexe.cinvclassid

and

((invclass.invclasscodelikeplanbill.invclasscode||'%'

andplanbill.invclasscodeis

not

null

)

orplanbill.cobj2idis

null

)

and

po_planexe.dstartdate>=planbill.dstartdate

and

po_planexe.denddate<=planbill.denddate這里的po_planexe.cdeptid=planbill.cobj1idorplanbill.cobj1idis

null和

po_planexe.cinvmandocid=planbill.cobj3idorplanbill.cobj3idis

null是無法使用執(zhí)行表上的部門和存貨索引。（部門上單獨加索引？？？）采購計劃表上有一個邏輯是：存貨和存貨分類兩個列是互斥的，且必有一個不為空.我們可以使用這個邏輯將條件分開，而不是寫在一起。and((

po_planexe.cinvmandocid=planbill.cobj3idandplanbill.cobj2idis

null

)or(po_planexe.cinvclassid=invclass.pk_invcl…..))但是，對存貨不需要存貨分類表，而對存貨分類因為可以不定義到最末級，因此必須關聯(lián)兩次存貨分類表。最后可行的解決辦法是將兩個查詢UNION達到解決問題實例-續(xù)7仔細分析一下外圍關聯(lián)條件：實例-續(xù)8先按照計劃表上有存貨的進行查詢select

/*+rule*/planbill.cplanbid,…..from

(

SELECT

po_plan_b.cplanbid,

po_plan_h.pk_corp,

FROM

po_plan_h,

po_plan_b

where

po_plan_h.cplanhid=

po_plan_b.cplanhidand

po_plan_h.dr=

0

and

po_plan_b.dr=

0

and

po_plan_h.dprocessdate<=

'2005-04-19'

)planbill,

po_planexe

where

po_planexe.pk_corp=planbill.pk_corpand

po_planexe.dr=

0

and

(

po_planexe.cdeptid=planbill.cobj1idorplanbill.cobj1idis

null)

and

po_planexe.cinvmandocid=planbill.cobj3idandplanbill.cobj2idis

null

and

po_planexe.dstartdate>=planbill.dstartdate

and

po_planexe.denddate<=planbill.denddate

group

byplanbill.cplanbid實例-續(xù)8先按照計劃表上有存貨的進行查詢實例-續(xù)9執(zhí)行計劃如下，看到已經能得到比較優(yōu)化的結果。查詢時間1秒（3305行）實例-續(xù)9執(zhí)行計劃如下，看到已經能得到比較優(yōu)化的結果。查詢時實例-續(xù)10按照存貨分類查詢的呢？按照編碼進行l(wèi)ike查詢已證明比較慢。那么我們可以考慮建立一個存貨分類上下級關系的完全對照表，使用對照表關聯(lián)。CREATE

TABLE

temp_inv2up(pkid NUMBER(10)

NOT

NULL,//主鍵ID用處不大pk_invcl CHAR(20)

NOT

NULL,//子分類pk_upinvcl CHAR(20)

NOT

NULL,//上級分類CONSTRAINTPK_temp_inv2upPRIMARY

KEY

(pkid));CREATE

INDEXtemp_inv2up_idxinvONUAP0318.TEMP_INV2UP(PK_INVCL);CREATE

INDEXTEMP_INV_UPIDXON

TEMP_INV2UP(PK_UPINVCL);create

sequencetemp_invseqstart

with

1;//生成主鍵ID用//按照分類編碼關聯(lián)出上下級對照表數(shù)據insert

into

temp_inv2up(pkid,pk_invcl, pk_upinvcl)

selecttemp_invseq.nextval,bas.pk_invcl,up.pk_invclfrom

bd_invclbas,bd_invclup

wherebas.invclasscodelikeup.invclasscode||'%';實例-續(xù)10按照存貨分類查詢的呢？按照編碼進行l(wèi)ike查詢已實例-續(xù)11按照存貨分類查詢:select

/*+rule*/planbill.cplanbid,sum..

from

(

SELECT

po_plan_b.cplanbid,

po_plan_h.pk_corp,

…

FROM

po_plan_h,

po_plan_b

where

po_plan_h.cplanhid=

po_plan_b.cplanhidand

po_plan_h.dr=

0

and

po_plan_b.dr=

0

and

po_plan_h.dprocessdate<=

'2005-04-19'

)planbill,

po_planexe,

temp_inv2up

where

po_planexe.pk_corp=planbill.pk_corpand

po_planexe.dr=

0

and

(

po_planexe.cdeptid=planbill.cobj1idorplanbill.cobj1idis

null)

andplanbill.cobj3idis

null

and

temp_inv2up.pk_invcl=

po_planexe.cinvclassid

and

temp_inv2up.PK_UPINVCL=planbill.COBJ2ID

and

po_planexe.dstartdate>=planbill.dstartdate

and

po_planexe.denddate<=planbill.denddate

group

byplanbill.cplanbid;實例-續(xù)11按照存貨分類查詢:實例-續(xù)12按照存貨分類查詢的執(zhí)行計劃:效果不錯。實例-續(xù)12按照存貨分類查詢的執(zhí)行計劃:效果不錯。實例-成果按照這種方式優(yōu)化后，分別union前分別加rule提示，查詢時間小于2秒。再去掉規(guī)則提示，讓Oracle按照成本優(yōu)化器解析，執(zhí)行時間0.297秒。代碼業(yè)務邏輯并沒有改變，執(zhí)行結果也相同。?。?！OVER!實例-成果按照這種方式優(yōu)化后，分別union前分別加rule￥1111111111111111111111111111111222222222222222222222222222222222222222222222222222222223333333333333333333333333333333333333333333333333333333333333333333344444＄§|β↓×√㎜ɡ?≥≧ɑ←‰?↓←≠￥θ￥?÷㎝??￥＄§|β↓×√㎜②￥◎…ɡ?≥≧ɑ←‰?ɡ?≥≧￥◎…ɡ?≥≧ɑ←‰?ɡ?≥≧－￥???￥＄§|β↓×√㎜??￥＄§|β↓×√㎜↓←≠￥θ￥?÷㎝②￥◎…ɡ?≥≧ɑ←‰???￥＄§|β↓×√㎜ɡ?≥≧ɑ←‰?↓←≠￥θ￥?÷㎝??￥＄§|β↓×√㎜②￥◎…ɡ?≥≧ɑ←‰?ɡ?≥≧￥◎…ɡ?≥≧ɑ←‰?ɡ?≥≧－￥???￥＄§|β↓×√㎜??￥＄§|β↓×√㎜≧ɑ←‰ɡ?≥←‰???↓←≠￥θ￥?÷㎝②￥◎…ɡ?≥≧ɑ←‰???￥＄§|β↓×√㎜ɡ?≥≧ɑ←‰?↓←≠￥θ￥?÷㎝??￥＄§|β↓×√㎜②￥◎…ɡ?≥≧ɑ←‰?ɡ?≥≧￥◎…ɡ?≥≧ɑ←‰?ɡ?≥≧－￥???￥＄§|β↓×√㎜??￥＄§|β↓×√㎜￥＄§|β↓×√㎜??￥＄§|β↓×√㎜￥＄§|β↓×√㎜??￥＄§|β↓×√㎜↓×√㎜↓←②￥←‰???←‰???←‰???←‰???←‰???←‰??↓←≠￥θ￥?÷㎝②￥◎…ɡ?≥≧ɑ←‰???￥＄§|β↓×√㎜ɡ?≥≧ɑ←‰?↓←≠￥θ￥?÷㎝??￥＄§|β↓×√㎜②￥◎…ɡ?≥≧ɑ←‰?ɡ?≥≧￥◎…ɡ?≥≧ɑ←‰?ɡ?≥≧－￥???￥＄§|β↓×√㎜??￥＄§|β↓×√㎜￥＄§|β↓×√㎜??￥＄§|β↓×√㎜￥＄§|β↓×√㎜??￥＄§|β↓×√㎜↓×√㎜↓←②￥←‰???←‰???←‰???←‰???←‰???←‰???↓←≠￥θ￥?÷㎝②￥◎…ɡ?≥≧ɑ←‰???￥＄§|β↓×√㎜ɡ?≥≧ɑ←‰?↓←≠￥θ￥?÷㎝??￥＄§|β↓×√㎜②￥◎…ɡ?≥≧ɑ←‰?ɡ?≥≧￥◎…ɡ?≥≧ɑ←‰?ɡ?≥≧－￥???￥＄§|β↓×√㎜??￥＄§|β↓×√㎜￥＄§|β↓×√㎜??￥＄§|β↓×√請刪除后使用，謝謝請刪除后使用，謝謝請刪除后使用，謝謝請刪除后使用，謝謝請刪除后使用，謝謝請刪除后使用，謝謝請刪除后使用，謝謝請刪除后使用，謝謝￥1111111111111111111111111111141dskdfibsdkbfndsldkhslhokdhsodhvkskdnvklsoifyoishigibivbisbdvibsidbvoisbvoibsoivbiosbviohsivhkjshivuhiuhfiugruysdbvslkdbvlkbokbvkozbxkjcbjkbvjkbkjvbkjccbvkjbcxzvhoisdbvibsdivbisdvknflksnlkvnsodskdfibsdkbfndsldkhslhokdhsodhvkskdnvklsoifyoishigibivbisbdvibsidbvoisbvoibsoivbiosbviohsivhkjshivuhiuhfiugruysdbvslkdbvlkbokbvkozbxkjcbjkbvjkbkjvbkjccbvkjbcxzvhoisdbvibsdivbisdvknflksnlkvnsodskdfibsdkbfndsldkhslhokdhsodhvkskdnvklsoifyoishigibivbisbdvibsidbvoisbvoibsoivbiosbviohsivhkjshivuhiuhfiugruysdbvslkdbvlkbokbvkozbxkjcbjkbvjkbkjvbkjccbvkjbcxzvhoisdbvibsdivbisdvknflksnlkvnsodskdfibsdkbfndsldkhslhokdhsodhvkskdnvklsoifyoishigibivbisbdvibsidbvoisbvoibsoivbiosbviohsivhkjshivuhiuhfiugruysdbvslkdbvlkbokbvkozbxkjcbjkbvjkbkjvbkjccbvkjbcxzvhoisdbvibsdivbisdvknflksnlkvnsodskdfibsdkbfndsldkhslhokdhsodhvkskdnvklsoifyoishigibivbisbdvibsidbvoisbvoibsoivbiosbviohsivhkjshivuhiuhfiugruysdbvslkdbvlkbokbvkozbxkjcbjkbvjkbkjvbkjccbvkjbcxzvhoisdbvibsdivbisdvknflksnlkvnsoxkbvoxlknvlkxncklvhsiudlknvknsklvnlksnvkjbsijvbksjvkskvbkjsbdvkklsvlnkclnvlknczxklnvlksndkvnksdvlkslkvlkczvlknspivhsojvposvnsknvlksnklhissoiyuregohkenklfvnosihvlmsdlvvnlknxclkvlkscnvlksdnkjvbsdkvklsndlkhsdoihfoisdnlnslkbvoishdovnlkznkjvgsoidnvposdnklvbkjsdhgoivhsonvlsnvhoisnvlnskvoisnvnsdlkhviusshoinvpnksgivhnosnvlkbxslkvnlkbxkvblkcxnvlknxlkvnoidskdfibsdkbfndsldkhslhokdhsodh42dsfdbsy384y982ythb3oibt4oy39y409705923y09y53b2lkboi2y58wy0ehtoibwoify98wy049ywh4b3oiut89u983yf9ivh98y98sv98hv98ys9f698y9v698yv98x98tb98fyd98gyd98h98ds98nt98d8genklgb4klebtlkb5ktkeirh893y89ey698vhkrnelkhgi8eyokbnkdhf98hodfhxvy78fd678t9fdu90gys98y9shihixyv78dfhvifndovhf9f8yv9onvkobkwkjfegiudsfdbsy384y982ythb3oibt4oy39y409705923y09y53b2lkboi2y58wy0ehtoibwoify98wy049ywh4b3oiut89u983yf9ivh98y98sv98hv98ys9f698y9v698yv98x98tb98fyd98gyd98h98ds98nt98d8genklgb4klebtlkb5ktkeirh893y89ey698vhkrnelkhgi8eyokbnkdhf98hodfhxvy78fd678t9fdu90gys98y9shihixyv78dfhvifndovhf9f8yv9onvkobkwkjfegiudsfdbsy384y982ythb3oibt4oy39y409705923y09y53b2lkboi2y58wy0ehtoibwoify98wy049ywh4b3oiut89u983yf9ivh98y98sv98hv98ys9f698y9v698yv98x98tb98fyd98gyd98h98ds98nt98d8gendsfdbsy384y982ythb3oibt4oy39y44345643453425gvmkbmvbmcf45643453425gvmkbmvbmcf45643453425gvmkbmvbmcf45643453425gvmkbmvbmcffgjhghkggggggggggggggggggg45643453425gvmkbmvbmcf45643453425gvmkbmvbmcfgkkkkkkkkkkkkkkkk45643453425gvmkbmvbmcf45643453425gvmkbmvbmcf45643453425gvmkbmvbmcf45643453425gvmkbmvbmcf45643453425gvmkbmvbmcfgkkkkkkkkkkkkkkkkkkkk45643453425gvmkbmvbmcf45643453425gvmkbmvbmcf45643453425gvmkbmvbmcf45643453425gvmkbmvbmcf45643453425gvmkbmvbmcf4564345344dsfdbsy384y982ythb3oibt4oy39y409705923y09y53b2lkboi2y58wy0ehtoibwoify98wy049ywh4b3oiut89u983yf9ivh98y98sv98hv98ys9f698y9v698yv98x98tb98fyd98gyd98h98ds98nt98d8genklgb4klebtlkb5ktkeirh893y89ey698vhkrnelkhgi8eyokbnkdhf98hodfhxvy78fd678t9fdu90gys98y9shihixyv78dfhvifndovhf9f8yv9onvkobkwkjfegiudsfdbsy384y982ythb3oibt4oy39y409705923y09y53b2lkboi2y58wy0ehtoibwoify98wy049ywh4b3oiut89u983yf9ivh98y98sv98hv98ys9f698y9v698yv98x98tb98fyd98gyd98h98ds98nt98d8genklgb4klebtlkb5ktkeirh893y89ey698vhkrnelkhgi8eyokbnkdhf98hodfhxvy78fd678t9fdu90gys98y9shihixyv78dfhvifndovhf9f8yv9onvkobkwkjfegiudsfdbsy384y982ythb3oibt4oy39y409705923y09y53b2lkboi2y58wy0ehtoibwoify98wy049ywh4b3oiut89u983yf9ivh98y98sv98hv98ys9f698y9v698yv98x98tb98fyd98gyd98h98ds98nt98d8gendsfdbsy384y982ythb3oibt4oy39y44556384866666gjfdghmghm56384866666gjfdghmghm

56384866666gjfdghmghm

56384866666gjfdghmghm

56384866666gjfdghmghm

56384866666gjfdghmghm

56384866666gjfdghmghm

56384866666gjfdghmghm

56384866666gjfdghmghm

56384866666gjfdghmghm

56384866666gjfdghmghm

56384866666gjfdghmghm

56384866666gjfdghmghm

56384866666gjfdghmghm56384866666gjfdghmghm56384866666gjfdghmghm56384866666gjfdghmghm56384866666gjfdghmghm56384866666gjfdghmghm56384866666gjfdghmghm56384866666gjfdghmghm56384866666gjfdghmghm56384866666gjfdghmghm56384866666gjfdghmghm56384866646￥1111111111111111111111111111111222222222222222222222222222222222222222222222222222222223333333333333333333333333333333333333333333333333333333333333333333344444＄§|β↓×√㎜ɡ?≥≧ɑ←‰?↓←≠￥θ￥?÷㎝??￥＄§|β↓×√㎜②￥◎…ɡ?≥≧ɑ←‰?ɡ?≥≧￥◎…ɡ?≥≧ɑ←‰?ɡ?≥≧－￥???￥＄§|β↓×√㎜??￥＄§|β↓×√㎜↓←≠￥θ￥?÷㎝②￥◎…ɡ?≥≧ɑ←‰???￥＄§|β↓×√㎜ɡ?≥≧ɑ←‰?↓←≠￥θ￥?÷㎝??￥＄§|β↓×√㎜②￥◎…ɡ?≥≧ɑ←‰?ɡ?≥≧￥◎…ɡ?≥≧ɑ←‰?ɡ?≥≧－￥???￥＄§|β↓×√㎜??￥＄§|β↓×√㎜≧ɑ←‰ɡ?≥←‰???↓←≠￥θ￥?÷㎝②￥◎…ɡ?≥≧ɑ←‰???￥＄§|β↓×√㎜ɡ?≥≧ɑ←‰?↓←≠￥θ￥?÷㎝??￥＄§|β↓×√㎜②￥◎…ɡ?≥≧ɑ←‰?ɡ?≥≧￥◎…ɡ?≥≧ɑ←‰?ɡ?≥≧－￥???￥＄§|β↓×√㎜??￥＄§|β↓×√㎜￥＄§|β↓×√㎜??￥＄§|β↓×√㎜￥＄§|β↓×√㎜??￥＄§|β↓×√㎜↓×√㎜↓←②￥←‰???←‰???←‰???←‰???←‰???←‰??↓←≠￥θ￥?÷㎝②￥◎…ɡ?≥≧ɑ←‰???￥＄§|β↓×√㎜ɡ?≥≧ɑ←‰?↓←≠￥θ￥?÷㎝??￥＄§|β↓×√㎜②￥◎…ɡ?≥≧ɑ←‰?ɡ?≥≧￥◎…ɡ?≥≧ɑ←‰?ɡ?≥≧－￥???￥＄§|β↓×√㎜??￥＄§|β↓×√㎜￥＄§|β↓×√㎜??￥＄§|β↓×√㎜￥＄§|β↓×√㎜??￥＄§|β↓×√㎜↓×√㎜↓←②￥←‰???←‰???←‰???←‰???←‰???←‰???↓←≠￥θ￥?÷㎝②￥◎…ɡ?≥≧ɑ←‰???￥＄§|β↓×√㎜ɡ?≥≧ɑ←‰?↓←≠￥θ￥?÷㎝??￥＄§|β↓×√㎜②￥◎…ɡ?≥≧ɑ←‰?ɡ?≥≧￥◎…ɡ?≥≧ɑ←‰?ɡ?≥≧－￥???￥＄§|β↓×√㎜??￥＄§|β↓×√㎜￥＄§|β↓×√㎜??￥＄§|β↓×√￥1111111111111111111111111111147Oracle執(zhí)行計劃解讀

培訓教師：謝高興

時間：2006.4.27

BEGINOracle執(zhí)行計劃解讀培訓教師：謝高興時間SQL執(zhí)行過程1,解析SQL2,執(zhí)行3,顯示結果集4,轉換字段數(shù)據檢查安全性檢查SQL語法查詢重新書寫創(chuàng)建執(zhí)行計劃捆綁執(zhí)行計劃執(zhí)行執(zhí)行計劃讀取結果集SQL執(zhí)行過程1,解析SQL2,執(zhí)行3,顯示結果集4,轉換字執(zhí)行計劃執(zhí)行計劃:Oracle內部的機器級代碼,決定如何訪問存儲器，得到需要的結果集。執(zhí)行計劃的主要內容：訪問方式，訪問順序。執(zhí)行計劃執(zhí)行計劃:Oracle內部的機器級代碼,決定得到執(zhí)行計劃的方式1.Explain（解釋）2.Autotrace(自動跟蹤)3.其他工具基本格式：explain

plan

set

STATEMENT_ID='testplan'ForSelect…..(Select,insert,update等數(shù)據操作語句均可)基本使用方式：SQL>setautotraceon;(SQLPLUS中使用)得到執(zhí)行計劃的方式1.Explain（解釋）2.Autotr準備：創(chuàng)建Plan_table表createtableplan_table(statement_idvarchar2(30),timestampdate,remarksvarchar2(80),operationvarchar2(30),optionsvarchar2(30),object_nodevarchar2(128),object_ownervarchar2(30),object_namevarchar2(30),object_instanceinteger,object_typevarchar2(30),optimizervarchar2(255),search_columnsinteger,idinteger,parent_idinteger,positioninteger,costinteger,cardinalityinteger,bytesinteger,other_tagvarchar2(255),partition_startvarchar2(255),partition_stopvarchar2(255),partition_idinteger,otherlong,distributionvarchar2(30));準備：創(chuàng)建Plan_table表createtablepAUTOTRACESQL>setautotraceon;SQL>select*fromdual;D-XExecutionPlan----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE10TABLEACCESS(FULL)OF'DUAL'Statistics----------------------------------------------------------0recursivecalls0dbblockgets3consistentgets0physicalreads0redosize372bytessentviaSQL*Nettoclient511bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)1rowsprocessedSQL>統(tǒng)計信息執(zhí)行計劃結果數(shù)據AUTOTRACESQL>setautotraceo使用Explain1,刪除上次解析數(shù)據2,執(zhí)行解析delete

from

plan_table

where

STATEMENT_ID='testplan';explainplansetSTATEMENT_ID='testplan'forselect*fromdual;3,顯示執(zhí)行計劃selectlpad('',5*(level-1))||operationoperation,options,object_name,cost,positionfromplan_tablestartwithid=0andSTATEMENT_ID='testplan'connectbypriorid=parent_id;使用Explain1,刪除上次解析數(shù)據2,執(zhí)行解析delet第一個執(zhí)行計劃對應SQL語句：select*fromdual;執(zhí)行計劃:第一個執(zhí)行計劃對應SQL語句：select*from怎樣看執(zhí)行計劃執(zhí)行計劃其實是一棵樹，層次最深的最先執(zhí)行，層次相同，上面的先執(zhí)行。顯示時已經按照層次縮進，因此從最里面的看起。最后一組就是驅動表。例：驅動表

selectcount(*)

from

prm_adjustpricemain,prm_adjustprice_bdetail

wheremain.cadjpriceid=detail.CADJPRICEIDandmain.DADJPRICEDATE>=

'2004-04-18'

anddetail.CINVENTORYIDin

(selectpk_invmandocfrom

bd_invmandoc

where

bd_invmandoc.SEALFLAG='N');怎樣看執(zhí)行計劃執(zhí)行計劃其實是一棵樹，層次最深的最先執(zhí)行，層次表訪問方式散列獲取：全表掃描：ROWID訪問：讀取表的每一條記錄，順序地從第一個數(shù)據塊開始知道結尾標志。ROWID包含記錄的數(shù)據塊號和數(shù)據塊中的偏移量。因此它是獲取一條記錄的最快的方法。使用散列算法得到符號關鍵值的來確定記錄所在的數(shù)據塊。它能減少數(shù)據讀入量，但是存在重新定位記錄的問題，只能在靜態(tài)表中使用。表訪問方式散列獲?。喝頀呙瑁篟OWID訪問：讀取表的每一條索引訪問方式快速全索引掃描：索引掃描：從索引中讀取一個或多個ROWID。索引值通常按照升序方式掃描。有些查詢可以不掃描表只掃描索引就能得到期望的查詢結果。所有查詢字段都在索引中指定;查詢返回大于索引所有記錄數(shù)的10%;進行Count(*)操作。Oracle索引索引訪問方式快速全索引掃描：索引掃描：從索引中讀取一個或多個表連接操作11,嵌套循環(huán)連接-NESTEDLOOP兩個表，一個小的內部表和一個外部表一般情況下速度較快，特別是中間結果集非常小的情況下速度快。外部表索引內部表外部表表連接操作11,嵌套循環(huán)連接-NESTEDLOOP兩個表，表連接操作22,散列連接-HASHJOIN兩個表，一個較小的驅動表和一個大表中間結果集非常大的情況下速度較快。Hash_Area_SizeRAM驅動表大表散列訪問ROWIDRAM溢出使用臨時表空間表連接操作22,散列連接-HASHJOIN兩個表，一個較小表連接操作33,排序合并連接-MERGEJOIN兩個表都使用全表掃描，分別進行排序，然后再合并成查詢的結果集。極少情況適合。只有包含兩個表的決大多數(shù)記錄的查詢適合。A表全表掃描B表全表掃描A表排序B表排序輸出結果集合并表連接操作33,排序合并連接-MERGEJOIN兩個表都使Step1:全表掃描對查詢影響最大的就是全表掃描-TableAccessFull.計執(zhí)行劃中所有的全表掃描都值得懷疑.除非是數(shù)據量非常小的表可能引起全表掃描的原因：表上沒有索引沒有WHERE條件對索引字段使用了內置函數(shù)，如To(Dcredate)=‘2005-02-15’Like操作符而參數(shù)以“%”開始表記錄非常少Step1:全表掃描對查詢影響最大的就是全表掃描-TableStep2:驅動表－1驅動表最內層的驅動表是不是能在執(zhí)行過程中得到最小的中間數(shù)據集？例如：查詢銷售訂單明細，涉及三個表，訂單主表，訂單附表,存貨基本檔案表。條件中包含：訂單日期等于某天和存貨編碼等于某個值。方案一：如果以訂單主表為驅動表，可以使用日期上的索引過濾出所有的當天的訂單，然后關聯(lián)附表，最后再關聯(lián)到存貨檔案表，過濾出相應存貨。訂單附表訂單主表存貨檔案表日期索引主表PK索引存貨主鍵索引Step2:驅動表－1驅動表最內層的驅動表是不是能在執(zhí)行過程Step2:驅動表－2方案二：如果以存貨檔案為驅動表，可以使用編碼上的索引過濾出所有存貨，然后關聯(lián)訂單附表，得到所有存貨符合條件的訂單附表記錄，最后再用訂單附表上的主表主鍵關聯(lián)主表，判斷訂單主表上的日期是否符合條件，最后得到結果集。訂單附表訂單主表存貨檔案表編碼索引存貨ID索引主表主鍵索引結論：如果按照存貨去過濾，得到訂單附表上符合條件記錄會較少，認為方案二更優(yōu)。Step2:驅動表－2方案二：如果以存貨檔案為驅動表，可以使Step3:不該建的索引錯誤索引：對于只有少數(shù)一個可能值的列，不應該建索引。如單據狀態(tài)，單據主表上的部門人員，附表上的倉庫，庫存組織等。索引不是越多越好。數(shù)據的插入，刪除和修改都需要維護索引表，也是有成本的。建議建索引的列。所有可能用做查詢條件的日期列,客戶列；附表上的主表主鍵列，存貨列，來源單據列；基本檔案的編碼列等。Step3:不該建的索引錯誤索引：對于只有少數(shù)一個可能值的列Step4:復合索引列順序對復合索引，應將重復值少的列作為先導列例如訂單附表上有來源單據類型(srctype)和來源單據ID(srcid),建立索引的順序應該是srcid,srctype.如果將srctype列作為先導列，可能在查詢時，沒有srcid的條件也使用這個索引掃描，其結果是掃描了大半個表，比全表掃描還慢。Step4:復合索引列順序對復合索引，應將重復值少的列作為先Step5:非最優(yōu)索引對一個表存在多個索引的列作為條件，解析中可能使用了非最優(yōu)索引使用提示，指定使用某個索引(存在SQL兼容問題，不推薦)將索引盡可能多的使用條件使用內置函數(shù)或運算使不想使用的條件列失效。整型數(shù)據列+0字符型加trim函數(shù)例如：在進行調撥的時候需要判斷這個倉庫中此存貨不存在沒有記帳的出庫單

Selectcount(*)fromXXXwherepk_inv=:1andstatus=0這個查詢會用到存貨索引，但是速度還是慢。但是仔細分析會發(fā)現(xiàn)，其實實際運行中沒記帳的出庫單比例很少，如果在存貨索引上再加上狀態(tài)列，可以過濾更少的數(shù)據，速度將大大提高。Step5:非最優(yōu)索引對一個表存在多個索引的列作為條件，解析Step6:數(shù)據非均勻分布對于某些SQL可能存在時快時慢，這個即可能是數(shù)據的分布不均導致舉例：做銷售訂單時，去判斷此客戶是否存在未結算訂單。SQL語句：這個語句在做多數(shù)客戶的時候沒有感覺，但是在做某一兩個客戶時速度奇慢，查其執(zhí)行計劃，也使用了主表上的客戶索引。Select..Fromso_order,so_order_b,..whereso_order.pk=so_order_b.pkandccustomerid=:1and……..最后發(fā)現(xiàn)情況是這樣的：企業(yè)是做批發(fā)業(yè)務的，政策法規(guī)上不允許直接銷售給個人，內部職工需要購買時都是按照統(tǒng)一個特定的客戶開票，因此數(shù)據庫中數(shù)據嚴重不均，此客戶的訂單數(shù)量大于總數(shù)的10%。一進行這樣的查詢就嚴重占用數(shù)據庫資源，導致系統(tǒng)相應速度慢。解決的辦法：這種行為都是現(xiàn)金交易根本不需要進行檢查，查詢的結果永遠是空。程序繞過這個檢查就行了。Step6:數(shù)據非均勻分布對于某些SQL可能存在時快時慢，這Step7:EXIST查詢基本格式例：selectcount(*)

from

prm_adjustpricemain,prm_adjustprice_bdetail

wheremain.cadjpriceid=detail.CADJPRICEIDandmain.cadjpriceidin

('1','2')

and

exists

(select

*

from

bd_invmandoc

where

bd_invmandoc.SEALFLAG='N‘

anddetail.CINVENTORYID=bd_invmandoc.pk_invmandoc);不要將主表的其他條件寫在子查詢中，這樣可能使外層查詢沒有條件而進行全表掃描。如果想按照子查詢中的表作為驅動表進行查詢，需要改造成IN子句。Select..From主表where主表條件andexists(Select*from子表where子表條件and主表關聯(lián)字段＝子表PK)Step7:EXIST查詢基本格式例：selectcoStep8:IN查詢基本格式例：

selectcount(*)

from

prm_adjustpricemain,prm_adjustprice_bdetail

wheremain.cadjpriceid=detail.CADJPRICEIDandmain.cadjpriceidin

('1','2')

anddetail.CINVENTORYIDin

(selectpk_invmandoc

from

bd_invmandoc

where

bd_invmandoc.SEALFLAG='N');如果不是想按照子查詢中的表作為驅動表進行查詢，建議改造成EXIST子句，特別是自查詢存在較多重復數(shù)據時。Select..From主表where主表條件and主表關聯(lián)字段in(Select子表PKfrom子表where子表條件)Step8:IN查詢基本格式例：selectcount(Step9:NOTIN盡量不要使用NOTINNOTIN都可以改造成NOTEXISTSNOTIN中子查詢如果對某個記錄返回空，整個結果集都會為空Step9:NOTIN盡量不要使用NOTINNOTINStep10:視圖視圖不要存在視圖套視圖情況視圖中不要使用Distinct視圖中希望作為條件的關聯(lián)列和結果列，不要進行運算。如月份的加減。不要使用條件太復雜的視圖。Step10:視圖視圖不要存在視圖套視圖情況視圖中不要使用DStepOver當所有的調整都無效時：那就是算法的問題！StepOver當所有的調整都無效時：那就是算法的問題！實例采購計劃查詢時，先查詢出采購計劃，然后查詢執(zhí)行數(shù)據，再追加到采購計劃數(shù)據中。查詢執(zhí)行數(shù)據SQL如下：

select

planbill.cplanbid,sum(

po_planexe.npraysnum),sum……from

(SELECTpo_plan_b.cplanbid,po_plan_h.pk_corp,po_plan_b.cobj1id,po_plan_b.cobj2id,po_plan_b.cobj3id,po_plan_h.dstartdate,po_plan_h.denddate,aa.invclasscodeFROMpo_plan_h,po_plan_b,bd_invclaawherepo_plan_h.cplanhid=po_plan_b.cplanhidandpo_plan_h.dr=0andpo_plan_b.dr=0andaa.pk_invcl(+)=po_plan_b.cobj2idandpo_plan_h.dprocessdate<='2005-04-19')planbill,

po_planexe,

bd_invclinvclass

where

po_planexe.pk_corp=planbill.pk_corpand

po_planexe.dr=

0

and

(

po_plane