oracle 10g performance tuning 02

《oracle 10g performance tuning 02》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。

1、PerformanceTuning:OverviewObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:WriteappropriatetuninggoalsApplythetuningmethodologyBalanceperformanceandsafetytrade-offsIdentifycommontuningproblemsLogaperformanceServiceRequestwithOracleSupportGeneralTuningSessi

2、onTuningsessionshavethesameprocedure:1.Definetheproblemandstatethegoal.2.Collectcurrentstatistics.3.Considersomecommonperformanceerrors.4.Buildatrialsolution.5.Implementandmeasurethechange.6.Decide:“Didthesolutionmeetthegoal?”No?Thengotostep3andrepeat.Yes?Thencreateanewbasel

3、ine.DefiningtheProblemTodiscoveranddefinetheproblem:ListentouserfeedbackCheckthealertlogandtracefilesforerrorsChecktheparameterfileforanydiagnosticorinappropriateparametersettingCheckmemory,I/O,andCPUusage.IdentifyprocesseswithresourceusageanomaliesIdentifyandtuneSQLstatemen

4、tsthatareheavyconsumersofCPUorI/OCollectinstanceandoperatingsystem(OS)statisticsSettingthePriorityChoosetheproblemthathasthegreatestimpact:Analyzesystemperformanceintermsofworkdone(CPUorservicetime)versustimespentwaitingforwork(waittime).Determinewhichcomponentconsumesthegre

5、atestamountoftime.Drilldowntotunethatcomponent,ifappropriate.TuningMethodology:SettingthePriorityExampleTimeModelSystemStatsDB/Inst:ORCLSnaps:1-11->Orderedby%ofDBtimedesc,StatisticnameStatisticTime(s)%ofDBtime---------------------------------------------sqlexecuteelapsedtim

6、e467.077.1DBCPU414.268.4parsetimeelapsed200.533.1hardparseelapsedtime109.018.0…DBtime605.8CommonTuningProblemsThemostcommontuningproblems:SQLstatementsSessionmanagementSharedpoolsizingandcontentionBuffercachesizingandcontentionDatablockcontentionRedologandredobuffertuningUnd

7、otuningI/OissuesLockingissuesADDMTuningSessionADDMtuningsessionhasthesameprocedureasthegeneraltuningsession,butcombinessteps:1.ViewtheADDMreport.A.Collectcurrentstatistics;comparewithpreviousset.B.Comparetoperformanceissuesknowledgebase.C.Definetheproblemandmakerecommendatio

8、ns.2.Reviewrecommendations.D.Buildatrialsolution.3.Implementtherecommendati