怎样看懂Oracle的执行计划.docx
- 文档编号:8720838
- 上传时间:2023-02-01
- 格式:DOCX
- 页数:4
- 大小:17.22KB
怎样看懂Oracle的执行计划.docx
《怎样看懂Oracle的执行计划.docx》由会员分享,可在线阅读,更多相关《怎样看懂Oracle的执行计划.docx(4页珍藏版)》请在冰豆网上搜索。
怎样看懂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有值,采用CBO
SELECTSTATEMENT[CHOOSE]Cost=--Cost为空,采用RBO
2.层次的父子关系,看比较复杂的例子:
PARENT1
**FIRSTCHILD
****FIRSTGRANDCHILD
**SECONDCHILD
Herethesameprinciplesapply,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'schildobjects
Soitexecutesitsfirstchildstep:
ID=1PID=0HASHJOINbutthisisdependandonit'schildobjects
Soitexecutesitsfirstchildstep:
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_count
QueryPlan
------------------------------------
SELECTSTATEMENT[CHOOSE]Cost=1
**INDEXUNIQUESCANEMP_I1 --如果索引里就找到了所要的数据,就不会再去访问表了
2.IndexLookup索引扫描
Thereare5methodsofindexlookup:
indexuniquescan --索引唯一扫描
Methodforlookingupasinglekeyvalueviaauniqueindex.alwaysreturnsasinglevalue,YoumustsupplyATLEASTtheleadingcolumnoftheindextoaccessdataviatheindex.
eg:
SQL>explainplanforselectempno,enamefromempwhereempno=10;
indexrangescan --索引局部扫描
Indexrangescanisamethodforaccessingarangevaluesofaparticularcolumn.ATLEASTtheleadingcolumnoftheindexmustbesuppliedtoaccessdataviatheindex.Canbeusedforrangeoperations(e.g.><<>>=<=between).
eg:
SQL>explainplanforselectmgrfromempwheremgr=5;
indexfullscan --索引全局扫描
FullindexscansareonlyavailableintheCBOasotherwiseweareunabletodeterminewhetherafullscanwouldbeagoodideaornot.WechooseanindexFullScanwhenwehavestatisticsthatindicatethatitisgoingtobemoreefficientthanaFulltablescanandasort.ForexamplewemaydoaFullindexscanwhenwedoanunboundedscanofanindexandwantthedatatobeorderedintheindexorder.
eg:
SQL>explainplanforselectempno,enamefrombig_emporderbyempno,ename;
indexfastfullscan --索引快速全局扫描,不带orderby情况下常发生
Scansalltheblockintheindex,Rowsarenotreturnedinsortedorder,Introducedin7.3andrequiresV733_PLANS_ENABLED=TRUEandCBO,maybehintedusingINDEX_FFShint,usesmultiblocki/o,canbeexecutedinparallel,canbeusedtoaccesssecondcolumnofconcatenatedindexes.Thisisbecauseweareselectingalloftheindex.
eg:
SQL>explainplanforselectempno,enamefrombig_emp;
indexskipscan --索引跳跃扫描,where条件列是非索引的前导列情况下常发生
Indexskipscanfindsrowsevenifthecolumnisnottheleadingcolumnofaconcatenatedindex.Itskipsthefirstcolumn(s)duringthesearch.
eg:
SQL>createindexi_emponemp(empno,ename);
SQL>select/*+index_ss(empi_emp)*/jobfromempwhereename='SMITH';
3.Rowid物理ID扫描
Thisisthequickestaccessmethodavailable.Oracleretrievesthespecifiedblockandextractstherowsitisinterestedin.--Rowid扫描是最快的访问数据方式
六、表连接方式
有三种连接方式:
1.SortMergeJoin(SMJ) --由于sort是非常耗资源的,所以这种连接方式要避免
RowsareproducedbyRowSource1andarethensortedRowsfromRowSource2arethenproducedandsortedbythesamesortkeyasRowSource1.RowSource1and2areNOTaccessedconcurrently.
SQL>explainplanfor
select/*+ordered*/e.deptno,d.deptno
fromempe,deptd
wheree.deptno=d.deptno
orderbye.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>explainplanfor
selecta.dname,b.sql
fromdepta,empb
wherea.deptno=b.deptno;
QueryPlan
-------------------------
SELECTSTATEMENT[CHOOSE]Cost=5
**NESTEDLOOPS
****TABLEACCESSFULLDEPT[ANALYZED]
****TABLEACCESSFULLEMP[ANALYZED]
3.HashJoin --最为高效的一种连接方式
Newjointypeintroducedin7.3,MoreefficientintheorythanNL&SMJ,OnlyaccessibleviatheCBO.SmallestrowsourceischosenandusedtobuildahashtableandabitmapThesecondrowsourceishashedandcheckedagainstthehashtablelookingforjoins.Thebitmapisusedasaquicklookuptocheckifrowsareinthehashtableandareespeciallyusefulwhenthehashtableistoolargetofitinmemory.
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 怎样 看懂 Oracle 执行 计划