《查看oracle数据库执行计划》由会员分享，可在线阅读，更多相关《查看oracle数据库执行计划（29页珍藏版）》请在金锄头文库上搜索。
1、为了适应公司新战略的发展，保障停车场安保新项目的正常、顺利开展，特制定安保从业人员的业务技能及个人素质的培训计划查看oracle数据库执行计划如何看懂ORACLE执行计划一、什么是执行计划AnexplainplanisarepresentationoftheaccesspaththatistakenwhenaqueryisexecutedwithinOracle.二、如何访问数据AtthephysicallevelOraclereadsblocksofdata.ThesmallestamountofdatareadisasingleOracleblock,thelargestisconstra
2、inedbyoperatingsystemlimits(andmultiblocki/o).LogicallyOraclefindsthedatatoreadbyusingthefollowingmethods:FullTableScan(FTS)-全表扫描IndexLookup(unique&non-unique)-索引扫描Rowid-物理行id三、执行计划层次关系Whenlookingataplan,therightmost(iemostinndented)uppermostoperationisthefirstthingthatisexecuted.-采用最右最上最先执行的原则看层次关系
3、，在同一级如果某个动作没有子ID就最先执行1.一个简单的例子：SQLselect/*+parallel(e4)*/*fromempe;ExecutionPlan-0SELECTSTATEMENTOptimizer=CHOOSE(Cost=1Card=82Bytes=7134)10TABLEACCESS*(FULL)OFEMP(Cost=1Card=82Bytes=7134):Q5000-:Q5000表示是并行方式1PARALLEL_TO_SERIALSELECT/*+NO_EXPANDROWID(A1)*/A1.EMPNO,A1.ENAME,A1.JOB,A1.MGR,A1.HI优化模式是CH
4、OOSE的情况下，看Cost参数是否有值来决定采用CBO还是RBO：SELECTSTATEMENTCHOOSECost=1234-Cost有值，采用CBOSELECTSTATEMENTCHOOSE-Cost为空，采用RBO(9I是如此显示的)2.层次的父子关系的例子：PARENT1*FIRSTCHILD*FIRSTGRANDCHILD*SECONDCHILDHerethesameprinciplesapply,theFIRSTGRANDCHILDistheinitialoperationthentheFIRSTCHILDfollowedbytheSECONDCHILDandfinallythe
5、PARENTcollatestheoutput.四、例子解说ExecutionPlan-0*SELECTSTATEMENTOptimizer=CHOOSE(Cost=3Card=8Bytes=248)10*HASHJOIN(Cost=3Card=8Bytes=248)21*TABLEACCESS(FULL)OFDEPT(Cost=1Card=3Bytes=36)31*TABLEACCESS(FULL)OFEMP(Cost=1Card=16Bytes=304)左侧的两排数据，前面的是序列号ID，后面的是对应的PID。Ashortenedsummaryofthisis:Executionstart
6、swithID=0:SELECTSTATEMENTbutthisisdependandonitschildobjectsSoitexecutesitsfirstchildstep:ID=1PID=0HASHJOINbutthisisdependandonitschildobjectsSoitexecutesitsfirstchildstep:ID=2PID=1TABLEACCESS(FULL)OFDEPTThenthesecondchildstep:ID=3PID=2TABLEACCESS(FULL)OFEMPRowsarereturnedtotheparentstep(s)untilfini
7、shed五、表访问方式TableScan(FTS)全表扫描InaFTSoperation,thewholetableisreaduptothehighwatermark(HWM).TheHWMmarksthelastblockinthetablethathaseverhaddatawrittentoit.IfyouhavedeletedalltherowsthenyouwillstillreaduptotheHWM.TruncateresetstheHWMbacktothestartofthetable.FTSusesmultiblocki/otoreadtheblocksfromdisk.-
8、全表扫描模式下会读数据到表的高水位线，读取速度依赖于Oracle初始化参数db_block_multiblock_read_count(我觉得应该这样翻译:FTS扫描会使表使用上升到高水位(HWM),HWM标识了表最后写入数据的块,如果你用DELETE删除了所有的数据表仍然处于高水位(HWM),只有用TRUNCATE才能使表回归,FTS使用多IO从磁盘读取数据块).QueryPlan-SELECTSTATEMENTCHOOSECost=1*INDEXUNIQUESCANEMP_I1-如果索引里就找到了所要的数据，就不会再去访问表Lookup索引扫描Thereare5methodsofindex
9、lookup:indexuniquescan-索引唯一扫描Methodforlookingupasinglekeyvalueviaauniqueindex.alwaysreturnsasinglevalue,YoumustsupplyATLEASTtheleadingcolumnoftheindextoaccessdataviatheindex.eg:SQLexplainplanforselectempno,enamefromempwhereempno=10;indexrangescan-索引局部扫描Indexrangescanisamethodforaccessingarangevalues
10、ofaparticularcolumn.ATLEASTtheleadingcolumnoftheindexmustbesuppliedtoaccessdataviatheindex.Canbeusedforrangeoperations(=explainplanforselectmgrfromempwheremgr=5;indexfullscan-索引全局扫描FullindexscansareonlyavailableintheCBOasotherwiseweareunabletodeterminewhetherafullscanwouldbeagoodideaornot.Wechoosean
11、indexFullScanwhenwehavestatisticsthatindicatethatitisgoingtobemoreefficientthanaFulltablescanandasort.ForexamplewemaydoaFullindexscanwhenwedoanunboundedscanofanindexandwantthedatatobeorderedintheindexorder.eg:SQLexplainplanforselectempno,enamefrombig_emporderbyempno,ename;indexfastfullscan-索引快速全局扫描，
12、不带orderby情况下常发生Scansalltheblockintheindex,Rowsarenotreturnedinsortedorder,IntroducedinandrequiresV733_PLANS_ENABLED=TRUEandCBO,maybehintedusingINDEX_FFShint,usesmultiblocki/o,canbeexecutedinparallel,canbeusedtoaccesssecondcolumnofconcatenatedindexes.Thisisbecauseweareselectingalloftheindex.eg:SQLexp
13、lainplanforselectempno,enamefrombig_emp;indexskipscan-索引跳跃扫描，where条件列是非索引的前导列情况下常发生Indexskipscanfindsrowsevenifthecolumnisnottheleadingcolumnofaconcatenatedindex.Itskipsthefirstcolumn(s)duringthesearch.eg:SQLcreateindexi_emponemp(empno,ename);SQLselect/*+index_ss(empi_emp)*/jobfromempwhereename=SMIT
14、H;物理ID扫描Thisisthequickestaccessmethodretrievesthespecifiedblockandextractstherowsitisinterestedin.-Rowid扫描是最快的访问数据方式六、表连接方式七、运算符-排序，很消耗资源Thereareanumberofdifferentoperationsthatpromotesorts:(1)orderbyclauses(2)groupby(3)sortmergejoin-这三个会产生排序运算-过滤，如notin、min函数等容易产生Hasanumberofdifferentmeanings,usedtoindicatepartitionelimination,mayalsoindicateanactualfilterstepwhereonerowsourceisfiltering,another,functionssuchasminmayintroducefilterstepsintoqueryplans.-视图，大都由内联视图产生(可能深入到视图基表)Whenaviewcannotbemergedintothemainqueryyouwilloftenseeaprojectionviewoperation.Thisin
