Oracle英文版培訓課件之SQL Tuning：Les06-RW

ExecutionPlansObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:UsetheEXPLAIN

PLANcommandtoshowhowastatementisprocessedUsetheDBMS_XPLANpackageUsetheAutomaticWorkloadRepositoryQuerytheV$SQL_PLANperformanceviewUsetheSQL*PlusAUTOTRACEsettingtoshowSQLstatementexecutionplansandstatisticsWhatIsanExecutionPlan?AnexecutionplanisasetofstepsthatareperformedbytheoptimizerinexecutingaSQLstatementandperforminganoperation.MethodsforViewingExecutionPlansEXPLAINPLAN

SQLTraceStatspackAutomaticWorkloadRepositoryV$SQL_PLANSQL*PlusAUTOTRACEFullNotesPageUsingExecutionPlansDeterminingthecurrentexecutionplanIdentifyingtheeffectofindexesDeterminingaccesspathsVerifyingtheuseofindexesVerifyingwhichexecutionplanmaybeusedDBMS_XPLANPackage:OverviewTheDBMS_XPLANpackageprovidesaneasywaytodisplaytheoutputfrom:EXPLAINPLANcommandAutomaticWorkloadRepository(AWR)V$SQL_PLANandV$SQL_PLAN_STATISTICS_ALLfixedviewsTheDBMS_XPLANpackagesuppliesthreetablefunctionsthatcanbeusedtoretrieveanddisplaytheexecutionplan:DISPLAYDISPLAY_CURSORDISPLAY_AWRFullNotesPageEXPLAIN

PLANCommandGeneratesanoptimizerexecutionplanStorestheplaninthePLANtableDoesnotexecutethestatementitselfEXPLAIN

PLANCommandSETSTATEMENT_ID

='text'EXPLAINPLANINTOyourplantableFORstatementEXPLAINPLANCommand:ExampleEXPLAINPLANSETSTATEMENT_ID='demo01'FORSELECTe.last_name,d.department_name

FROMhr.employeese,hr.departmentsdWHEREe.department_id=d.department_id;Explained.Note:TheEXPLAINPLANcommanddoesnotactuallyexecutethestatement.EXPLAINPLANCommand:OutputPlanhashvalue:2933537672|Id|Operation|Name|Rows|Bytes|Cost(%CPU||0|SELECTSTATEMENT||106|2862|6(17||1|MERGEJOIN||106|2862|6(17||2|TABLEACCESSBYINDEXROWID|DEPARTMENTS|27|432|2(0||3|INDEXFULLSCAN|DEPT_ID_PK|27||1(0||*4|SORTJOIN||107|1177|4(25||5|TABLEACCESSFULL|EMPLOYEES|107|1177|3(0|PredicateInformation(identifiedbyoperationid):4-access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")18rowsselected.SELECTPLAN_TABLE_OUTPUTFROMTABLE(DBMS_XPLAN.DISPLAY());

ParseTree0SELECTSTATEMENTSORTJOIN12435MERGEJOINFULLTABLESCAN

of

EMPLOYEESTABLEACCESSBYINDEXROWID

of

DEPARTMENTSINDEXFULLSCANDEPT_ID_PKUsingtheV$SQL_PLANViewV$SQL_PLANprovidesawayofexaminingtheexecutionplanforcursorsthatwererecentlyexecuted.InformationinV$SQL_PLANisverysimilartotheoutputofanEXPLAINPLANstatement:EXPLAINPLANshowsatheoreticalplanthatcanbeusedifthisstatementweretobeexecuted.V$SQL_PLANcontainstheactualplanused.V$SQL_PLANColumnsNote:Thisisonlyapartiallistingofthecolumns.HASH_VALUE

ADDRESS

CHILD_NUMBERPOSITIONPARENT_IDIDHashvalueoftheparentstatementinthelibrarycacheObjectnumberofthetableortheindexChildcursornumberusingthisexecutionplanOrderofprocessingforoperationsthatallhavethesamePARENT_ID

IDofthenextexecutionstepthatoperatesontheoutputofthecurrentstepNumberassignedtoeachstepintheexecutionplanQueryingV$SQL_PLANSQL_ID47ju6102uvq5q,childnumber0SELECTe.last_name,d.department_nameFROMhr.employeese,hr.departmentsdWHEREe.department_id=d.department_idPlanhashvalue:2933537672|Id|Operation|Name|Rows|Bytes|Cost(%CPU||0|SELECTSTATEMENT||||6(100||1|MERGEJOIN||106|2862|6(17||2|TABLEACCESSBYINDEXROWID|DEPARTMENTS|27|432|2(0||3|INDEXFULLSCAN|DEPT_ID_PK|27||1(0||*4|SORTJOIN||107|1177|4(25||5|TABLEACCESSFULL|EMPLOYEES|107|1177|3(0|PredicateInformation(identifiedbyoperationid):4-access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")24rowsselected.SELECTPLAN_TABLE_OUTPUTFROM

TABLE(DBMS_XPLAN.DISPLAY_CURSOR('47ju6102uvq5q'));V$SQL_PLAN_STATISTICSViewV$SQL_PLAN_STATISTICSprovidesactualexecutionstatistics.V$SQL_PLAN_STATISTICS_ALLenables

side-by-sidecomparisonsoftheoptimizerestimates.AutomaticWorkloadRepositoryCollects,processes,andmaintainsperformancestatisticsforproblem-detectionandself-tuningpurposesStatisticsinclude:ObjectstatisticsTime-modelstatisticsSomesystemandsessionstatisticsActiveSessionHistory(ASH)statisticsAutomaticallygeneratessnapshotsoftheperformancedataFullNotesPageManagingAWRwithPL/SQLCreatingsnapshotsDroppingsnapshotsManagingsnapshotsettingsFullNotesPageAWRViewsV$ACTIVE_SESSION_HISTORYV$metricviewsDBA_HISTviews:DBA_HIST_ACTIVE_SESS_HISTORY

DBA_HIST_BASELINEDBA_HIST_DATABASE_INSTANCE

DBA_HIST_SNAPSHOT

DBA_HIST_SQL_PLAN

DBA_HIST_WR_CONTROLQueryingtheAWRSELECTPLAN_TABLE_OUTPUTFROMTABLE(DBMS_XPLAN.DISPLAY_AWR('454rug2yva18w'));PLAN_TABLE_OUTPUTSQL_ID454rug2yva18w

select/*example*/*fromhr.employeesnaturaljoinhr.departmentsPlanhashvalue:4179021502|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time||0|SELECTSTATEMENT||||6(100)|||1|HASHJOIN||11|968|6(17)|00:00:01||2|TABLEACCESSFULL|DEPARTMENTS|11|220|2(0)|00:00:01||2|TABLEACCESSFULL|DEPARTMENTS|11|220|2(0)|00:00:01||3|TABLEACCESSFULL|EMPLOYEES|107|7276|3(0)|00:00:01|

FullNotesPageSQL*PlusAUTOTRACEOFFTRACE[ONLY]EXPLAINSTATISTICSSHOWAUTOTRACESETAUTOTRACEONSQL*PlusAUTOTRACE:ExamplesTostarttracingstatementsusingAUTOTRACE:Tohidestatementoutput:Todisplayonlyexecutionplans:ControlthelayoutwithcolumnsettingssetautotraceonsetautotracetraceonlysetautotracetraceonlyexplainSQL*PlusAUTOTRACE:Statisticssetautotracetraceonlystatistics

SELECT*FROMproducts;Statistics1recursivecalls0db