SQL Performance Analyzer: Performance - Oracle

SQL Performance Analyzer:Eliminating the Guesswork from SQLPerformancePrabhaker Gongloor (GP)Khaled YagoubPete BelknapDatabase Manageability GroupOracle Corporation

Outline MotivationSQL Performance Analyzer (SPA) OverviewSPA for Pre-11g Database ReleasesNew!Usage Scenarios Upgrade testing:9i R2, 10g R1, 10g R2 10g R2 OR 11g Any change that impacts SQL performance Real-world Deployments ConclusionPlease visit us at the OOW DemogroundsBooth Moscone West: L52/L51 : DatabaseReplay/SPA

Why SQL Performance Analyzer (SPA)? Businesses want systems that are performant andmeet SLA’s SQL performance regressions are #1 cause of poorsystem performance Solution for proactively detecting all SQL regressionsresulting from changes not available DBA’s use ineffective and time-consuming manualscripts to identify problemsSPA identifies all changes in SQL performance beforeimpacting users

Real Application Testing Real Application Testing SPA Database Replay SPA Database Replay are complementary Together, provide comprehensive & flexible testing solution SQLSQLPerformancePerformanceAnalyzerAnalyzer LSQLresponseresponsetimetime outwithoutconcurrencyconcurrency x,index,etc.etc. CanCanuseuseforforupgradesupgradesfromfrom 9.2/10.19.2/10.1 10.210.2oror11g11g10.210.2 10.210.2oror11g11g DatabaseDatabase ReplayReplay PredictsPredicts impactimpact ofof changechange ononworkloadworkload throughputthroughput AssessAssess changechange byby replayingreplayingworkloadworkload withwith concurrencyconcurrency ComprehensiveComprehensive testingtesting ofof DBDBstack,stack, e.g,e.g, memory,memory, RAC,RAC,concurrencyconcurrency relatedrelated CanCan useuse forfor upgradesupgrades fromfrom 9.29.2 oror 10.210.2 11g11g

SQL PerformanceAnalyzer (SPA)

SPA : Common Usage Scenarios Database upgrades and patch-set releases 9.2/10.1 10.2 or 11.1 releases 10.2.0.x 10.2.0.y or 11.1 releases Database parameter changes Schema changes Optimizer statistics refresh Implementation of tuning recommendations I/O subsystem changesSPA can be used for any change that affects SQLexecution plan and performance

SQL Performance Analyzer: Overview Helps users predict the impact ofsystem changes on SQL workloadresponse time Builds different trials (experiment)of SQL workload performance(i.e., SQL execution plans andstatistics) SQL WorkloadSQL plans statsPre-change TrialAnalyzes performance differencesCompare Offers fine-grained performanceanalysis on individual SQL Integrated with SQL tuning set,SQL plan baselines, and SQLtuning advisor to form an End-toend solutionSQL PerformanceAnalysis ReportSQL plans statsPost-change Trial

SQL Performance Analyzer Generic WorkflowProduction DatabaseTest DatabaseSQL Tuning SetFetch Next SQLTest ExecuteCursor CacheFtpExport/ImportIncremental Capture*SQL Tuning SetExecution Plan &StatisticseChangerofBeTrialCompareSQL Performance*Negligible Overhead , 1- 2%geChanAfterTrial

SPA Report32514

SPA ReportRegressed SQL Statements

SPA Enterprise Manager Interface Rich GUI through Enterprise Manager DBMS SQLPA package PL/SQL API

SPA Usage Scenarios

Database Upgrade: 9.2/10.1* 10.2Scenario 1:I have heard premier DB support for 9.2 hasended, I want to upgrade 10.2 database release. Howcan I use 11g SPA functionality to accomplish theupgrade?Goal:Assess impact of upgrade on SQL workloadperformance on a test system using SPA so that thereare no surprises after upgrade.* For 10.1, the solution is similar to 9.2

Scenario 1: Database Upgrade: 9.2/10.1 10.2System SetupTest DatabaseProduction DatabaseProd DB (9.2/10.1)No patchesneededUpgradeTest DB (10.2)Clone of Production10.2 system Test Execpatch installed11g Test System: No applicationschema/data, stores SPA results11.1.0.7 or 11.1.0.6 patch11g SPA SystemMetalink Note: 560977.1

