大型数据库应用的性能调优-案例学习

WrittenbyQuestSoftware,Inc.PerformanceTuningforMission-CriticalDatabaseApplicationsAnExampleinPeopleSoft©2009QuestSoftware,Inc.ALLRIGHTSRESERVED.Thisdocumentcontainsproprietaryinformation,protectedbycopyright.Nopartofthisdocumentmaybereproducedortransmittedforanypurposeotherthanthereader'spersonalusewithoutthewrittenpermissionofQuestSoftware,Inc.WARRANTYTheinformationcontainedinthisdocumentissubjecttochangewithoutnotice.QuestSoftwaremakesnowarrantyofanykindwithrespecttothisinformation.QUESTSOFTWARESPECIFICALLYDISCLAIMSTHEIMPLIEDWARRANTYOFTHEMERCHANTABILITYANDFITNESSFORAPARTICULARPURPOSE.QuestSoftwareshallnotbeliableforanydirect,indirect,incidental,consequential,orotherdamageallegedinconnectionwiththefurnishingoruseofthisinformation.TRADEMARKSQuest,QuestSoftware,andtheQuestSoftwarelogoaretrademarksandregisteredtrademarksofQuestSoftware,Inc.intheUnitedStatesofAmericaandothercountries.Othertrademarksandregisteredtrademarksusedinthisdocumentarepropertyoftheirrespectiveowners.WorldHeadquarters:5PolarisWayAlisoViejo,CA92656e-mail:[e-mail]te()forregionalandinternationalofficeinformation.Updated—January26,2008iCONTENTSINTRODUCTION..........................................................................................1PERFORMANCECHALLENGES......................................................................2OPTIMIZINGPEOPLESOFT’SPERFORMANCE...............................................4TuningPeopleSoftthroughSQLOptimization...............................................5TuningPeopleSoftthroughIndexing..........................................................6MAINTAININGOPTIMALPERFORMANCE.....................................................8BESTPRACTICESFORTHEPEOPLESOFTAPPLICATION’SLIFECYCLE..........9CONCLUSION............................................................................................11APPENDIX1.QUESTSQLOPTIMIZER’SAPPROACH...................................121.LocatetheproblematicSQL................................................................122.OptimizetheSQL..............................................................................143.OptimizetheIndexes.........................................................................154.CheckforImpactAnalysis...................................................................165.TrackChangesofSQLExecutionPlans..................................................17ABOUTQUESTSOFTWARE,INC.................................................................19CONTACTINGQUESTSOFTWARE.......................................................................19CONTACTINGQUESTSUPPORT.........................................................................19PerformanceTuningforMission-CriticalDatabaseApplications1INTRODUCTIONThiswhitepaperexplainshowQuestSQLOptimizercanassuretheperformanceofmissioncriticalapplications.AlthoughthispaperfocusesonPeopleSoftandOracle,thesamemethodologycanbeappliedtoimproveperformanceofallmissioncriticalapplicationsonOracle,SQLServer,DB2LUW,andSybaseAdaptiveServer.ToaddresstheperformancechallengesfacedinPeopleSoftapplicationdevelopment,wewillexploretheprocessofperformancetuningfordifferentPeopleSoftcomponents.AndbydemonstratingtheprocessofperformancetuningforPeopleSoftapplications,weillustratehowto“unlock”businessefficienciesthroughtheadoptionofQuestSQLOptimizer’smethodology.Finally,weaddressthefulllifecycleofPeopleSoftapplicationsandprovidethesolutionformaintainingperformancethroughoutthelifeofyourapplication.WhitePaper2PERFORMANCECHALLENGESDesignedforenterprises,PeopleSoftbusinessapplicationsachieveproductivityandefficiencygainsbyautomatingbusinessprocessessuchasHumanResources,Finance,SupplyChain,StudentAdministration,andothers.ThemaingoalsofaPeopleSoftimplementationaretoimproveworkflow,reducecosts,andensurereliableandtimelydatamanagement.AlthoughPeopleSoftdeliverssuperiorfunctionalitytotheendusers,theapplication’splatformindependentarchitecturecancauseperformancedegradationwhendeployed.PeopleSoft’sapplicationsaredevelopedinagenericfashiontoworkonanumberofdifferentvendor’sdatabasessuchasOracle,Sybase,MicrosoftSQLServerandDB2.DataretrievalanddatamodificationfromPeopleSofttothedatabaseisdonethroughgenericSQLstatementsthatarecompatibleacrossallRDBMS.EveryRDBMShasauniquearchitectureandprocessesSQLstatementsindifferentways.Therefore,genericSQLstatementsdonottakeadvantageofanyRDBMSarchitectureandspecificplatformcharacteristics,whichresultsinperformanceinefficiencies.AnotherperformancechallengethatPeopleSoftapplicationshaveisthateveryimplementationresidesinadifferentdatabaseenvironmentwithdifferentdatavolumes,transactionrates,hardwareandsoftwareconfigurations.ThismeansthatnotwoPeopleSoftimplementationsarealike,resultinginuniqueperformancecharacteristics.Forthesereasons,PeopleSoftapplicationsarenottunedformaximumperformanceforeveryimplementationrightoutofthebox.SQLoptimization,indexing,anddatabaseparameterschangesofferthebestopportunitytoimprovedatabaseandapplicationperformance,resultinginconsiderablesavi