Oracle英文版培訓課件之Performance Tuning：les-02Performance Tuning Overview

PerformanceTuning:OverviewObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:WriteappropriatetuninggoalsApplythetuningmethodologyBalanceperformanceandsafetytrade-offsIdentifycommontuningproblemsLogaperformanceServiceRequestwithOracleSupportGeneralTuningSessionTuningsessionshavethesameprocedure:1. Definetheproblemandstatethegoal.2. Collectcurrentstatistics.3. Considersomecommonperformanceerrors.4. Buildatrialsolution.5. Implementandmeasurethechange.6. Decide:“Didthesolutionmeetthegoal?”No?Thengotostep3andrepeat.Yes?Thencreateanewbaseline.DefiningtheProblemTodiscoveranddefinetheproblem:ListentouserfeedbackCheckthealertlogandtracefilesforerrorsChecktheparameterfileforanydiagnosticorinappropriateparametersettingCheckmemory,I/O,andCPUusage.IdentifyprocesseswithresourceusageanomaliesIdentifyandtuneSQLstatementsthatareheavyconsumersofCPUorI/OCollectinstanceandoperatingsystem(OS)statisticsSettingthePriorityChoosetheproblemthathasthegreatestimpact:Analyzesystemperformanceintermsofworkdone(CPUorservicetime)versustimespentwaitingforwork(waittime).Determinewhichcomponentconsumesthegreatestamountoftime.Drilldowntotunethatcomponent,ifappropriate.TuningMethodology:

SettingthePriorityExampleTimeModelSystemStatsDB/Inst:ORCLSnaps:1-11->Orderedby%ofDBtimedesc,StatisticnameStatisticTime(s)%ofDBtime

sqlexecuteelapsedtime467.077.1DBCPU414.268.4parsetimeelapsed200.533.1hardparseelapsedtime109.018.0…DBtime605.8CommonTuningProblemsThemostcommontuningproblems:SQLstatementsSessionmanagementSharedpoolsizingandcontentionBuffercachesizingandcontentionDatablockcontentionRedologandredobuffertuningUndotuningI/OissuesLockingissuesADDMTuningSessionADDMtuningsessionhasthesameprocedureasthegeneraltuningsession,butcombinessteps:1. ViewtheADDMreport.A. Collectcurrentstatistics;comparewithpreviousset.B. Comparetoperformanceissuesknowledgebase.C. Definetheproblemandmakerecommendations.2. Reviewrecommendations.D. Buildatrialsolution.3. Implementtherecommendation.E. Implementandmeasurethechange.4. ReviewthenextADDMreport.F. Decide:“Didthesolutionmeetthegoal?”EffectiveTuningGoalsEffectivetuninggoalsare:SpecificMeasurableAchievableTuningObjectivesTheobjectivesoftuningare:MinimizingresponsetimeIncreasingthroughputIncreasingloadcapabilitiesDecreasingrecoverytimeDBTime DBTime= DBWaitTime+ DBCPUTimeCPUandWaitTimeTuningDimensionsScalable

applicationPossibly

needsSQL

tuningNeeds

instance/RAC

tuningCPU

timeWait

timeNogainachieved

byadding

CPUs/nodesDBtime=DBCPUtime+DBwaittimeTuningLifeCyclePhasesApplicationlifecyclecanbedividedintodifferentphases:ApplicationdesignanddevelopmentTesting:DatabaseconfigurationDeployment:AddinganewapplicationtoanexistingdatabaseProduction:TroubleshootingandtuningTuningStepsDuringLifeCycle1. Tunethedesign.2. Tunetheapplication.3. Tunememory.4. TuneI/O.5. Tunecontention.6. Tunetheoperatingsystem.ApplicationDesignandDevelopmentTheapplicationcanbetunedeveninthedesignanddevelopmentphasesbybuildingandtuningtestcases.Checknormalizationagainstmajorfunctions.Checkdatastructuresagainstaccesstimes.Lookatpointswhereprocessesareserialized.Tunethemajorreports.Tunethehigh-volumeprocesses.Testing:DatabaseConfigurationThetestingphaseallowstuningatadeeperlevel:Checkphysicallayout.Monitorforresourcecontention.MemoryutilizationLocksDiskhotspotsTestforresourceexhaustion.DeploymentDeploymentof:NewapplicationanddatabaseTakebaseline.Monitorgrowthandperformance.NewapplicationinexistingdatabaseTakebaselinebeforedeployment.Takebaselineafterdeployment.Comparebaselines.ProductionTuningisreactive.Youneedtoknow:Whathaschanged?Whereisthebaseline?CollectingaBaselineSetofStatisticsAbaselinesetofstatisticsisusedto:ProvideasetofstatisticsthatarecollectedwhenthesystemisoperatingwithintheboundssetComparethebaselinestatisticswithcurrentstatisticsCreateahypothesisaboutwhathaschangedon

thesystemPerformanceVersusSafetyTrade-OffsFactorsthataffectperformance:MultiplecontrolfilesMultipleredologmembersinagroupFrequentcheckpointingBackingupdatafilesPerformingarchivingBlockchecksumsNumberofconcurrentusersandtransactionsFilingaPerformanceServiceRequestFileaPerformanceServiceRequest:Istheprobleminstancewideorqueryspecific?Identifytherootcause.ProvideStatspackorAWRreports,andOSstatistics.ProvideRemoteDiagnosticsAgent(RDA)reports.ProvideSQL_TRACEreports.RDAReportMonitoringandTuningTool:OverviewAlertlogTracefilesPerformanceviewsOptimizerstatisticsSQLstatisticsBasestatisticsHistogramsMetricsServicestatisticsASHtkproftrcsessSystemstatisticsSessionstatisticsWaitmodelTimemodelAlertsASHreportsServicesMonitoringandTuningTool: