7 Introduction to SQL Performance Analyzer

You can run SQL Performance Analyzer on a production system or a test system that closely resembles the production system. Testing a system change on a production system will impact the system's throughput because SQL Performance Analyzer needs to execute the SQL statements that you are testing. Any global changes made on the system to test the performance effect may also affect other users of the system. If the system change does not impact many sessions or SQL statements, then running SQL Performance Analyzer on the production system may be acceptable. However, for system-wide changes—such as a database upgrade—using a production system is not recommended. Instead, consider running SQL Performance Analyzer on a separate test system so that you can test the effects of the system change without affecting the production system. Using a test system also ensures that other workloads running on the production system will not affect the analysis performed by SQL Performance Analyzer. Running SQL Performance Analyzer on a test system is the recommended approach and the methodology described here. If you choose to run the SQL Performance Analyzer on the production system, then substitute the production system for the test system where applicable.

Analyzing the SQL performance effect of system changes using SQL Performance Analyzer involves the following steps, as illustrated in Figure 7-1:

Figure 7-1 SQL Performance Analyzer Workflow

Description of Figure 7-1 follows
Description of "Figure 7-1 SQL Performance Analyzer Workflow"

  1. Capture the SQL workload that you intend to analyze and store it in a SQL tuning set, as described in "Capturing the SQL Workload".

  2. If you plan to use a test system separate from your production system, then perform the following steps:

    1. Set up the test system to match the production environment as closely as possible.

    2. Transport the SQL tuning set to the test system.

    For more information, see "Setting Up the Test System".

  3. On the test system, create a SQL Performance Analyzer task, as described in "Creating a SQL Performance Analyzer Task".

  4. Build the pre-change SQL trial by executing the SQL statements stored in the SQL tuning set, as described in "Measuring the Pre-Change SQL Performance"

  5. Perform the system change, as described in "Making a System Change"

  6. Build the post-change SQL trial by re-executing the SQL statements in the SQL tuning set on the post-change test system, as described in "Measuring the Post-Change SQL Performance"

  7. Compare and analyze the pre-change and post-change versions of performance data, and generate a report to identify the SQL statements that have improved, remained unchanged, or regressed after the system change, as described in "Comparing Performance Measurements"

  8. Tune any regressed SQL statements that are identified, as described in "Fixing Regressed SQL Statements".

  9. Ensure that the performance of the tuned SQL statements is acceptable by repeating steps 6 through 8 until your performance goals are met.

    For each comparison, you can use any previous SQL trial as the pre-change SQL trial and the current SQL trial as the post-change SQL trial. For example, you may want to compare the first SQL trial to the current SQL trial to assess the total change, or you can compare the most recent SQL trial to the current SQL trial to assess just the most recent change.

Capturing the SQL Workload

Before running the SQL Performance Analyzer, capture a set of SQL statements on the production system that represents the SQL workload which you intend to analyze.

The captured SQL statements should include the following information:

  • SQL text

  • Execution environment

    • SQL binds, which are bind values needed to execute a SQL statement and generate accurate execution statistics

    • Parsing schema under which a SQL statement can be compiled

    • Compilation environment, including initialization parameters under which a SQL statement is executed

  • Number of times a SQL statement was executed

Capturing a SQL workload has a negligible performance impact on your production system and should not affect throughput. A SQL workload that contains more SQL statements will better represent the state of the application or database. This will enable SQL Performance Analyzer to more accurately forecast the potential impact of system changes on the SQL workload. Therefore, you should capture as many SQL statements as possible. Ideally, you should capture all SQL statements that are either called by the application or are running on the database.

You can store captured SQL statements in a SQL tuning set and use it as an input source for SQL Performance Analyzer. 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 different sources, including the cursor cache, Automatic Workload Repository (AWR), and existing SQL tuning sets. Capturing a SQL workload 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 advisors, such as the SQL Tuning Advisor and the SQL Access Advisor

See Also:

Setting Up the Test System

After you have captured the SQL workload into a SQL tuning set on the production system, you can conduct SQL Performance Analyzer analysis on the same database where the workload was captured or on a different database. Because the analysis is resource-intensive, it is recommended that you capture the workload on a production database and transport it to a separate test database where the analysis can be performed. To do so, export the SQL tuning set from the production system and import it into a separate system where the system change will be tested.

There are many ways to create a test database. For example, you can use the DUPLICATE command of Recovery Manager (RMAN), Oracle Data Pump, or transportable tablespaces. Oracle recommends using RMAN because it can create the test database from pre-existing backups or from the active production datafiles. The production and test databases can reside on the same host or on different hosts.

