欢迎来到天天文库
浏览记录
ID:52062144
大小:205.00 KB
页数:26页
时间:2020-03-31
《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
此文档下载收益归作者所有