Scenario 1: Database Upgrade: 9.2/10.1 10.2SPA EnhancementsTest DatabaseProduction DatabaseProd DB (9.2/10.1)UpgradeTest DB (10.2)2. Remote Test ExecuteWorkload over DB Link1. Consume 9i SQLTrace files andgenerate STS11g SPA System

Scenario 1: Database Upgrade: 9.2/10.1 10.2Test DatabaseProduction DatabaseProd DB (9.2/10.1)UpgradeSend SQL forRemoteExecution1. Capture SQLworkload usingSQL*TraceTest DB (10.2)Collectexecutionstats2. Generate STS from trace file3. Generate Pre-Change TrialMove trace file to11g SPA system4. Send SQL to 10.2 DB to execute11g SPA System5. Compare performance andgenerate SPA report

Scenario 1: Database Upgrade: 9.2/10.1 10.2Test DatabaseProduction DatabaseProd DB (9.2/10.1)Test DB (10.2)UpgradeSend SQL forRemoteExecutionCollectexecutionstats1. Capture SQLworkload usingSQL*Trace2. Generate STS from trace fileMove trace file to11g SPA system3. Send SQL to 10.2 DB to execute11g SPA System4. Compare performance andgenerate SPA report

Scenario 1: Database Upgrade: 9.2/10.1 10.2Step 1: Capture SQL workload using SQL Trace Identify all interesting workloads such as month-end, daily peak,etc. Capture SQL trace for the workload, few sessions at a time Use dbms support/dbms monitor package, these support bind value capture tracing other running sessions SQL trace considerations time statistics true: Important for performance datauser dump destmax dump file sizetrace file identifierPerformance overhead: 10-15% for traced sessions

Scenario 1: Database Upgrade: 9.2/10.1 10.2Step 1: Capture SQL workload using SQL Trace (contd.) SQL Trace files only have object identifiers Create mapping table to map object identifiers intrace files to schema namesIdOwnerName123SCOTTEMP124SCOTTDEPTSQL trace files* SQL in Note pages/OTN

Scenario 1: Database Upgrade: 9.2/10.1 10.2Step 2: Transport Workload and Create STS11g SPA SystemProduction DatabaseSQL trace mportSQL trace filesIdOwnerName123SCOTTEMP124SCOTTDEPTMapping TableMapping TableSQL Tuning Set Transport SQL trace files, mapping table using ftp/expdp/impdp., etc.Create STS from trace files using dbms sqltune API (SQL in Notespage/OTN) Specify directory object containing trace files, mapping table, STS nameas input

Scenario 1: Database Upgrade: 9.2/10.1 10.2Step 3a: Create SPA Task Create SPA task on 11g SPA System

Scenario 1: Database Upgrade: 9.2/10.1 10.2Step 3a: Create SPA Task

Scenario 1: Database Upgrade: 9.2/10.1 10.2Step 3b: Establish “Before Change” Trial Trial Creation Method: Select “Build From SQL Tuning Set”option to use plans and statistics from 9i

Scenario 1: Database Upgrade: 9.2/10.1 10.2Step 4: Establish “After Change” Trial

Scenario 1: Database Upgrade: 9.2/10.1 10.2Test DatabaseProduction DatabaseProd DB (9.2/10.1)Test DB (10.2)UpgradeSend SQL forRemoteExecutionCollectexecutionstats1. Capture SQLworkload usingSQL*Trace2. Generate STS from trace fileMove trace file to11g SPA system3. Send SQL to 10.2 DB to execute11g SPA System4. Compare performance andgenerate SPA report

Scenario 1: Database Upgrade: 9.2/10.1 10.2Step 5: Compare and Generate Report Compare Pre-Change and After-Change Trials basedon a performance metric Oracle recommends using CPU TIME and BUFFER GETSmetrics Use multiple metrics that provide repeatable andcomprehensive statistics

Fixing Regressed SQL Systematic problems Check un-analyzed tables, PGA memory, statistics collection,system statistics Refer “Upgrading from Oracle 9i to 10g: What to expect from theOptimizer” on OTN For statements suffering from isolated problems use one ofthe following fixes SQL Profiles: Implement Profiles recommended by SQL TuningAdvisor (STA) Stored Outlines**: If no profile was recommended by STA, thencapture Stored Outlines in 9i for the targeted SQL statements.Import stored outline into 10g.

