关于如何理解Explain Plan的输出.docx
- 文档编号:17935461
- 上传时间:2023-08-05
- 格式:DOCX
- 页数:22
- 大小:22.81KB
关于如何理解Explain Plan的输出.docx
《关于如何理解Explain Plan的输出.docx》由会员分享,可在线阅读,更多相关《关于如何理解Explain Plan的输出.docx(22页珍藏版)》请在冰点文库上搜索。
关于如何理解ExplainPlan的输出
关于如何理解ExplainPlan的输出
关于怎样解释Explain的输出曾经一直是一个困扰我的问题,后来我在Metalink上找到这篇文章,顿时豁然开朗。
希望有同样问题的同志能从这篇文章有所收获,曾经想翻译成中文,但实在没有时间,有心的同志可以试试。
InterpretingExplainplan
1.Backgroundinformation
1.1What'sanexplainplan?
~~~~~~~~~~~~~~~~~~~~~~~
AnexplainplanisarepresentationoftheaccesspaththatistakenwhenaqueryisexecutedwithinOracle.
Queryprocessingcanbedividedinto7phases:
[1]Syntactic-checksthesyntaxofthequery
[2]Semantic-checksthatallobjectsexistandareaccessible
[3]ViewMerging-rewritesqueryasjoinonbasetablesas
opposedtousingviews
[4]StatementTransformation-rewritesquerytransformingsomecomplex
constructsintosimpleroneswhere
appropriate(e.g.subqueryunnesting,in/or
transformation)
[5]Optimization-determinestheoptimalaccesspathforthe
querytotake.WiththeRuleBased
Optimizer(RBO)itusesasetofheuristics
todetermineaccesspath.WiththeCost
BasedOptimizer(CBO)weusestatistics
toanalyzetherelativecostsofaccessing
objects.
[6]QEPGeneration
[7]QEPExecution
(QEP=QueryEvaluationPlan)
Steps[1]-[6]arehandledbytheparser.
Step[7]istheexecutionofthestatement.
Theexplainplanisproducedbytheparser.Oncetheaccesspathhasbeendecideduponitisstoredinthelibrarycachetogetherwiththestatementitself.Westorequeriesinthelibrarycachebaseduponahashedrepresentationofthatquery.Whenlookingforastatementinthelibrarycache,wefirstapplyahashingalgorithmtothestatementandthenwelookforthishashvalueinthelibrarycache.
Thisaccesspathwillbeuseduntilthequeryisreparsed.
1.2Terminology
~~~~~~~~~~~
RowSource-asetofrowsusedinaquery
maybeaselectfromabaseobjectortheresultsetreturnedby
joining2earlierrowsources
Predicate-whereclauseofaquery
Tuples-rows
DrivingTable-Thisistherowsourcethatweusetoseedthequery.
Ifthisreturnsalotofrowsthenthiscanhaveanegative
affectonallsubsequentoperations
ProbedTable-Thisistheobjectwelookupdatainafterwehaveretrieved
relevantkeydatafromthedrivingtable.
1.3HowdoesOracleaccessdata?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
AtthephysicallevelOraclereadsblocksofdata.ThesmallestamountofdatareadisasingleOracleblock,thelargestisconstrainedbyoperatingsystemlimits(andmultiblocki/o).
LogicallyOraclefindsthedatatoreadbyusingthefollowingmethods:
FullTableScan(FTS)
IndexLookup(unique&non-unique)
Rowid
1.4ExplainplanHierarchy
~~~~~~~~~~~~~~~~~~~~~~
Simpleexplainplan:
QueryPlan
-----------------------------------------
SELECTSTATEMENT[CHOOSE]Cost=1234
TABLEACCESSFULLLARGE[:
Q65001][ANALYZED]
Therightmostuppermostoperationofanexplainplanisthefirstthingthattheexplainplanwillexecute.InthiscaseTABLEACCESSFULLLARGEisthefirstoperation.ThisstatementmeanswearedoingafulltablescanoftableLARGE.Whenthisoperationcompletesthentheresultantrowsourceispasseduptothenextlevelofthequeryforprocessing.InthiscaseitistheSELECTSTATEMENTwhichisthetopofthequery.
[CHOOSE]isanindicationoftheoptimizer_goalforthequery.ThisDOESNOTnecessarilyindicatethatplanhasactuallyusedthisgoal.Theonlywaytoconfirmthisistocheckthecost=partoftheexplainplanaswell.ForexamplethefollowingqueryindicatesthattheCBOhasbeenusedbecausethereisacostinthecostfield:
SELECTSTATEMENT[CHOOSE]Cost=1234
HowevertheexplainplanbelowindicatestheuseoftheRBObecausethecostfieldisblank:
SELECTSTATEMENT[CHOOSE]Cost=
Thecostfieldisacomparativecostthatisusedinternallytodeterminethebestcostforparticularplans.Thecostsofdifferentstatementsarenotreallydirectlycomparable.
[:
Q65001]indicatesthatthisparticularpartofthequeryisbeingexecutedinparallel.Thisnumberindicatesthattheoperationwillbeprocessedbyaparallelqueryslaveasopposedtobeingexecutedserially.
[ANALYZED]indicatesthattheobjectinquestionhasbeenanalyzedandtherearecurrentlystatisticsavailablefortheCBOtouse.Thereisnoindicationofthe'level'ofanalysisdone.
2.AccessMethodsindetail
2.1FullTableScan(FTS)
~~~~~~~~~~~~~~~~~~~~~
InaFTSoperation,thewholetableisreaduptothehighwatermark(HWM).TheHWMmarksthelastblockinthetablethathaseverhaddatawrittentoit.IfyouhavedeletedalltherowsthenyouwillstillreaduptotheHWM.TruncateresetstheHWMbacktothestartofthetable.
FTSusesmultiblocki/otoreadtheblocksfromdisk.Multiblocki/oiscontrolledbytheparameter db_block_multiblock_read_count>.Thisdefaultsto: db_block_buffers/((PROCESSES+3)/4) MaximumvaluesareOSdependant BuffersfromFTSoperationsareplacedontheLeastRecentlyUsed(LRU)endofthebuffercachesowillbequicklyagedout.FTSisnotrecommendedforlargetablesunlessyouarereading>5-10%ofit(orso)oryouintendtoruninparallel. ExampleFTSexplainplan: ~~~~~~~~~~~~~~~~~~~~~~~~ SQL>explainplanforselect*fromdual; QueryPlan ----------------------------------------- SELECTSTATEMENT[CHOOSE]Cost= TABLEACCESSFULLDUAL 2.2Indexlookup ~~~~~~~~~~~~ Dataisaccessedbylookingupkeyvaluesinanindexandreturningrowids.Arowiduniquelyidentifiesanindividualrowinaparticulardatablock.Thisblockisreadviasingleblocki/o. Inthisexampleanindexisusedtofindtherelevantrow(s)andthenthetableisaccessedtolookuptheenamecolumn(whichisnotincludedintheindex): SQL>explainplanfor selectempno,enamefromempwhereempno=10; QueryPlan ------------------------------------ SELECTSTATEMENT[CHOOSE]Cost=1 TABLEACCESSBYROWIDEMP[ANALYZED] INDEXUNIQUESCANEMP_I1 Noticethe'TABLEACCESSBYROWID'section.ThisindicatesthatthetabledataisnotbeingaccessedviaaFTSoperationbutratherbyarowidlookup.Inthiscasetherowidhasbeenproducedbylookingupvaluesintheindexfirst. Theindexisbeingaccessedbyan'INDEXUNIQUESCAN'operation.Thisisexplainedbelow.TheindexnameinthiscaseisEMP_I1. Ifalltherequireddataresidesintheindexthenatablelookupmaybeunnecessaryandallyouwillseeisanindexaccesswithnotableaccess. Inthefollowingexampleallthecolumns(empno)areintheindex.Noticethatnotableaccesstakesplace: SQL>explainplanfor selectempnofromempwhereempno=10; QueryPlan ------------------------------------ SELECTSTATEMENT[CHOOSE]Cost=1 INDEXUNIQUESCANEMP_I1 Indexesarepresortedsosortingmaybeunnecessaryifthesortorderrequiredisthesameastheindex. e.g. SQL>explainplanforselectempno,enamefromemp whereempno>7876orderbyempno; QueryPlan -------------------------------------------------------------------------------- SELECTSTATEMENT[CHOOSE]Cost=1 TABLEACCESSBYROWIDEMP[ANALYZED] INDEXRANGESCANEMP_I1[ANALYZED] Inthiscasetheindexissortedsotherrowswillbereturnedintheorderoftheindexhenceasortisunecessary. explainplanfor select/*+Full(emp)*/empno,enamefromemp whereempno>7876orderbyempno; QueryPlan -------------------------------------------------------------------------------- SELECTSTATEMENT[CHOOSE]Cost=9 SORTORDERBY TABLEACCESSFULLEMP[ANALYZED]Cost=1Card=2Bytes=66 BecausewehaveforcedaFTSthedataisunsortedandsowemustsortthedataafterithasbeenretrieved. Thereare4methodsofindexlookup: indexuniquescan indexrangescan indexfullscan indexfastfullscan 2.2.1Indexuniquescan ~~~~~~~~~~~~~~~~~ Methodforlookingupasinglekeyvalueviaauniqueindex.alwaysreturnsasinglevalue.YoumustsupplyATLEASTtheleadingcolumnoftheindextoaccessdataviatheindex,Howeverthismayreturn>1rowastheuniquenesswillnotbeguaranteed. exampleexplainplan: SQL>explainplanfor selectempno,enamefromempwhereempno=10; QueryPlan ------------------------------------ SELECTSTATEMENT[CHOOSE]Cost=1 TABLEACCESSBYROWIDEMP[ANALYZED] INDEXUNIQUESCANEMP_I1 2.2.2Indexrangescan ~~~~~~~~~~~~~~~~ Methodforaccessingmultiplecolumnvalues.YoumustsupplyATLEASTtheleadingcolumnoftheindextoaccessdataviatheindex.Canbeusedforrangeoperations(e.g.><<>>=<=between) e.g. SQL>explainplanforselectempno,enamefromemp whereempno>7876orderbyempno; QueryPlan -------------------------------------------------------------------------------- SELECTSTATEMENT[CHOOSE]Cost=1 TABLEACCESSBYROWIDEMP[ANALYZED] INDEXRANGESCANEMP_I1[ANALYZED] Anon-uniqueindexmayreturnmultiplevaluesforthepredicatecol1=5andwilluseanindexrangescan SQL>explainplanforselectmgrfromempwheremgr=5 Queryplan -------------------- SELECTSTATEMENT[CHOOSE]Cost=1 INDEXRANGESCANEMP_I2[ANALYZED] 2.2.3IndexFullScan ~~~~~~~~~~~~~~~ Incertaincircumstancesitispossibleforthewholeindextobescannedasopposedtoarangescan(i.e.wherenoconstrainingpredicatesareprovidedforatable).FullindexscansareonlyavailableintheCBOasotherwiseweareunabletodeterminewhetherafullscanwouldbeagoodideaornot. WechooseanindexFullScanwhenwehavestatisticsthatindicatethatitisgoingtobemoreefficientthanaFulltablescanandasort. ForexamplewemaydoaFullindexscanwhenwedoanunboundedscanofanindexandwantthedatatobeorderedintheindexorder.TheoptimizermaydecidethatselectingalltheinformationfromtheindexandnotsortingismoreefficientthandoingaFTSoraFastFullIndexScanandthensorting. AnIndexfullscanwillperformsingleblocki/o'sandsoitmayprove
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 关于如何理解Explain Plan的输出 关于 如何 理解 Explain Plan 输出