SQL Tuning Overview.docx
- 文档编号:25533969
- 上传时间:2023-06-09
- 格式:DOCX
- 页数:27
- 大小:30.52KB
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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL Tuning Overview