SQL Tuning Overview.docx
《SQL Tuning Overview.docx》由会员分享，可在线阅读，更多相关《SQL Tuning Overview.docx（27页珍藏版）》请在冰豆网上搜索。
SQLTuningOverview
SQLTuningOverview
Thischapterdiscussesgoalsfortuning,howtoidentifyhigh-resourceSQLstatements,explainswhatshouldbecollected,andprovidestuningsuggestions.
Thischaptercontainsthefollowingsections:
∙IntroductiontoSQLTuning
∙GoalsforTuning
∙IdentifyingHigh-LoadSQL
∙AutomaticSQLTuningFeatures
∙DevelopingEfficientSQLStatements
SeeAlso:
∙OracleDatabaseConceptsforanoverviewofSQL
∙OracleDatabase2DayDBAforinformationonmonitoringandtuningthedatabase
IntroductiontoSQLTuning
AnimportantfacetofdatabasesystemperformancetuningisthetuningofSQLstatements.SQLtuninginvolvesthreebasicsteps:
∙IdentifyinghighloadortopSQLstatementsthatareresponsibleforalargeshareoftheapplicationworkloadandsystemresources,byreviewingpastSQLexecutionhistoryavailableinthesystem.
∙Verifyingthattheexecutionplansproducedbythequeryoptimizerforthesestatementsperformreasonably.
∙ImplementingcorrectiveactionstogeneratebetterexecutionplansforpoorlyperformingSQLstatements.
Thesethreestepsarerepeateduntilthesystemperformancereachesasatisfactorylevelornomorestatementscanbetuned.
GoalsforTuning
Theobjectiveoftuningasystemiseithertoreducetheresponsetimeforendusersofthesystem,ortoreducetheresourcesusedtoprocessthesamework.Youcanaccomplishbothoftheseobjectivesinseveralways:
∙ReducetheWorkload
∙BalancetheWorkload
∙ParallelizetheWorkload
ReducetheWorkload
SQLtuningcommonlyinvolvesfindingmoreefficientwaystoprocessthesameworkload.Itispossibletochangetheexecutionplanofthestatementwithoutalteringthefunctionalitytoreducetheresourceconsumption.
Twoexamplesofhowresourceusagecanbereducedare:
1.Ifacommonlyexecutedqueryneedstoaccessasmallpercentageofdatainthetable,thenitcanbeexecutedmoreefficientlybyusinganindex.Bycreatingsuchanindex,youreducetheamountofresourcesused.
2.Ifauserislookingatthefirsttwentyrowsofthe10,000rowsreturnedinaspecificsortorder,andifthequery（andsortorder）canbesatisfiedbyanindex,thentheuserdoesnotneedtoaccessandsortthe10,000rowstoseethefirst20rows.
BalancetheWorkload
Systemsoftentendtohavepeakusageinthedaytimewhenrealusersareconnectedtothesystem,andlowusageinthenighttime.Ifnoncriticalreportsandbatchjobscanbescheduledtoruninthenighttimeandtheirconcurrencyduringdaytimereduced,thenitfreesupresourcesforthemorecriticalprogramsintheday.
ParallelizetheWorkload
Queriesthataccesslargeamountsofdata（typicaldatawarehousequeries）oftencanbeparallelized.Thisisextremelyusefulforreducingtheresponsetimeinlowconcurrencydatawarehouse.However,forOLTPenvironments,whichtendtobehighconcurrency,thiscanadverselyimpactotherusersbyincreasingtheoverallresourceusageoftheprogram.
IdentifyingHigh-LoadSQL
Thissectiondescribesthestepsinvolvedinidentifyingandgatheringdataonhigh-loadSQLstatements.High-loadSQLarepoorly-performing,resource-intensiveSQLstatementsthatimpacttheperformanceoftheOracledatabase.High-loadSQLstatementscanbeidentifiedby:
∙AutomaticDatabaseDiagnosticMonitor
∙AutomaticWorkloadRepository
∙V$SQLview
∙CustomWorkload
∙SQLTrace
IdentifyingResource-IntensiveSQL
Thefirststepinidentifyingresource-intensiveSQListocategorizetheproblemyouareattemptingtofix:
∙Istheproblemspecifictoasingleprogram（orsmallnumberofprograms）
∙Istheproblemgenericovertheapplication?
TuningaSpecificProgram
Ifyouaretuningaspecificprogram（GUIor3GL）,thenidentifyingtheSQLtoexamineisasimplematteroflookingattheSQLexecutedwithintheprogram.OracleEnterpriseManagerprovidestoolsforidentifyingresourceintensiveSQLstatements,generatingexplainplans,andevaluatingSQLperformance.
SeeAlso:
∙OracleEnterpriseManagerConceptsforinformationaboutthetoolsavailableformonitoringandtuningSQLapplications
∙Chapter 13,"AutomaticSQLTuning"forinformationonautomaticSQLtuningfeatures
IfitisnotpossibletoidentifytheSQL（forexample,theSQLisgenerateddynamically）,thenuseSQL_TRACEtogenerateatracefilethatcontainstheSQLexecuted,thenuseTKPROFtogenerateanoutputfile.
TheSQLstatementsintheTKPROFoutputfilecanbeorderedbyvariousparameters,suchastheexecutionelapsedtime（exeela）,whichusuallyassistsintheidentificationbyorderingtheSQLstatementsbyelapsedtime（withhighestelapsedtimeSQLstatementsatthetopofthefile）.ThismakesthejobofidentifyingthepoorlyperformingSQLeasieriftherearemanySQLstatementsinthefile.
SeeAlso:
Chapter 20,"UsingApplicationTracingTools"
TuninganApplication/ReducingLoad
Ifyourwholeapplicationisperformingsuboptimally,orifyouareattemptingtoreducetheoverallCPUorI/Oloadonthedatabaseserver,thenidentifyingresource-intensiveSQLinvolvesthefollowingsteps:
1.Determinewhichperiodinthedayyouwouldliketoexamine;typicallythisistheapplication'speakprocessingtime.
2.GatheroperatingsystemandOraclestatisticsatthebeginningandendofthatperiod.TheminimumofOraclestatisticsgatheredshouldbefileI/O（V$FILESTAT）,systemstatistics（V$SYSSTAT）,andSQLstatistics（V$SQLAREAorV$SQL,V$SQLTEXT,V$SQL_PLAN,andV$SQL_PLAN_STATISTICS）.
SeeAlso:
Chapter 6,"AutomaticPerformanceDiagnostics"forinformationonhowtouseOracletoolstogatherOracleinstanceperformancedata
1.Usingthedatacollectedinsteptwo,identifytheSQLstatementsusingthemostresources.AgoodwaytoidentifycandidateSQLstatementsistoqueryV$SQLAREA.V$SQLAREAcontainsresourceusageinformationforallSQLstatementsinthesharedpool.ThedatainV$SQLAREAshouldbeorderedbyresourceusage.Themostcommonresourcesare:
∙Buffergets（V$SQLAREA.BUFFER_GETS,forhighCPUusingstatements）
∙Diskreads（V$SQLAREA.DISK_READS,forhighI/Ostatements）
∙Sorts（V$SQLAREA.SORTS,formanysorts）
OnemethodtoidentifywhichSQLstatementsarecreatingthehighestloadistocomparetheresourcesusedbyaSQLstatementtothetotalamountofthatresourceusedintheperiod.ForBUFFER_GETS,divideeachSQLstatement'sBUFFER_GETSbythetotalnumberofbuffergetsduringtheperiod.ThetotalnumberofbuffergetsinthesystemisavailableintheV$SYSSTATtable,forthestatisticsessionlogicalreads.
Similarly,itispossibletoapportionthepercentageofdiskreadsastatementperformsoutofthetotaldiskreadsperformedbythesystembydividingV$SQL_AREA.DISK_READSbythevaluefortheV$SYSSTATstatisticphysicalreads.TheSQLsectionsoftheAutomaticWorkloadRepositoryreportincludethisdata,soyoudonotneedtoperformthepercentagecalculationsmanually.
SeeAlso:
OracleDatabaseReferenceforinformationaboutdynamicperformanceviews
AfteryouhaveidentifiedthecandidateSQLstatements,thenextstageistogatherinformationthatisnecessarytoexaminethestatementsandtunethem.
GatheringDataontheSQLIdentified
IfyouaremostconcernedwithCPU,thenexaminethetopSQLstatementsthatperformedthemostBUFFER_GETSduringthatinterval.Otherwise,startwiththeSQLstatementthatperformedthemostDISK_READS.
InformationtoGatherDuringTuning
Thetuningprocessbeginsbydeterminingthestructureoftheunderlyingtablesandindexes.Theinformationgatheredincludesthefollowing:
1.CompleteSQLtextfromV$SQLTEXT
2.StructureofthetablesreferencedintheSQLstatement,usuallybydescribingthetableinSQL*Plus
3.Definitionsofanyindexes（columns,columnorderings）,andwhethertheindexesareuniqueornonunique
4.Optimizerstatisticsforthesegments（includingthenumberofrowseachtable,selectivityoftheindexcolumns）,includingthedatewhenthesegmentswerelastanalyzed
5.DefinitionsofanyviewsreferredtointheSQLstatement
6.Repeatstepstwo,three,andfourforanytablesreferencedintheviewdefinitionsfoundinstepfive
7.OptimizerplanfortheSQLstatement（eitherfromEXPLAINPLAN,V$SQL_PLAN,ortheTKPROFoutput）
8.AnypreviousoptimizerplansforthatSQLstatement
Note:
ItisimportanttogenerateandreviewexecutionplansforallofthekeySQLstatementsinyourapplication.DoingsoletsyoucomparetheoptimizerexecutionplansofaSQLstatementwhenthestatementperformedwelltotheplanwhenthatthestatementisnotperformingwell.Havingthecomparison,alongwithinformationsuchaschangesindatavolumes,canassistinidentifyingthecauseofperformancedegradation.
AutomaticSQLTuningFeatures
BecausethemanualSQLtuningprocessposesmanychallengestotheapplicationdeveloper,theSQLtuningprocesshasbeenautomatedbytheautomaticSQLTuningmanageabilityfeatures.ThesesfeatureshavebeendesignedtoworkequallywellforOLTPandDataWarehousetypeapplications.SeeChapter 13,"AutomaticSQLTuning".
ADDM
AutomaticDatabaseDiagnosticMonitor（ADDM）analyzestheinformationcollectedbytheAWRforpossibleperformanceproblemswiththeOracledatabase,includinghigh-loadSQLstatements.See"AutomaticDatabaseDiagnosticMonitor".
SQLTuningAdvisor
SQLTuningAdvisorallowsaquickandefficienttechniqueforoptimizingSQLstatementswithoutmodifyinganystatements.See"SQLTuningAdvisor".
SQLTuningSets
WhenmultipleSQLstatementsareusedasinputtoADDMorSQLTuningAdvisor,aSQLTuningSet（STS）isconstructedandstored.TheSTSincludesthesetofSQLstatementsalongwiththeirassociatedexecutionc
升级会员 