Database Upgrade: 10.2.0.x 10.2.0.yScenario 2:One of the database I’m managing is on10.2.0.2. How can I use 11g SPA functionality toaccomplish an 10.2.0.4 patchset upgrade?Goal:Assess impact of upgrade on SQL workloadperformance using SPA so that there are no surprisesafter upgrade.

Scenario 2: Database Upgrade: 10.2.0.x 10.2.0.ySystem SetupTest DatabaseProduction DatabaseProd DB (10.2.0.x)UpgradeTest DB (10.2.0.y)UpgradeTest DB (10.2.0.x)No patchesneeded10.2.0.x: Clone of Prod10.2 systems Test Execpatch installed11g Test System: No applicationschema/data, stores SPA results11.1.0.7 or 11.1.0.6 patch11g SPA SystemMetalink Note: 560977.1

Scenario 1: Database Upgrade: 10.2.0.x 10.2.0.yWorkflowProduction DatabaseProd DB (10.2.0.x)Test DatabaseUpgradeTest DB (10.2.0.y)UpgradeTest DB (10.2.0.x)Send SQL forRemoteExecution1. Capture SQLworkload to STSusing IncrementCursor CacheCaptureCollectexecutionstats2. Import STSMove STS dmpfile to 11g SPAsystem11g SPA System3. Send SQL for test execution on10.2 before and after upgrade4. Compare performance andgenerate SPA report

Scenario 3: Using SPA Functionalityfor 9i/10g 11g Upgrades Similar workflow as Scenario 2 Use 11g SPA system and test execute on 10g/11gsource and destination target databases Stores results of experiments separately Allows use of latest releases for 11g SPA system

Schema ChangesScenario 4:A few months after upgrade, the databaseengineering team wants to add few indexes on theproduction system. They want my input on how it willimpact the database and applicationGoal:Assess impact of schema changes on SQLworkload on test system performance & make sure areno negative effects of the change

Schema Changes

Schema ChangesChange Accepted1

Evaluating Optimizer Statistics RefreshScenario 5:Can I use SPA to check if any SQL statementsregressed due to optimizer statistics refresh on my 10.2production databases. If so, how can I evaluate therefreshed optimizer statistics?Goal:Assess impact of optimizer statistics gatheringon SQL workload performance on production system &make sure are no negative effects of the change

Evaluating Optimizer Statistics Refresh Assumptions Optimizer has already gathered statistics on the database Statistics refreshed periodically No prod copy is available on test Use “11g SPA system” to evaluate optimizer statistics on 10.2production database Remote test execute before/after statistics refresh Analyze SPA report and take appropriate action Overall improvement but few SQL regressions Solution: Use SQL Profiles for regressed SQL No improvement and many regressions Solution: Revert to old statistics: Use optimizer statisticsretention/history feature For Oracle Database 11g, use publish pending statistics feature topublish statistics after evaluation of statistics

Evaluating Optimizer Statistics Refresh for 10.2Production DatabaseProd DB (10.2)11g SPA SystemOpt Statistics RefreshProd DB (10.2)1. Capture SQLworkload toSTS2. Import STS3. Use SPA to detect planchanges4. Compare performance andgenerate SPA report5. Retain new statistics orrevert to old statistics or useSQL Profiles for regressiontuning

SPA Real-World CaseStudies

Case Study 1:Large Hotel ChainChallengeSolutionApproachBenefit Upgrade critical customer-facing application providing ratesfor room reservations from Oracle Database 10.2.0.4 to 11.1 Highly volatile data where plan stability is critical Unsuccessfully used synthetic queries to test previousupgrades SQL Performance Analyzer to identify SQL regressions SQL Profiles to tune SQL transparently SQL Plan Baselines for plan stability Very successful upgrade. No surprises! Predictable performance and SLAs Reduced testing time from 5 months to 10 days

Large Hotel ChainProblems Resolved Issues Prior to Upgrade Volatile nature of data sometimes produced inefficient queries basedon stale stats. Impossible to maintain good statistics due to the nature of data load– frequent and widely changing Bind variables didn't always help Outlines hurt in some cases due to nature of the data values After Oracle Database 11g Upgrade: No performance problems noticed Predictable query performance and plan stability due to PlanBaselines Provided a history of plan changes Allowed a more efficient usage of bind variables due to adaptivecursor sharing