怎样看懂Oracle的执行计划

怎样看懂Oracle的执行计划一、什么是执行计划AnexplainplanisarepresentationoftheaccesspaththatistakenwhenaqueryisexecutedwithinOracle.二、如何访问数据AtthephysicallevelOraclereadsblocksofdata.ThesmallestamountofdatareadisasingleOracleblock,thelargestisconstrainedbyoperatingsystemlimits(andmultiblocki/o).LogicallyOraclefindsthedatatoreadbyusingthefollowingmethods:FullTableScan(FTS)--全表扫描IndexLookup(unique&non-unique)--索引扫描（唯一和非唯一）Rowid--物理行id三、执行计划层次关系Whenlookingataplan,therightmost(iemostinndented)uppermostoperationisthefirstthingthatisexecuted.--采用最右最上最先执行的原则看层次关系，在同一级如果某个动作没有子ID就最先执行1.看一个简单的例子：QueryPlan-----------------------------------------SELECTSTATEMENT[CHOOSE]Cost=1234**TABLEACCESSFULLLARGE[:Q65001][ANALYZED]--[:Q65001]表示是并行方式，[ANALYZED]表示该对象已经分析过了优化模式是CHOOSE的情况下，看Cost参数是否有值来决定采用CBO还是RBO：SELECTSTATEMENT[CHOOSE]Cost=1234--Cost有值，采用CBOSELECTSTATEMENT[CHOOSE]Cost=--Cost为空，采用RBO2.层次的父子关系，看比较复杂的例子：PARENT1**FIRSTCHILD****FIRSTGRANDCHILD**SECONDCHILDHerethesameprinciplesapply,theFIRSTGRANDCHILDistheinitialoperationthentheFIRSTCHILDfollowedbytheSECONDCHILDandfinallythePARENTcollatestheoutput.四、例子解说ExecutionPlan----------------------------------------------------------0**SELECTSTATEMENTOptimizer=CHOOSE(Cost=3Card=8Bytes=248)10**HASHJOIN(Cost=3Card=8Bytes=248)21****TABLEACCESS(FULL)OF'DEPT'(Cost=1Card=3Bytes=36)31****TABLEACCESS(FULL)OF'EMP'(Cost=1Card=16Bytes=304)左侧的两排数据，前面的是序列号ID，后面的是对应的PID（父ID）。Ashortenedsummaryofthisis:ExecutionstartswithID=0:SELECTSTATEMENTbutthisisdependandonit'schildobjectsSoitexecutesitsfirstchildstep:ID=1PID=0HASHJOINbutthisisdependandonit'schildobjectsSoitexecutesitsfirstchildstep:ID=2PID=1TABLEACCESS(FULL)OF'DEPT'Thenthesecondchildstep:ID=3PID=2TABLEACCESS(FULL)OF'EMP'Rowsarereturnedtotheparentstep(s)untilfinished五、表访问方式1.FullTableScan(FTS)全表扫描InaFTSoperation,thewholetableisreaduptothehighwatermark(HWM).TheHWMmarksthelastblockinthetablethathaseverhaddatawrittentoit.IfyouhavedeletedalltherowsthenyouwillstillreaduptotheHWM.TruncateresetstheHWMbacktothestartofthetable.FTSusesmultiblocki/otoreadtheblocksfromdisk.--全表扫描模式下会读数据到表的高水位线（HWM即表示表曾经扩展的最后一个数据块），读取速度依赖于Oracle初始化参数db_block_multiblock_read_countQueryPlan------------------------------------SELECTSTATEMENT[CHOOSE]Cost=1**INDEXUNIQUESCANEMP_I1--如果索引里就找到了所要的数据，就不会再去访问表了2.IndexLookup索引扫描Thereare5methodsofindexlookup:indexuniquescan--索引唯一扫描Methodforlookingupasinglekeyvalueviaauniqueindex.alwaysreturnsasinglevalue,YoumustsupplyATLEASTtheleadingcolumnoftheindextoaccessdataviatheindex.eg:SQLexplainplanforselectempno,enamefromempwhereempno=10;indexrangescan--索引局部扫描Indexrangescanisamethodforaccessingarangevaluesofaparticularcolumn.ATLEASTtheleadingcolumnoftheindexmustbesuppliedtoaccessdataviatheindex.Canbeusedforrangeoperations(e.g.==between).eg:SQLexplainplanforselectmgrfromempwheremgr=5;indexfullscan--索引全局扫描FullindexscansareonlyavailableintheCBOasotherwiseweareunabletodeterminewhetherafullscanwouldbeagoodideaornot.WechooseanindexFullScanwhenwehavestatisticsthatindicatethatitisgoingtobemoreefficientthanaFulltablescanandasort.ForexamplewemaydoaFullindexscanwhenwedoanunboundedscanofanindexandwantthedatatobeorderedintheindexorder.eg:SQLexplainplanforselectempno,enamefrombig_emporderbyempno,ename;indexfastfullscan--索引快速全局扫描，不带orderby情况下常发生Scansalltheblockintheindex,Rowsarenotreturnedinsortedorder,Introducedin7.3andrequiresV733_PLANS_ENABLED=TRUEandCBO,maybehintedusingINDEX_FFShint,usesmultiblocki/o,canbeexecutedinparallel,canbeusedtoaccesssecondcolumnofconcatenatedindexes.Thisisbecauseweareselectingalloftheindex.eg:SQLexplainplanforselectempno,enamefrombig_emp;indexskipscan--索引跳跃扫描，where条件列是非索引的前导列情况下常发生Indexskipscanfindsrowsevenifthecolumnisnottheleadingcolumnofaconcatenatedindex.Itskipsthefirstcolumn(s)duringthesearch.eg:SQLcreateindexi_emponemp(empno,ename);SQLselect/*+index_ss(empi_emp)*/jobfromempwhereename='SMITH';3.Rowid物理ID扫描Thisisthequickestaccessmethodavailable.Oracleretrievesthespecifiedblockandextractstherowsitisinterestedin.--Rowid扫描是最快的访问数据方式六、表连接方式有三种连接方式：1.SortMergeJoin(SMJ)--由于sort是非常耗资源的，所以这种连接方式要避免RowsareproducedbyRowSource1andarethensortedRowsfromRowSource2arethenproducedandsortedbythesamesortkeyasRowSource1.RowSource1and2areNOTaccessedconcurrently.SQLexplainplanforselect/*+ordered*/e.deptno,d.deptnofromempe,deptdwheree.deptno=d.deptnoorderbye.deptno,d.deptno;QueryPlan-------------------------------------SELECTSTATEMENT[CHOOSE]Cost=17**MERGEJOIN****SORTJOIN******TABLEACCESSFULLEMP[ANALYZED]****SORTJOIN******TABLEACCESSFULLDEPT[ANALYZED]Sortingisanexpensiveoperation,especiallywithlargetables.Becauseofthis,SMJisoftennotaparticularlyefficientjoinmethod.2.NestedLoops(NL)--比较高效的一种连接方式Fetchesthefirstbatchofrowsfromrowsource1,Thenweproberowsource2onceforeachrowreturnedfromrowsource1.Fornestedloopstobeefficientitisimportantthatthefirstrowsourcereturnsasfewrowsaspossibleasthisdirectlycontrolsthenumberofprobesofthesecondrowsource.Alsoithelpsiftheaccessmethodforrowsource2isefficientasthisoperationisbeingrepeatedonceforeveryrowreturnedbyrowsource1.SQL