SQL Performance Analyzer (SPA) allows you to evaluate a set of SQL statements against known workloads.  This set of SQL statements with execution statistics and execution context is known as an SQL Tuning set.    A single SQL Tuning set can be adjusted with updated SQL statements to test various evolutions of the same process.   Additionally, you can use cursor cache and AWR snapshots to track performance.  Using SQL TUNING SET enables you to:

 

1. Store SQL text and auxiliary info in single persistent database objects.
2. Capture Insert, update, delete, and select SQL statements in the SQL TUNING SET.
3. Load and merge content from different data sources like AWR and cursor cache.
4. Export SQL TUNING SET where SQL workload is captured and import into other systems.
5. Reuse SQL workload as input source for other advisors like SQL Tuning Advisor and SQL Access Advisor.

 

The Views and  package that relate to SPA are:

DBA_ADVISOR_TASKS – Info about SPA tasks created.

DBA_ADVISOR_EXECUTIONS – info about SPA tasks executed.

DBA_ADVISOR_FINDINGS – info about SPA finding, generate the following reports:

• Performance regressions problems.
• Changing structure of execution plan.
• Errors like non-existence of objects.
• Info messages.

DBA_ADVISOR_SQLPLANS – list of execution plans.

DBA_ADVISOR_SQLSTATS – list of SQWL compilations and execution statistics.

V$ADVISOR_PROGRESS – displays operation progress of SPA.

Functions

DBMS_SQLPA .CREATE_ANALYSIS_TASK – This function create analysis tasks for single SQL statements from SQL text, AWR, or cursor cache.  Also create analysis task from SQL Tuning set.

DBMS_SQLPA .EXECUTE_ANALYSIS_TASK – This function executes defined analysis tasks.

DBMS_SQLPA .REPORT_ANALYSIS_TASK – This function displays results of executed tasks.

 

 

Steps in Performing Performance Analysis:

 

1. Capture SQL workload to analyze and store it in a SQL tuning set.
2. If using test system it should be as close to production as possible and transfer SQL Tuning Set.
3. On test system create SPA task
4. Build pre-change SQL trails by executing the SQL statements store in the SQL tuning set.
5. Perform the system change.
6. Build post-change SQL trial by re-executing the SQL statements in SQL tuning set on the post-change test system.
7. Compare and analyze the pre-change and post-change versions of performance data and generate a report to identify the SLQ statements that have improved, remained unchanged, or regressed after system change.
8. Tune any regressed SQL statements that are identified.

 

Larry Catt

OCP