Using EXPLAIN PLAN.docx
- 文档编号:8194490
- 上传时间:2023-01-29
- 格式:DOCX
- 页数:38
- 大小:37.10KB
Using EXPLAIN PLAN.docx
《Using EXPLAIN PLAN.docx》由会员分享,可在线阅读,更多相关《Using EXPLAIN PLAN.docx(38页珍藏版)》请在冰豆网上搜索。
UsingEXPLAINPLAN
UsingEXPLAINPLAN
Thischapterintroducesexecutionplans,describestheSQLstatementEXPLAINPLAN,andexplainshowtointerpretitsoutput.Thischapteralsoprovidesproceduresformanagingoutlinestocontrolapplicationperformancecharacteristics.
Thischaptercontainsthefollowingsections:
∙UnderstandingEXPLAINPLAN
∙ThePLAN_TABLEOutputTable
∙RunningEXPLAINPLAN
∙DisplayingPLAN_TABLEOutput
∙ReadingEXPLAINPLANOutput
∙ViewingParallelExecutionwithEXPLAINPLAN
∙ViewingBitmapIndexeswithEXPLAINPLAN
∙ViewingPartitionedObjectswithEXPLAINPLAN
∙PLAN_TABLEColumns
SeeAlso:
∙OracleDatabaseSQLReferenceforthesyntaxoftheEXPLAINPLANstatement
∙Chapter 14,"TheQueryOptimizer"
UnderstandingEXPLAINPLAN
TheEXPLAINPLANstatementdisplaysexecutionplanschosenbytheOracleoptimizerforSELECT,UPDATE,INSERT,andDELETEstatements.Astatement'sexecutionplanisthesequenceofoperationsOracleperformstorunthestatement.
Therowsourcetreeisthecoreoftheexecutionplan.Itshowsthefollowinginformation:
∙Anorderingofthetablesreferencedbythestatement
∙Anaccessmethodforeachtablementionedinthestatement
∙Ajoinmethodfortablesaffectedbyjoinoperationsinthestatement
∙Dataoperationslikefilter,sort,oraggregation
Inadditiontotherowsourcetree,theplantablecontainsinformationaboutthefollowing:
∙Optimization,suchasthecostandcardinalityofeachoperation
∙Partitioning,suchasthesetofaccessedpartitions
∙Parallelexecution,suchasthedistributionmethodofjoininputs
TheEXPLAINPLANresultsletyoudeterminewhethertheoptimizerselectsaparticularexecutionplan,suchas,nestedloopsjoin.Italsohelpsyoutounderstandtheoptimizerdecisions,suchaswhytheoptimizerchoseanestedloopsjoininsteadofahashjoin,andletsyouunderstandtheperformanceofaquery.
Note:
OraclePerformanceManagerchartsandOracleSQLAnalyzecanautomaticallycreateanddisplayexplainplansforyou.Formoreinformationonusingexplainplans,seeOracleEnterpriseManagerConcepts.
HowExecutionPlansCanChange
Withthequeryoptimizer,executionplanscananddochangeastheunderlyingoptimizerinputschange.EXPLAINPLANoutputshowshowOraclerunstheSQLstatementwhenthestatementwasexplained.ThiscandifferfromtheplanduringactualexecutionforaSQLstatement,becauseofdifferencesintheexecutionenvironmentandexplainplanenvironment.
Executionplanscandifferduetothefollowing:
∙DifferentSchemas
∙DifferentCosts
DifferentSchemas
∙Theexecutionandexplainplanhappenondifferentdatabases.
∙Theuserexplainingthestatementisdifferentfromtheuserrunningthestatement.Twousersmightbepointingtodifferentobjectsinthesamedatabase,resultingindifferentexecutionplans.
∙Schemachanges(usuallychangesinindexes)betweenthetwooperations.
DifferentCosts
Eveniftheschemasarethesame,theoptimizercanchoosedifferentexecutionplansifthecostsaredifferent.Somefactorsthataffectthecostsincludethefollowing:
∙Datavolumeandstatistics
∙Bindvariabletypesandvalues
∙Initializationparameters-setgloballyoratsessionlevel
MinimizingThrow-Away
Examininganexplainplanletsyoulookforthrow-awayincasessuchasthefollowing:
∙Fullscans
∙Unselectiverangescans
∙Latepredicatefilters
∙Wrongjoinorder
∙Latefilteroperations
Forexample,inthefollowingexplainplan,thelaststepisaveryunselectiverangescanthatisexecuted76563times,accesses11432983rows,throwsaway99%ofthem,andretains76563rows.Whyaccess11432983rowstorealizethatonly76563rowsareneeded?
Example19-1LookingforThrow-AwayinanExplainPlan
RowsExecutionPlan
------------------------------------------------------------
12SORTAGGREGATE
2SORTGROUPBY
76563NESTEDLOOPS
76575NESTEDLOOPS
19TABLEACCESSFULLCN_PAYRUNS_ALL
76570TABLEACCESSBYINDEXROWIDCN_POSTING_DETAILS_ALL
76570INDEXRANGESCAN(objectid178321)
76563TABLEACCESSBYINDEXROWIDCN_PAYMENT_WORKSHEETS_ALL
11432983INDEXRANGESCAN(objectid186024)
LookingBeyondExecutionPlans
Theexecutionplanoperationalonecannotdifferentiatebetweenwell-tunedstatementsandthosethatperformpoorly.Forexample,anEXPLAINPLANoutputthatshowsthatastatementusesanindexdoesnotnecessarilymeanthatthestatementrunsefficiently.Sometimesindexescanbeextremelyinefficient.Inthiscase,youshouldexaminethefollowing:
∙Thecolumnsoftheindexbeingused
∙Theirselectivity(fractionoftablebeingaccessed)
ItisbesttouseEXPLAINPLANtodetermineanaccessplan,andthenlaterprovethatitistheoptimalplanthroughtesting.Whenevaluatingaplan,examinethestatement'sactualresourceconsumption.
UsingV$SQL_PLANViews
InadditiontorunningtheEXPLAINPLANcommandanddisplayingtheplan,youcanusetheV$SQL_PLANviewstodisplaytheexecutionplanofaSQLstatement:
Afterthestatementhasexecuted,youcandisplaytheplanbyqueryingtheV$SQL_PLANview.V$SQL_PLANcontainstheexecutionplanforeverystatementstoredinthecursorcache.ItsdefinitionissimilartothePLAN_TABLE.See"PLAN_TABLEColumns".
TheadvantageofV$SQL_PLANoverEXPLAINPLANisthatyoudonotneedtoknowthecompilationenvironmentthatwasusedtoexecuteaparticularstatement.ForEXPLAINPLAN,youwouldneedtosetupanidenticalenvironmenttogetthesameplanwhenexecutingthestatement.
TheV$SQL_PLAN_STATISTICSviewprovidestheactualexecutionstatisticsforeveryoperationintheplan,suchasthenumberofoutputrowsandelapsedtime.Allstatistics,exceptthenumberofoutputrows,arecumulative.Forexample,thestatisticsforajoinoperationalsoincludesthestatisticsforitstwoinputs.ThestatisticsinV$SQL_PLAN_STATISTICSareavailableforcursorsthathavebeencompiledwiththeSTATISTICS_LEVELinitializationparametersettoALL.
TheV$SQL_PLAN_STATISTICS_ALLviewenablessidebysidecomparisonsoftheestimatesthattheoptimizerprovidesforthenumberofrowsandelapsedtime.ThisviewcombinesbothV$SQL_PLANandV$SQL_PLAN_STATISTICSinformationforeverycursor.
SeeAlso:
∙OracleDatabaseReferenceformoreinformationonV$SQL_PLANviews
∙OracleDatabaseReferenceforinformationontheSTATISTICS_LEVELinitializationparameter
EXPLAINPLANRestrictions
OracledoesnotsupportEXPLAINPLANforstatementsperformingimplicittypeconversionofdatebindvariables.Withbindvariablesingeneral,theEXPLAINPLANoutputmightnotrepresenttherealexecutionplan.
FromthetextofaSQLstatement,TKPROFcannotdeterminethetypesofthebindvariables.ItassumesthatthetypeisCHARACTER,andgivesanerrormessageifthisisnotthecase.YoucanavoidthislimitationbyputtingappropriatetypeconversionsintheSQLstatement.
SeeAlso:
Chapter 20,"UsingApplicationTracingTools"
ThePLAN_TABLEOutputTable
ThePLAN_TABLEisautomaticallycreatedasaglobaltemporarytabletoholdtheoutputofanEXPLAINPLANstatementforallusers.PLAN_TABLEisthedefaultsampleoutputtableintowhichtheEXPLAINPLANstatementinsertsrowsdescribingexecutionplans.See"PLAN_TABLEColumns"foradescriptionofthecolumnsinthetable.
WhileaPLAN_TABLEtableisautomaticallysetupforeachuser,youcanusetheSQLscriptutlxplan.sqltomanuallycreatealocalPLAN_TABLEinyourschema.Theexactnameandlocationofthisscriptdependsonyouroperatingsystem.OnUnix,itislocatedinthe$ORACLE_HOME/rdbms/admindirectory.
Forexample,runthecommandsinExample 19-2fromaSQL*PlussessiontocreatethePLAN_TABLEintheHRschema.
Example19-2CreatingaPLAN_TABLE
CONNECTHR/your_password
@$ORACLE_HOME/rdbms/admin/utlxplan.sql
Tablecreated.
OracleCorporationrecommendsthatyoudropandrebuildyourlocalPLAN_TABLEtableafterupgradingtheversionofthedatabasebecausethecolumnsmightchange.ThiscancausescriptstofailorcauseTKPROFtofail,ifyouarespecifyingthetable.
Ifyouwantanoutputtablewithadifferentname,firstcreatePLAN_TABLEmanuallywiththeutlxplan.sqlscriptandthenrenamethetablewiththeRENAMESQLstatement.Forexample:
RENAMEPLAN_TABLETOmy_plan_table;
RunningEXPLAINPLAN
ToexplainaSQLstatement,usetheEXPLAINPLANFORclauseimmediatelybeforethestatement.Forexample:
EXPLAINPLANFOR
SELECTlast_nameFROMemployees;
ThisexplainstheplanintothePLAN_TABLEtable.YoucanthenselecttheexecutionplanfromPLAN_TABLE.See"DisplayingPLAN_TABLEOutput".
IdentifyingStatementsforEXPLAINPLAN
Withmultiplestatements,youcanspecifyastatementidentifierandusethattoidentifyyourspecificexecutionplan.BeforeusingSETSTATEMENTID,removeanyexistingrowsforthatstatementID.
InExample 19-3,st1isspecifiedasthestatementidentifier:
Example19-3UsingEXPLAINPLANwiththeSTATEMENTIDClause
EXPLAINPLAN
SETSTATEMENT_ID='st1'FOR
SELECTlast_nameFROMemployees;
SpecifyingDifferentTablesforEXPLAINPLAN
YoucanspecifytheINTOclausetospecifyadifferenttable.
Example19-4UsingEXPLAINPLANwiththeINTOClause
EXPLAINPLAN
INTOmy_plan_table
FOR
SELECTlast_nameFROMemployees;
YoucanspecifyastatementIdwhenusingtheINTOclause.
EXPLAINPLAN
SETSTATEMENT_ID='st1'
INTOmy_plan_table
FOR
SELECTlast_nameFROMemployees;
SeeAlso:
OracleDatabaseSQLReferenceforacompletedescriptionofEXPLAINPLANsyntax.
DisplayingPLAN_TABLEOutput
Afteryouhaveexplainedtheplan,usethefollowingSQLscriptsorPL/SQLpackageprovidedbyOracletodisplaythemostrecentplantableoutput:
∙UT
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Using EXPLAIN PLAN
![提示](https://static.bdocx.com/images/bang_tan.gif)