SQL performance analyzer performs a similar job to the SQL Tuning Advisor with one major difference, SQL performance analyser allows you to compare the performance of SQL statements before and after a change, allowing you to predict the impact of changes to the system on SQL performance as well as pre tuning your database before making changes.
So, The SQL Tuning Advisor is designed to help tune SQL in the system as it currently exists where as the SQL Performance Analyzer is designed to help determine the impact of system changes to SQL performance.
It allows you to compare a wide range of changes from operating system changes to database upgrades and down to the level of index changes.
The recommended approach would be to replecate your productoin system and run your tests on that system.
The basic procedure for using SQL Performance Analyzer is as follows:
- Capture SQL workload – Load the required SQL into a tuning set.
- Set up test system – So that you do not need to make changes to the live system.
- Move SQL tuning set to new system.
- Create SQL Performance Analyzer task – by associating your SQL tuning set with a SQL performance analyzer task.
- Build trial by executing SQL – Get a baseline to compare the post change performance to.
- Make System Change
- Run Post change trial
- Compare changes
- Tune as necessary – The report generated includes guidance and suggestions on tuning the SQL for the new changes.
A SQL tuning set is a database object that includes one or more SQL statements , along with their execution statistics and execution context.
SQL statements can be loaded into a SQL tuning set from many different sources. It’s possible to use the cursor cache, Automatic Workload Repository (AWR), or existing SQL tuning sets.
Using a SQL tuning set enables you to:
- Store the SQL text and any necessary auxiliary information in a single, persistent database object
- Populate, update, delete, and select captured SQL statements in the SQL tuning set
- Load and merge content from various data sources, such as the Automatic Workload Repository (AWR) or the cursor cache
- Export the SQL tuning set from the system where the SQL workload is captured and import it into another system
- Reuse the SQL workload as an input source for other advisers, such as the SQL Tuning Adviser and the SQL Access Adviser
Useful SQL Performance Adviser views:
- DBA_ADVISOR_TASKS
- DBA_ADVISOR_EXECUTIONS
- DBA_ADVISOR_FINDINGS
- DBA_ADVISOR_SQLPLANS
- DBA_ADVISOR_SQLSTATS
- V$ADVISOR_PROGRESS
A great article with examples can be found on Oracle Base
Processes
DBMS_SQLTUNE package:
*
CREATE_SQLSET – creates tuning set object in DB
LOAD_SQLSET – populates tuning set
CREATE_SGTAB_SQLSET – creates staging table
PACK_SGTAB_SQLSET – copy tuning set from SYS schema to staging table
UNPACK_SGTAB_SQLSET – copy tuning set from staging table to sql tuning set schema
DBMS_SQLPA package:
CREATE_ANALYSIS_TASK
EXECUTE_ANALYSIS_TASK
REPORT_ANALYSIS_TASK