System changes that affect SQL execution plans, such as upgrading a database or adding new indexes, can severely impact SQL performance. As a result, DBAs spend considerable time identifying and fixing SQL statements that have regressed due to a change.
SQL Performance Analyzer automates the process of assessing the overall effect of a change on the full SQL workload by identifying performance divergence for each statement. A report that shows the net impact on the workload performance due to the change is provided. For regressed SQL statements, SQL Performance Analyzer also provides appropriate executions plan details along with tuning recommendations. As a result, DBAs can remedy any negative outcome before their end users are affected and can validate, with significant time and cost savings, that the system change to the production environment will result in net improvement.
You can use SQL Performance Analyzer to analyze the SQL performance impact of any type of system changes. Examples of common system changes for which you can use SQL Performance Analyzer include:
Database upgrade
Configuration changes to the operating system, hardware, or database
Database initialization parameter changes
Schema changes, for example, adding new indexes or materialized views
Gathering optimizer statistics
Validating SQL tuning actions, for example, creating SQL profiles
This chapter contains the following sections:
As illustrated in Figure 2-1, SQL Performance Analyzer evaluates the impact of system changes on SQL performance through five main steps.
Figure 2-1 SQL Performance Analyzer Workflow
The steps of the SQL Performance Analyzer workflow are as follows:
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 a SQL Workload".
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.
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.
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.
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.
To capture a SQL workload that can be used with the SQL Performance Analyzer, you must capture a representative set of SQL statements on the production system and store them in a SQL Tuning Set. A SQL tuning set (STS) 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), another SQL tuning set, and from a table or a view.
Note:
Only SQL workload capture is currently supported in this release. Captured SQL workloads can be executed, and their performance can be measured and compared, on Oracle Database 11g Release 1 (11.1) and subsequent releases. For more information, see Oracle Database 2 Day + Performance Tuning Guide 11g Release 1 (11.1) or Oracle Database Performance Tuning Guide 11g Release 1 (11.1).This section contains the following topics:
To capture a SQL workload into a SQL tuning set using Oracle Enterprise Manager:
On the Database Performance page, under Additional Monitoring Links, click SQL Tuning Sets.
The SQL Tuning Sets page appears. Existing SQL tuning sets are displayed on this page.
Click Create.
The Create SQL Tuning Set: Options page appears.
On the Create SQL Tuning Set: Options page:
In the SQL Tuning Set Name field, enter a name for the SQL tuning set.
Optionally, in the Description field, enter a description of the SQL tuning set.
Click Next.
The Create SQL Tuning Set: Load Methods page appears.
On the Create SQL Tuning Set: Load Methods page, select a load method to collect and load SQL statements into the SQL tuning set:
You can load active and running SQL statements from the cursor cache into the SQL tuning set incrementally over a specified period of time.
To incrementally capture active SQL statements from the cursor cache, select Incrementally capture active SQL statements over a period of time from the cursor cache.
In the Duration field, specify the duration within which the SQL statement will collected.
In the Frequency field, specify the frequency over which the active SQL statements will be collected repeatedly from the cursor cache.
To load SQL statements from another data source, select Load SQL statements one time only.
Select the desired data source:
Cursor cache
You can load SQL statements from the cursor cache into the SQL tuning set. However, because only current and recent SQL statements are stored in the SQL cache, collecting these SQL statements only once may result in a SQL tuning set this is not representative of the entire workload on your database.
To use SQL statements from the cursor cache, from the Data Source list, select Cursor Cache.
AWR snapshots
You can load SQL statements captured in AWR snapshots. This is useful when you want to collect SQL statements for specific snapshot periods of interest.
To use SQL statements captured in AWR snapshots, from the Data Source list, select AWR Snapshots.
From the AWR Snapshots list, select the period containing the AWR snapshots you want to include. You can choose to include only AWR snapshots captured in the last day, week, or month. Alternatively, you can choose to include all AWR snapshots.
Preserved snapshot sets
You can load SQL statements captured in preserved snapshot sets. This is useful when you want to collect SQL statements from previously preserved snapshots that are representative of a time period during known performance levels.
To use SQL statements captured in preserved snapshot sets, from the Data Source list, select Preserved Snapshot Sets.
In the Preserved Snapshot Sets field, enter the name of the preserved snapshot set containing the AWR snapshots you want to include, or click the Search icon to select it from the Search and Select: Preserved Snapshot Sets window.
User-defined workload
You can load SQL statements into a SQL tuning set by importing SQL statements from a table or view. This is useful if the workload you want to use is not currently running on the database or captured in existing AWR snapshots or preserved snapshot sets. There are no restrictions on which schema the workload resides in, the name of the table, or the number of tables that you can define. The only requirement is that the table must have a sql_text
column that stores the text of the SQL statements.
To use SQL statements from a table or view, from the Data Source list, select User-Defined Workload. In the User-Defined Workload field, enter the name of the table or view you want to use, or click the Search icon to select it from the Search and Select: User Defined Workload window.
Click Next.
The Create SQL Tuning Set: Filter Options page appears.
After the load method is selected, you can apply filters to reduce the scope of the SQL statements found in the SQL tuning set:
On the Create SQL Tuning Set: Filter Options page, specify the values of filter conditions that you want use in the search in the Value column, and an operator or a condition in the Operator column.
Only the SQL statements that meet all of the specified filter conditions will be added into the SQL tuning set. Unspecified filter values will not be included as filter conditions in the search.
To add filter conditions, under Filter Conditions, select the filter condition you want to add and click Add a Filter or Column.
After the desired filter conditions have been added, specify their values in the Value column, and an operator or a condition in the Operator column.
To remove any unused filter conditions, click the icon in the Remove column for the corresponding filter condition you want to remove.
Click Next.
The Create SQL Tuning Set: Schedule page appears.
After the filter options are specified for the SQL tuning set, you can schedule and submit a job to collect the SQL statements and load them into the SQL tuning set:
On the Create SQL Tuning Set: Schedule page, under Job Parameters, enter a name in the Job Name field if you do not want to use the system-generated job name.
In the Description field, enter a description of the job.
Under Scheduling, select Immediately to run the job immediately after it has been submitted, or Later to run the job at a later time as specified using the Time Zone, Date, and Time fields.
Click Next.
The Create SQL Tuning Set: Review page appears.
Review the SQL tuning set options that you have selected.
To view the SQL statements used by the job, expand Show SQL.
Click Submit.
The SQL Tuning Sets page appears.
If the job was scheduled to run immediately, a message is displayed to inform you that the job and the SQL tuning set was created successfully. If the job was scheduled to run at a later time, a message is displayed to inform you that the job was created successfully.
See Also:
Oracle Database 2 Day + Performance Tuning Guide for information about creating a SQL tuning set using Enterprise ManagerTo capture a SQL workload into a SQL tuning set using APIs:
Use the DBMS_SQLTUNE.CREATE_SQLSET
procedure to create an empty SQL tuning set in the database.
For example, the following procedure creates a SQL tuning set named STS_080125
:
BEGIN DBMS_SQLTUNE.CREATE_SQLSET( sqlset_name => 'STS_080125', description => 'SQL tuning set for 2008/01/25'); END; /
Populate the SQL tuning set with selected SQL statements. The data sources for populating a SQL tuning set include the cursor cache, workload repository, or another SQL tuning set.
In the following example, the CAPTURE_CURSOR_CACHE_SQLSET
procedure is used to load STS_080125
with active SQL statements incrementally captured from the cursor cache every 5 minutes over a one-hour period.
EXEC DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET( - sqlset_name => 'STS_080125', - time_limit => 3600, - repeat_interval => 300);
See Also:
Oracle Database Performance Tuning Guide for information about creating a SQL tuning set using APIs
Oracle Database PL/SQL Packages and Types Reference for information about the DBMS_SQLTUNE
package
After a SQL workload is captured, you can transport it to another system, such as a test system running Oracle Database 11g, where its performance can be measured and compared using SQL Performance Analyzer.
This section contains the following topics:
To transport a SQL workload using Enterprise Manager, you need to first export the SQL tuning set(s) containing the SQL workload from the production system where it was captured, then import the SQL tuning set(s) into another system where it can be analyzed by SQL Performance Analyzer.
Note:
For Enterprise Manager, only exporting a SQL workload is currently supported in this release. Exported SQL workloads can be imported using Enterprise Manager on Oracle Database 11g Release 1 (11.1) and subsequent releases, or by using APIs. For more information, see Oracle Database 2 Day + Performance Tuning Guide 11g Release 1 (11.1).To export a SQL tuning set:
On the Database Home page, under Related Links, click Advisor Central.
The Advisor Central page appears.
Under Advisors, click SQL Tuning Advisor.
The SQL Tuning Advisor Links page appears.
Click SQL Tuning Sets.
The SQL Tuning Sets page appears. Existing SQL tuning sets are displayed on this page.
Select the SQL tuning set you want to export and click Export.
The Export SQL Tuning Set page appears.
In the Directory Object field, select a directory where the export file will be created.
For example, to use the Data Pump directory, select DATA_PUMP_DIR
. The Directory Name field refreshes automatically to indicate the selected directory.
In the Export File field, enter a name for the dump file that will be exported.
Alternatively, you can accept the name generated by the system.
In the Log File field, enter a name for the log file for the export operation.
Alternatively, you can accept the name generated by the system.
Select a tablespace to temporarily store the data for the export operation.
By default, SYSAUX
is used.
In the Job Name field, enter a name for the job.
Alternatively, you can accept the name generated by the system.
Under Schedule, select:
Immediately to run the job immediately after it has been submitted.
Later to run the job at a later time as specified by selecting or entering values in the Time Zone, Date, and Time fields.
Click OK.
The SQL Tuning Sets page appears.
A confirmation message is displayed to indicate that the job was successfully created.
Transport the export file to another system using the mechanism of choice (such as Data Pump or database link).
See Also:
Oracle Database 2 Day + Performance Tuning Guide for information about transporting a SQL tuning set using Enterprise ManagerTo transport a SQL workload using APIs, you need to first move the SQL tuning set(s) containing the SQL workload from the production system where it was captured to a staging table, then export the SQL tuning set(s) from the staging table into another system where it can be analyzed by SQL Performance Analyzer.
To transport a SQL Tuning Set:
Use the CREATE_STGTAB_SQLSET
procedure to create a staging table where the SQL tuning sets will be exported.
The following example shows how to create a staging table named staging_table
. Table names are case-sensitive.
BEGIN DBMS_SQLTUNE.CREATE_STGTAB_SQLSET( table_name => 'staging_table' ); END; /
Use the PACK_STGTAB_SQLSET
procedure to export SQL tuning sets into the staging table.
The following example shows how to export a SQL tuning set named STS_080125
to the staging table.
BEGIN DBMS_SQLTUNE.PACK_STGTAB_SQLSET( sqlset_name => 'STS_080125', staging_table_name => 'staging_table'); END; /
Move the staging table to the system where the SQL tuning sets will be imported using the mechanism of choice (such as datapump or database link).
On the system where the SQL Tuning Sets will be imported, use the UNPACK_STGTAB_SQLSET
procedure to import SQL Tuning Sets from the staging table.
The following example shows how to import SQL Tuning Sets contained in the staging table.
BEGIN DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET( sqlset_name => '%', replace => TRUE, staging_table_name => 'staging_table'); END; /
See Also:
Oracle Database Performance Tuning Guide for information about transporting a SQL tuning set using APIs
Oracle Database PL/SQL Packages and Types Reference for information about the DBMS_SQLTUNE
package