22 SQL Performance Analyzer

22.1 Overview of SQL Performance Analyzer

As illustrated in Figure 22-1, SQL Performance Analyzer evaluates the impact of system changes on SQL performance through five main steps.

Figure 22-1 SQL Performance Analyzer Workflow

Description of "Figure 22-1 SQL Performance Analyzer Workflow"

The steps of the SQL Performance Analyzer workflow are as follows:

1. Capture the SQL workload.

   You must first capture the set of SQL statements that represents the typical SQL workload on your production system in a SQL Tuning Set (STS). Later, you can conduct the SQL Performance Analyzer analysis on the same database where the workload was captured or on a different database. Because the analysis is resource-intensive, you would typically capture the workload on a production database and perform the analysis on a test database that closely resembles the production system. For more details about how to perform these actions, see "Capturing the SQL Workload".

2. Measure the performance of the workload before the change.

   SQL Performance Analyzer executes the SQL statements captured in the SQL Tuning Set and generates execution plans and execution statistics for each statement. Only queries and the query part of DML statements are executed to avoid any side effect on the database. SQL Performance Analyzer executes SQL statements sequentially and in isolation from each other without any respect to their initial order of execution and concurrency. However, you can customize the order in which SQL Performance Analyzer executes the SQL queries. For example, you can start with the most expensive SQL statements in terms of response time.

3. Make a change.

   Make the change whose effect on SQL performance you intend to measure. SQL Performance Analyzer can analyze the effect of many types of system changes. For example, you can test a database upgrade, new index creation, initialization parameter changes, optimizer statistics refresh, and so on.

4. Measure the performance of the workload after the change.

   After you have made the planned change, SQL Performance Analyzer re-executes the SQL statements and produces execution plans and execution statistics for each SQL statement a second time. This execution result represents a new set of performance data that SQL Performance Analyzer uses for subsequent comparison.

5. Compare performance.

   SQL Performance Analyzer compares the performance of SQL statements before and after the change and produces a report identifying any changes in execution plans or performance of the SQL statements.If the performance comparison reveals regressed SQL statements, then you can make further changes to remedy the problem. For example, you can fix regressed SQL by running SQL Tuning Advisor. You can then repeat the process of executing the SQL Tuning Set and comparing its performance to the first execution. Repeat these steps until you are satisfied with the outcome of the analysis.

22.2 Capturing the SQL Workload

To capture a SQL workload that can be used with the SQL Performance Analyzer, you must capture a representative set of SQL statements and store them in a SQL tuning set. A SQL tuning set is a database object that is used to manage SQL workloads. The SQL tuning set can be used to store one or more SQL statements along with their execution context, including the text of the SQL, parsing schema under which the SQL statement can be compiled, bind values needed to execute the SQL statement, execution plan, number of times the SQL statement was executed, and so on.

You can load SQL statements into a SQL Tuning Set from different sources, including the cursor cache, Automatic Workload Repository (AWR), and existing SQL Tuning Sets. For more information about creating, loading, and transporting SQL tuning sets, see "SQL Tuning Sets".