oracle数据库新特性之spa

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

1、SQLPerformanceAnalyzerObjectivesAftercompletingthislesson,youshouldbeableto:IdentifythebenefitsofusingSQLPerformanceAnalyzerDescribetheSQLPerformanceAnalyzerworkflowphasesUseSQLPerformanceAnalyzertoascertainperformancegainsfollowingadatabasechangeSQLPerformanceAnaly

2、zer:OverviewTargetedusers:DBAs,QAs,applicationdevelopersHelpspredicttheimpactofsystemchangesonSQLworkloadresponsetimeBuildsdifferentversionsofSQLworkloadperformance(thatis,SQLexecutionplansandexecutionstatistics)ExecutesSQLserially(concurrencynothonored)Analyzesperf

3、ormancedifferencesOffersfine-grainedperformanceanalysisonindividualSQLIsintegratedwithSQLTuningAdvisortotuneregressionsSQLPerformanceAnalyzer:UseCasesSQLPerformanceAnalyzerisbeneficialinthefollowingusecases:DatabaseupgradesImplementationoftuningrecommendationsSchema

4、changesStatisticsgatheringDatabaseparameterchangesOSandhardwarechangesUsingSQLPerformanceAnalyzerCaptureSQLworkloadonproduction.TransporttheSQLworkloadtoatestsystem.Build“before-change”performancedata.Makechanges.Build“after-change”performancedata.Compareresultsfrom

5、steps3and5.TuneregressedSQL.Step1:CaptureSQLWorkloadDatabaseInstanceProductiondatabaseCursorcacheSQLTuningSet(STS)isusedtostoreSQLworkload.Includes:SQLTextBindvariablesExecutionplansExecutionstatisticsIncrementalcaptureisusedtopopulateSTSfromcursorcacheoveraperiodo

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

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

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