Database Administrators spend countless hours troubleshooting SQL statements that have taken upon a new life after a simple system or environment change. Oracle 11G SQL Performance Analyzer provides a solution to this never ending regressive statement issues. This new feature of Oracle 11G allows the database administrator to get a granular view of the impact to sql execution plans and statistics due to environment changes and the best part is that it completely automates the impact analysis process of extremely large SQL workloads

Oracle 11G SQL Performance Analyzer (SPA)

Capture the SQL Workload on the Oracle 11G production system as a SQL Tuning set is accomplished from several sources including the cursor cache, AWR repository and then this SQL Tuning Set also referred to as an STS is transferred to a test system where the analysis will take place.

Baselining the oracle 11g database performance through measurement of the workload is the critical next step. This is accomplished by executing the SQL Performance Analyzer on the SQL tuning set that was captured from production in the last step

Database Administrator can now make the required changes including upgrades, schema changes, patches or optimizer statistics refresh that would normally be a risky step due to the unknown performance of the workload post upgrade.

Re execute the workload after the change by executing SQL Performance Analyzer on the same SQL tuning set that was used to baseline the performance in step 2. SPA can now be used to compare performance of the two runs of the tuning set to identify the statements that have either improved, refressed or unchanged.

The advantage of this tool, compared to the home grown approaches that dba's have evolved over the years, is that it takes the number of executions into consideration while measuring the impact.  Oracle 11G SQL Performance Analyzer takes all these factors into account while predicting the overall performance improvement and regressions and allows the oracle 11g database administrator to fix the SQL or fix the regression through SQL Plan Baselines.