| Oracle® Database Performance Tuning Guide 11g Release 1 (11.1) Part Number B28274-01 |
|
|
View PDF |
| Oracle® Database Performance Tuning Guide 11g Release 1 (11.1) Part Number B28274-01 |
|
|
View PDF |
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
SQL tuning actions, for example, creating SQL profiles
This chapter contains the following sections:
See Also:
Oracle Database 2 Day + Performance Tuning Guide to learn how to run SQL Performance Analyzer with Oracle Enterprise Manager (Enterprise Manager)
Oracle Database PL/SQL Packages and Types Reference for information about the security model for the DBMS_SQLPA package
As illustrated in Figure 23-1, SQL Performance Analyzer evaluates the impact of system changes on SQL performance through five main steps.
Figure 23-1 SQL Performance Analyzer Workflow
The steps of the SQL Performance Analyzer workflow are as follows:
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".
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 or using SQL plan baselines. 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.
See Also:
Chapter 17, "Automatic SQL Tuning" to learn about the SQL Tuning Advisor
Chapter 15, "Using SQL Plan Management" to learn about SQL plan baselines
The primary interface for running SQL Performance Analyzer is Enterprise Manager, as described in Oracle Database 2 Day + Performance Tuning Guide. You can also run SQL Performance Analyzer using the DBMS_SQLPA PL/SQL package. This section explains how to run SQL performance Analyzer using the main PL/SQL APIs of this package. These APIs are listed in Table 23-1. For more details, see Oracle Database PL/SQL Packages and Types Reference.
Table 23-1 Main Functions of the DBMS_SQLPA Package
| Function | Purpose |
|---|---|
|
|
Create a SQL Performance Analyzer task to process and analyze a SQL Tuning Set |
|
|
Set a SQL analysis task parameter value |
|
|
Run a previously created task |
|
|
Drop a task, deleting all performance data |
|
|
Generate a report of analysis task results |
To use SQL Performance Analyzer:
Capture the SQL workload that you intend to analyze and store it in a SQL Tuning Set.
"Capturing the SQL Workload" explains how to perform this task.
If you plan to use a test system separate from your production system, then perform the following steps:
Set up the test system so that it matches the production environment as closely as possible.
"Setting Up the Test System" explains how to perform this task.
Transport the SQL Tuning Set to the test system.
"Transporting the Workload to the Test System" explains how to perform this task.
Create a SQL Performance Analyzer task on the test system using the SQL Tuning Set as its input source.
"Creating a SQL Performance Analyzer Task" explains how to perform this task.
Use SQL Performance Analyzer to build the pre-change performance data by executing the SQL statements stored in the SQL Tuning Set.
"Executing the SQL Workload Before a Change" explains how to perform this task.
Make a change on the test system.
"Making a Change" explains how to perform this task.
Use SQL Performance Analyzer to build the post-change performance data by re-executing the SQL statements in the SQL Tuning Set on the post-change test system.
"Executing the SQL Workload After a Change" explains how to perform this task.
Compare the SQL performance by performing the following steps:
Use SQL Performance Analyzer to compare and analyze the pre-change and post-change versions of performance data.
Generate a report to identify the SQL statements in the SQL workload that have improved, remained unchanged, or regressed after the system change.
Review the report and interpret the results.
"Comparing SQL Performance" explains how to perform this task.
Optionally, run SQL Tuning Advisor or use SQL plan baselines to prevent regressions caused by execution plan changes.
Chapter 17, "Automatic SQL Tuning" explains how to tune SQL statements to improve their performance.
Chapter 15, "Using SQL Plan Management" explains how to use SQL Plan Management to avoid performance regressions due to execution plan changes.
Ensure that the performance of the tuned SQL statements is acceptable by repeating steps 5 through 8 until your performance goals are met.
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 STS 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, see "Loading a SQL Tuning Set".
To capture the SQL workload, perform one of the following tasks:
If you do not have a SQL Tuning Set to use as an input for SQL Performance Analyzer, then create an STS as described in "Creating a SQL Tuning Set".
If you plan to perform the performance analysis on a test database, then proceed to "Setting Up the Test System".
If you plan to perform the performance analysis on the production database only, then skip to "Creating a SQL Performance Analyzer Task".
You can run SQL Performance Analyzer on the production database or a test database. If you use a test database, then 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.
To set up the test system:
Create a new database for testing SQL performance.
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. For best results, the test database should be as similar to the production system as possible. 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.
Configure the database environment.
For example, to test how changing a database initialization parameter will affect SQL performance, set the database initialization parameter on the test system to the same value as the production system. To test a database upgrade to Oracle Database 11g, install Oracle Database 11g on the test system and revert the OPTIMIZER_FEATURES_ENABLE initialization parameter to the database version of the production system.
See Also:
Oracle Database Backup and Recovery User's Guide to learn how to duplicate a database with RMANAfter you have created a SQL Tuning Set with the appropriate SQL workload, export the STS from the production system and import it into the test system where the system change under consideration will be tested.
To transport the SQL workload to the test system, export the SQL Tuning Set from one database to a staging table, then import the set from the staging table into the test database, as described in "Transporting a SQL Tuning Set".
This section describes how to create a new SQL Performance Analyzer task by using the DBMS_SQLPA.CREATE_ANALYSIS_TASK function. A task is a database container for SQL Performance Analyzer execution inputs and results.
Before creating the task, make sure that the SQL workload to use for the performance analysis is available on the system in the form of a SQL Tuning Set. Call the CREATE_ANALYSIS_TASK function to prepare the analysis of a SQL Tuning Set using the following parameters:
Set task_name to specify an optional name for the SQL Performance Analyzer task.
Set sqlset_name to the name of the SQL Tuning Set.
Set sqlset_owner to the owner of the SQL Tuning Set. The default is the current schema owner.
Set basic_filter to the SQL predicate used to filter the SQL from the SQL Tuning Set.
Set order_by to specify an order-by clause on the selected SQL.
Set top_sql to consider only the top number of SQL statements after filtering and ranking.
The following example illustrates a function call:
VARIABLE t_name VARCHAR2(100);
EXEC :t_name := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => 'my_sts', -
task_name => 'my_spa_task');
See Also:
Oracle Database PL/SQL Packages and Types Reference to learn more about theDBMS_SQLPA.CREATE_ANALYSIS_TASK functionAs explained in "Overview of SQL Performance Analyzer", you must execute the SQL workload to build the pre-change performance data.
Make sure that you have created a SQL Performance Analyzer task. Call the EXECUTE_ANALYSIS_TASK procedure using the following parameters:
Set the task_name parameter to the name of the SQL Performance Analyzer task that you want to execute.
Set the execution_type parameter in either of the following ways:
Set to EXPLAIN PLAN to generate execution plans for all SQL statements in the SQL Tuning Set without executing them.
Set to TEST EXECUTE (recommended) to execute all statements in the SQL Tuning Set and generate their execution plans and statistics. When TEST EXECUTE is specified, the procedure generates execution plans and execution statistics. The execution statistics enable SQL Performance Analyzer to identify SQL statements that have improved or regressed. Collecting execution statistics in addition to generating execution plans provides greater accuracy in the performance analysis, but takes longer.
Specify a name to identify the execution using the execution_name parameter. If not specified, then SQL Performance Analyzer automatically generates a name for the task execution.
Specify execution parameters using the execution_params parameters. The execution_params parameters are specified as (name, value) pairs for the specified execution. For example, you can set the following execution parameters:
The time_limit parameter specifies the global time limit to process all SQL statements in a SQL Tuning Set before timing out.
The local_time_limit parameter specifies the time limit to process each SQL statement in a SQL Tuning Set before timing out.
The following example illustrates a function call made before a system change:
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'my_spa_task', -
execution_type => 'TEST EXECUTE', -
execution_name => 'my_exec_BEFORE_change');
See Also:
Oracle Database PL/SQL Packages and Types Reference to learn about theDBMS_SQLPA.EXECUTE_ANALYSIS_TASK function"Overview of SQL Performance Analyzer" lists examples of possible system changes. For example, you may want to determine how a database initialization parameter change or database upgrade will affect SQL performance. You may also decide to change the system based on recommendations from an advisor such as Automatic Database Diagnostic Monitor (ADDM), SQL Tuning Advisor, or SQL Access Advisor.
Make sure that you executed the SQL workload in the initial environment to generate the pre-change performance data, and then make the system change. For example, if you are testing how changing a database initialization parameter will affect SQL performance, then set the database initialization parameter to a new value.
See Also:
"Overview of SQL Performance Analyzer" for examples of the types of system changes that can be analyzed by SQL Performance AnalyzerAfter you perform the system change, as described in "Making a Change", execute the SQL workload again to build the after-change performance data of the workload.
Make sure that you have made the change to the system. Call the EXECUTE_ANALYSIS_TASK procedure using the parameters described in "Executing the SQL Workload Before a Change". Specify a different value for execution_name.
The following example illustrates a function call made after a system change:
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'my_spa_task', -
execution_type => 'TEST EXECUTE', -
execution_name => 'my_exec_AFTER_change');
See Also:
Oracle Database PL/SQL Packages and Types Reference to learn about theDBMS_SQLPA.EXECUTE_ANALYSIS_TASK functionAfter the post-change SQL performance data is built, you can compare the pre-change version of performance data to the post-change version. Run a comparison analysis using the DBMS_SQLPA.EXECUTE_ANALYSIS_TASK procedure or function. Later, you can generate a report that shows the results of the comparison and then interpret the results.
To compare the pre-change and post-change SQL performance data:
Call the EXECUTE_ANALYSIS_TASK procedure or function using the following parameters:
Set the task_name parameter to the name of the SQL Performance Analyzer task.
Set the execution_type parameter to COMPARE PERFORMANCE. This setting will analyze and compare two versions of SQL performance data.
Specify a name to identify the execution using the execution_name parameter. If not specified, it will be generated by SQL Performance Analyzer and returned by the function.
Specify two versions of SQL performance data using the execution_params parameters. The execution_params parameters are specified as (name, value) pairs for the specified execution. Set the execution parameters that are related to comparing and analyzing SQL performance data as follows:
Set the execution_name1 parameter to the name of the first execution (before the system change was made). This value should correspond to the value of the execution_name parameter specified in "Executing the SQL Workload Before a Change".
Set the execution_name2 parameter to the name of the second execution (after the system change was made). This value should correspond to the value of the execution_name parameter specified in "Executing the SQL Workload After a Change" when you executed the SQL workload after the system change. If the caller does not specify the executions, then by default SQL Performance Analyzer will always compare the last two task executions.
Set the comparison_metric parameter to specify an expression of execution statistics to use in the performance impact analysis. Possible values include the following metrics or any combination of them: elapsed_time (default), cpu_time, buffer_gets, disk_reads, direct_writes, and optimizer_cost.
For other possible parameters that you can set for comparison, see the description of the DBMS_SQLPA package in Oracle Database PL/SQL Packages and Types Reference.
The following example illustrates a function call:
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'my_spa_task', -
execution_type => 'COMPARE PERFORMANCE', -
execution_name => 'my_exec_compare', -
execution_params => dbms_advisor.arglist(-
'comparison_metric', 'buffer_gets'));
Call the DBMS_SQLPA.REPORT_ANALYSIS_TASK function using the following parameters:
Set the task_name parameter to the name of the SQL Performance Analyzer task.
Set the execution_name parameter to the name of the execution to use. This value should match the execution_name parameter of the execution for which you want to generate a report.
To generate a report to display the results of:
Execution plans generated for the SQL workload, set this value to match the execution_name parameter of the desired EXPLAIN PLAN execution.
Execution plans and execution statistics generated for the SQL workload, set this parameter to match the value of the execution_name parameter used in the desired TEST EXECUTION execution.
A comparison analysis, set this value to match the execution_name parameter of the desired ANALYZE PERFORMANCE execution.
If unspecified, SQL Performance Analyzer generates a report for the last execution.
Set the type parameter to specify the type of report to generate. Possible values include TEXT (default), HTML, and XML.
Set the level parameter to specify the format of the recommendations. Possible values include TYPICAL (default), BASIC, and ALL.
Set the section parameter to specify a particular section to generate in the report. Possible values include SUMMARY (default) and ALL.
Set the top_sql parameter to specify the number of SQL statements in a SQL Tuning Set to generate in the report. By default, the report shows the top 100 SQL statements impacted by the system change.
The following example illustrates a portion of a SQL script that you could use to create and display a comparison summary report:
VAR rep CLOB;
EXEC :rep := DBMS_SQLPA.REPORT_ANALYSIS_TASK('my_spa_task', -
'text', 'typical', 'summary');
SET LONG 100000 LONGCHUNKSIZE 100000 LINESIZE 130
PRINT :rep
Review the SQL Performance Analyzer report.
The report is divided into the following main sections:
The following sections describe how to review a SQL Performance Analyzer report by using a sample report. This sample report uses buffer_gets as the comparison metric to compare the pre-change and post-change executions of a SQL workload.
See Also:
Oracle Database PL/SQL Packages and Types Reference for information about theDBMS_SQLPA.EXECUTE_ANALYSIS_TASK and DBMS_SQLPA.REPORT_ANALYSIS_TASK functionsThe General Information section contains basic information and metadata about the SQL Performance Analyzer task, the SQL Tuning Set used, and the pre-change and post-change executions. Example 23-1 shows the General Information section of a sample report.
Example 23-1 General Information
--------------------------------------------------------------------------------------------- General Information --------------------------------------------------------------------------------------------- Task Information: Workload Information: --------------------------------------------- --------------------------------------------- Task Name : my_spa_task SQL Tuning Set Name : my_sts Task Owner : APPS SQL Tuning Set Owner : APPS Description : Total SQL Statement Count : 101 Execution Information: --------------------------------------------------------------------------------------------- Execution Name : my_exec_compare Started : 05/21/2007 11:30:09 Execution Type : ANALYZE PERFORMANCE Last Updated : 05/21/2007 11:30:10 Description : Global Time Limit : UNLIMITED Scope : COMPREHENSIVE Per-SQL Time Limit : UNUSED Status : COMPLETED Number of Errors : 0 Analysis Information: --------------------------------------------------------------------------------------------- Comparison Metric: BUFFER_GETS ------------------ Workload Impact Threshold: 1% -------------------------- SQL Impact Threshold: 1% ---------------------- Before Change Execution: After Change Execution: --------------------------------------------- --------------------------------------------- Execution Name : my_exec_BEFORE_change Execution Name : my_exec_AFTER_change Execution Type : TEST EXECUTE Execution Type : TEST EXECUTE Description : Description : Scope : COMPREHENSIVE Scope : COMPREHENSIVE Status : COMPLETED Status : COMPLETED Started : 05/21/2007 11:22:06 Started : 05/21/2007 11:25:56 Last Updated : 05/21/2007 11:24:01 Last Updated : 05/21/2007 11:28:30 Global Time Limit : 1800 Global Time Limit : 1800 Per-SQL Time Limit : UNUSED Per-SQL Time Limit : UNUSED Number of Errors : 0 Number of Errors : 0 ---------------------------------------------------------------------------------------------
In Example 23-1, the Task Information section indicates that the task name is my_spa_task. The Workload Information section indicates that the task compares executions of the my_sts SQL Tuning Set, which contains 101 SQL statements. As shown in the Execution Information section, the comparison execution is named my_exec_compare.
The Analysis Information sections shows that SQL Performance Analyzer compares two executions of the my_sts SQL Tuning Set, my_exec_BEFORE_change and my_exec_AFTER_change, using buffer_gets as a comparison metric.
The Result Summary section summarizes the results of the SQL Performance Analyzer task. The Result Summary section is divided into the following subsections:
The Overall Performance Statistics subsection displays statistics about the overall performance of the entire SQL workload. This section is a very important part of the SQL Performance Analyzer analysis because it shows the impact of the system change on the overall performance of the SQL workload. Use the information in this section to understand the change of the workload performance, and determine whether the workload performance will improve or degrade after making the system change.
Example 23-2 shows the Overall Performance Statistics subsection of a sample report.
Example 23-2 Overall Performance Statistics
Report Summary --------------------------------------------------------------------------------------------- Projected Workload Change Impact: ------------------------------------------- Overall Impact : 47.94% Improvement Impact : 58.02% Regression Impact : -10.08% SQL Statement Count ------------------------------------------- SQL Category SQL Count Plan Change Count Overall 101 6 Improved 2 2 Regressed 1 1 Unchanged 98 3 . . . ---------------------------------------------------------------------------------------------
This example indicates that the overall performance of the SQL workload improved by 47.94%, despite the fact that regressions had a negative impact of -10.08%. After the system change, 2 of the 101 SQL statements ran faster, while 1 ran slower. Performance of 98 statements remained unchanged.
The Performance Statistics subsection highlights the SQL statements that are the most impacted by the system change. The pre-change and post-change performance data for each SQL statement in the workload are compared based on the following criteria:
Weight, or importance, of each SQL statement
Impact of the system change on each SQL statement relative to the entire SQL workload
Impact of the system change on each SQL statement
Whether the structure of the execution plan for each SQL statement has changed
Example 23-3 shows the Performance Statistics of SQL Statements subsection of a sample report. The report has been altered slightly to fit on the page.
Example 23-3 Performance Statistics of SQL Statements
SQL Statements Sorted by their Absolute Value of Change Impact on the Workload -------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- | | | Impact | Metric | Metric | Impact | % Wrkld | % Wrkld | Plan | | obj_id | sql_id | Wrkld | Before | After | on SQL | Before | After | Change | --------------------------------------------------------------------------------------------- | 205 | 73s2sgy2svfrw | 29.01%| 1681683 | 220590 | 86.88% | 33.39% | 8.42% | y | | 206 | gq2a407mv2hsy | 29.01%| 1681683 | 220590 | 86.88% | 33.39% | 8.42% | y | | 204 | 2wtgxbjz6u2by | -10.08%| 1653012 | 2160529 | -30.7% | 32.82% | 82.48% | y | ---------------------------------------------------------------------------------------------
The SQL statements are sorted in descending order by the absolute value of the net impact on the SQL workload, that is, the sort order does not depend on whether the impact was positive or negative.
The Errors subsection reports all errors that occurred during an execution. An error may be reported at the SQL Tuning Set level if it is common to all executions in the SQL Tuning Set, or at the execution level if it is specific to a SQL statement or execution plan.
Example 23-4 shows an example of the Errors subsection of a SQL Performance Analyzer report.
Example 23-4 Errors
----------------------------------------------------------------------------------
SQL STATEMENTS WITH ERRORS
----------------------------------------------------------------------------------
SQL ID Error
------------- --------------------------------------------------------------------
47bjmcdtw6htn ORA-00942: table or view does not exist
br61bjp4tnf7y ORA-00920: invalid relational operator
----------------------------------------------------------------------------------
The Result Details section represents a drill-down into the performance of SQL statements that appears in the Result Summary section of the report. Use the information in this section to investigate why the performance of a particular SQL statement regressed.
This section will contain an entry of every SQL statement processed in the SQL performance impact analysis. Each entry is organized into the following subsections:
This section of the report summarizes the SQL statement, listing its information and execution details.
Example 23-5 shows the SQL Details subsection of a sample report.
Example 23-5 SQL Details
SQL Details:
-----------------------------
Object ID : 204
Schema Name : APPS
SQL ID : 2wtgxbjz6u2by
Execution Frequency : 1
SQL Text : SELECT /* my_query_14_scott */ /*+ ORDERED INDEX(t1)
USE_HASH(t1) */ 'B' || t2.pg_featurevalue_05_id
pg_featurevalue_05_id, 'r' || t4.elementrange_id
pg_featurevalue_15_id, 'G' || t5.elementgroup_id
pg_featurevalue_01_id, 'r' || t6.elementrange_id . . .
.
.
.
---------------------------------------------------------------------------------------------
In Example 23-5, the report summarizes the regressed SQL statement whose ID is 2wtgxbjz6u2by and corresponding object ID is 204.
The Single Execution Statistics subsection compares execution statistics of the SQL statement from the pre-change and post-change executions and then summarizes the findings.
Example 23-6 shows the Single Execution Statistics subsection of a sample report.
Example 23-6 Single Execution Statistics
Execution Statistics: ----------------------------- --------------------------------------------------------------------------------------- | | Impact on | Value | Value | Impact | % Workload | % Workload | | Stat Name | Workload | Before | After | on SQL | Before | After | --------------------------------------------------------------------------------------- | elapsed_time | -95.54%| 36.484 | 143.161 | -292.39% | 32.68% | 94.73% | | parse_time | -12.37%| .004 | .062 | -1450% | .85% | 11.79% | | exec_elapsed | -95.89%| 36.48 | 143.099 | -292.27% | 32.81% | 95.02% | | exec_cpu | -19.73%| 36.467 | 58.345 | -59.99% | 32.89% | 88.58% | | buffer_gets | -10.08%| 1653012 | 2160529 | -30.7% | 32.82% | 82.48% | | cost | 12.17%| 11224 | 2771 | 75.31% | 16.16% | 4.66% | | reads | -1825.72%| 4091 | 455280 | -11028.82% | 16.55% | 96.66% | | writes | -1500%| 0 | 15 | -1500% | 0% | 100% | | rows | | 135 | 135 | | | | --------------------------------------------------------------------------------------- Findings (2): ----------------------------- 1. The performance of this SQL has regressed. 2. The structure of the SQL execution plan has changed. ---------------------------------------------------------------------------------------------
The Execution Plans subsection displays the pre-change and post-change execution plans for the SQL statement. In cases when the performance regressed, this section also contains findings on root causes and symptoms.
Example 23-7 shows the Execution Plans subsection of a sample report.
Example 23-7 Execution Plans
Execution Plan Before Change: ----------------------------- Plan Id : 1 Plan Hash Value : 3412943215 ---------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | ---------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 126 | 11224 | 00:02:15 | | 1 | HASH GROUP BY | | 1 | 126 | 11224 | 00:02:15 | | 2 | NESTED LOOPS | | 1 | 126 | 11223 | 00:02:15 | | * 3 | HASH JOIN | | 1 | 111 | 11175 | 00:02:15 | | * 4 | TABLE ACCESS FULL | LU_ELEMENTGROUP_REL | 1 | 11 | 162 | 00:00:02 | | * 5 | HASH JOIN | | 487 | 48700 | 11012 | 00:02:13 | | 6 | MERGE JOIN | | 14 | 924 | 1068 | 00:00:13 | | 7 | SORT JOIN | | 5391 | 274941 | 1033 | 00:00:13 | | * 8 | HASH JOIN | | 5391 | 274941 | 904 | 00:00:11 | | * 9 | TABLE ACCESS FULL | LU_ELEMENTGROUP_REL | 123 | 1353 | 175 | 00:00:03 | | * 10 | HASH JOIN | | 5352 | 214080 | 729 | 00:00:09 | | * 11 | TABLE ACCESS FULL | LU_ITEM_293 | 5355 | 128520 | 56 | 00:00:01 | | * 12 | TABLE ACCESS FULL | ADM_PG_FEATUREVALUE | 1629 | 26064 | 649 | 00:00:08 | | * 13 | FILTER | | | | | | | * 14 | SORT JOIN | | 1 | 15 | 36 | 00:00:01 | | * 15 | TABLE ACCESS FULL | LU_ELEMENTRANGE_REL | 1 | 15 | 35 | 00:00:01 | | 16 | INLIST ITERATOR | | | | | | | * 17 | TABLE ACCESS BY INDEX ROWID | FACT_PD_OUT_ITM_293 | 191837 | 6522458 | 9927 | 00:02:00 | | 18 | BITMAP CONVERSION TO ROWIDS | | | | | | | * 19 | BITMAP INDEX SINGLE VALUE | FACT_274_PER_IDX | | | | | | * 20 | TABLE ACCESS FULL | LU_ELEMENTRANGE_REL | 1 | 15 | 49 | 00:00:01 | ---------------------------------------------------------------------------------------------------------- . . . Execution Plan After Change: ----------------------------- Plan Id : 102 Plan Hash Value : 1923145679 ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost | Time | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 126 | 2771 | 00:00:34 | | 1 | HASH GROUP BY | | 1 | 126 | 2771 | 00:00:34 | | 2 | NESTED LOOPS | | 1 | 126 | 2770 | 00:00:34 | | * 3 | HASH JOIN | | 1 | 111 | 2722 | 00:00:33 | | * 4 | HASH JOIN | | 1 | 100 | 2547 | 00:00:31 | | * 5 | TABLE ACCESS FULL | LU_ELEMENTGROUP_REL | 1 | 11 | 162 | 00:00:02 | | 6 | NESTED LOOPS | | | | | | | 7 | NESTED LOOPS | | 484 | 43076 | 2384 | 00:00:29 | | * 8 | HASH JOIN | | 14 | 770 | 741 | 00:00:09 | | 9 | NESTED LOOPS | | 4 | 124 | 683 | 00:00:09 | | * 10 | TABLE ACCESS FULL | LU_ELEMENTRANGE_REL | 1 | 15 | 35 | 00:00:01 | | * 11 | TABLE ACCESS FULL | ADM_PG_FEATUREVALUE | 4 | 64 | 649 | 00:00:08 | | * 12 | TABLE ACCESS FULL | LU_ITEM_293 | 5355 | 128520 | 56 | 00:00:01 | | 13 | BITMAP CONVERSION TO ROWIDS | | | | | | | * 14 | BITMAP INDEX SINGLE VALUE | FACT_274_ITEM_IDX | | | | | | * 15 | TABLE ACCESS BY INDEX ROWID | FACT_PD_OUT_ITM_293 | 36 | 1224 | 2384 | 00:00:29 | | * 16 | TABLE ACCESS FULL | LU_ELEMENTGROUP_REL | 123 | 1353 | 175 | 00:00:03 | | * 17 | TABLE ACCESS FULL | LU_ELEMENTRANGE_REL | 1 | 15 | 49 | 00:00:01 | ------------------------------------------------------------------------------------------------------
You can query the following views to monitor SQL Performance Analyzer and view its analysis results:
The DBA_ADVISOR_TASKS and USER_ADVISOR_TASKS views display descriptive information about the SQL Performance Analyzer task that was created.
The DBA_ADVISOR_EXECUTIONS and USER_ADVISOR_EXECUTIONS views display information about task executions. SQL Performance Analyzer creates at least three executions to analyze the SQL performance impact caused by a database change on a SQL workload. The first execution collects a pre-change version of the performance data. The second execution collects a post-change version of the performance data. The third execution performs the comparison analysis.
The DBA_ADVISOR_FINDINGS and USER_ADVISOR_FINDINGS views display the SQL Performance Analyzer findings. SQL Performance Analyzer generates the following types of findings:
Problems, such as performance regression
Symptoms, such as when the structure of an execution plan has changed
Errors, such as nonexistence of an object or view
Informative messages, such as when the structure of an execution plan in the pre-change version is different than the one stored in the SQL Tuning Set
The DBA_ADVISOR_SQLPLANS and USER_ADVISOR_SQLPLANS views display a list of all execution plans.
The DBA_ADVISOR_SQLSTATS and USER_ADVISOR_SQLSTATS views display a list of all SQL compilations and execution statistics.
You must have the SELECT_CATALOG_ROLE role to access the DBA views.
See Also:
Oracle Database Reference for information about theDBA_ADVISOR_TASKS, DBA_ADVISOR_EXECUTIONS, and DBA_ADVISOR_SQLPLANS viewsThis example describes how to use SQL Performance Analyzer to analyze the SQL performance impact of a database upgrade from Oracle Database 10g Release 2 to Oracle Database 11g Release 1.
On the production system running Oracle Database 10g, capture the SQL workload that you want to use in the analysis into a SQL Tuning Set.
Export the SQL Tuning Set and import it into the test system running Oracle Database 11g.
Set up the initial environment on the test system. In this case, set the OPTIMIZER_FEATURES_ENABLE initialization parameter to the database version from which you are upgrading, which is 10.2 in this scenario. You should use this parameter to test a database upgrade because SQL Performance Analyzer does not exist in releases before Oracle Database 11g. Thus, you cannot use SQL Performance Analyzer before an upgrade to build the pre-change version of the workload.
Create a SQL Performance Analyzer task using the SQL Tuning Set as the input source.
Execute the SQL workload to compute the pre-change version of the SQL workload.
Set the OPTIMIZER_FEATURES_ENABLE initialization parameter to the database version to which you are upgrading.
Execute the SQL workload a second time to compute the post-change version of the SQL workload.
Perform a comparison analysis to analyze the change in performance between the pre-change and post-change executions.
Review the SQL Performance Analyzer report to identify improvements or degradation to the performance of the SQL workload. In cases where the performance degraded, use the report to identify the SQL statements that have regressed and fix them with SQL Tuning Advisor or SQL plan baselines.