You should configure the test database environment to match the database environment of the production system as closely as possible. In this way, SQL Performance Analyzer can more accurately forecast the effect of the system change on SQL performance.

After the test system is properly configured, export the SQL tuning set from the production system to a staging table, then import it from the staging table into the test system.

See Also:

Creating a SQL Performance Analyzer Task

After the SQL workload is captured and transported to the test system, and the initial database environment is properly configured, you can run SQL Performance Analyzer to analyze the effects of a system change on SQL performance.

To run SQL Performance Analyzer, you must first create a SQL Performance Analyzer task. A task is a container that encapsulates all of the data about a complete SQL Performance Analyzer analysis. A SQL Performance Analyzer analysis comprises of at least two SQL trials and a comparison. A SQL trial encapsulates the execution performance of a SQL tuning set under specific environmental conditions and represents a particular test execution or explain plan operation performed by SQL Performance Analyzer when testing a system change. When creating a SQL Performance Analyzer task, you will need to select a SQL tuning set as its input source. The SQL tuning set remains constant in the SQL Performance Analyzer task and is executed in isolation during each SQL trial. Thus, performance differences between trials are caused by environmental differences.

See Also:

Measuring the Pre-Change SQL Performance

Execute the SQL workload to create a pre-change SQL trial before making the system change. Executing a SQL workload runs each of the SQL statements contained in the workload to completion. Each SQL statement in the SQL tuning set is executed once—one at a time—separately from other SQL statements without preserving their initial order of execution or concurrency. To avoid a potential impact to the database, DDLs are not supported; only the query portion of DMLs are executed. During execution, SQL Performance Analyzer generates execution plans and computes execution statistics for each SQL statement in the workload.

Depending on its size, executing a SQL workload can be time and resource intensive. When executing a SQL workload, you can choose to generate execution plans only, without collecting execution statistics. This technique shortens the time to run the execution and lessens the effect on system resources, but a comprehensive performance analysis is not possible because only the execution plans will be available during the analysis.

Another method to execute a SQL workload is to run SQL Performance Analyzer on a system running Oracle Database 11g, and remotely execute the SQL statements on a separate database using a database link that you specify. SQL Performance Analyzer will establish a connection to the remote database using the specified database link and execute the SQL statements on that database, collect the execution plans and runtime statistics for each SQL statement, and store the results in a SQL trial on the local database that can be used for later analysis. This method is useful in cases where you want to:

  • Test a database upgrade

  • Execute the SQL workload on a system running another version of Oracle Database

  • Store the results from the SQL Performance Analyzer analysis on a separate test system

  • Perform testing on multiple systems with different hardware configurations

  • Use the newest features in SQL Performance Analyzer even if you are using an older version of Oracle Database on your production system

Once the SQL workload is executed, the resulting execution plans and runtime statistics are stored in a SQL trial.

See Also:

Making a System 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, or optimizer statistics refresh. If you are running SQL Performance Analyzer on the production system, then consider making a change using a private session to avoid affecting the rest of the system.

Measuring the Post-Change SQL Performance

After performing the system change, execute the SQL workload again to create post-change SQL trial. SQL Performance Analyzer generates execution plans and computes execution statistics for each SQL statement in the workload a second time, resulting in a new set of performance data that can be used to compare to the pre-change version. The results are stored in a new, or post-change, SQL trial.

See Also:

Comparing Performance Measurements

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.

SQL Performance Analyzer measures the impact of system changes both on the overall response time of the SQL workload and of every individual SQL statement in the workload. By default, SQL Performance Analyzer uses elapsed time as a metric for comparison. Alternatively, you can choose the metric for comparison from a variety of available SQL runtime statistics, including:

  • CPU time

  • Buffer gets

  • Disk reads

  • Disk writes

  • Any combination of these metrics in the form of an expression

If you chose to generate explain plans only in the SQL trials, then SQL Performance Analyzer will use the optimizer cost stored in the SQL execution plans.

See Also:

Fixing Regressed SQL Statements

If the performance analysis performed by SQL Performance Analyzer reveals regressed SQL statements, then you can make changes to remedy the problem. For example, you can fix regressed SQL by running SQL Tuning Advisor or using SQL plan baselines. You can then repeat the process of executing the SQL statements and comparing its performance to the first execution. Repeat these steps until you are satisfied with the outcome of the analysis.

See Also: