oracle数据库新特性之spa.ppt

《oracle数据库新特性之spa.ppt》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、SQLPerformanceAnalyzerObjectivesAftercompletingthislesson,youshouldbeableto:IdentifythebenefitsofusingSQLPerformanceAnalyzerDescribetheSQLPerformanceAnalyzerworkflowphasesUseSQLPerformanceAnalyzertoascertainperformancegainsfollowingadatabasechangeSQLPerformanceAnal

2、yzer:OverviewTargetedusers:DBAs,QAs,applicationdevelopersHelpspredicttheimpactofsystemchangesonSQLworkloadresponsetimeBuildsdifferentversionsofSQLworkloadperformance(thatis,SQLexecutionplansandexecutionstatistics)ExecutesSQLserially(concurrencynothonored)Analyzespe

3、rformancedifferencesOffersfine-grainedperformanceanalysisonindividualSQLIsintegratedwithSQLTuningAdvisortotuneregressionsSQLPerformanceAnalyzer:UseCasesSQLPerformanceAnalyzerisbeneficialinthefollowingusecases:DatabaseupgradesImplementationoftuningrecommendationsSch

4、emachangesStatisticsgatheringDatabaseparameterchangesOSandhardwarechangesUsingSQLPerformanceAnalyzerCaptureSQLworkloadonproduction.TransporttheSQLworkloadtoatestsystem.Build“before-change”performancedata.Makechanges.Build“after-change”performancedata.Compareresults

5、fromsteps3and5.TuneregressedSQL.Step1:CaptureSQLWorkloadDatabaseInstanceProductiondatabaseCursorcacheSQLTuningSet(STS)isusedtostoreSQLworkload.Includes:SQLTextBindvariablesExecutionplansExecutionstatisticsIncrementalcaptureisusedtopopulateSTSfromcursorcacheoverape

6、riodoftime.STS’sfilteringandrankingcapabilitiesfilteroutundesirableSQL.IncrementalcaptureProductiondatabaseTestdatabaseStep2:TransporttoaTestSystemCopySQLTuningSettostagingtable(“pack”).Transportstagingtabletotestsystem(datapump,DBlink,etc).CopySQLTuningSetfromst

7、agingtable(“unpack”).DatabaseinstanceCursorcacheDatabaseinstanceStep3:BuildBeforeChangePerformanceDataBeforechange,SQLperformanceversionistheSQLworkloadperformancebaseline.SQLperformance=executionplans+executionstatisticsTest/executeSQLinSTS:Produceexecutionplansa

8、ndstatistics.ExecuteSQLserially(noconcurrency).EverySQLisexecutedatleasttwice.SkipDDL/DMLeffects.ExplainplanSQLinSTSgeneratesonlySQLplans.Testd