SQL Performance Analyzer supports testing database upgrades from Oracle9i and later releases to Oracle Database 10g Release 2 or newer releases. The methodology used to test a database upgrade from Oracle9i Database and Oracle Database 10g Release 1 is slightly different from the one used to test a database upgrade from Oracle Database 10g Release 2 and later releases, so both methodologies are described here.
This chapter describes how to use SQL Performance Analyzer in a database upgrade and contains the following sections:
Upgrading from Oracle9i Database and Oracle Database 10g Release 1
Upgrading from Oracle Database 10g Release 2 and Newer Releases
Tuning Regressed SQL Statements After Testing a Database Upgrade
|
Note: For information about using SQL Performance Analyzer in other cases, see "SQL Performance Analyzer" |
As illustrated in Figure 7-1, SQL Performance Analyzer supports testing database upgrades of Oracle9i Database and Oracle Database 10g Release 1 to Oracle Database 10g Release 2 and later releases by building a SQL tuning set from SQL trace files captured on the production system, executing the SQL tuning set on the upgraded database remotely over a database link, and then comparing the results to those captured on the production system. Because SQL Performance Analyzer only accepts a set of SQL statements stored in a SQL tuning set as its input source, and SQL tuning sets are not supported in Oracle9i Database, a SQL tuning set must be constructed so that it can be used as an input source for SQL Performance Analyzer if you are upgrading from Oracle9i Database.
Figure 7-1 SQL Performance Analyzer Workflow for Database Upgrade from Oracle9i to Oracle Database 10g Release 2
The production system which you are upgrading from should be running Oracle9i or Oracle Database 10g Release 1. The test system which you are upgrading to should be running Oracle Database 10g Release 2 or a newer release. The database version can be release 10.2.0.2 or higher. If you are upgrading to Oracle Database 10g release 10.2.0.2, 10.2.0.3, or 10.2.0.4, you will also need to install a one-off patch before proceeding.
To ensure that the analysis made by SQL Performance Analyzer is accurate, the test system should resemble the production system as closely as possible because the performance on both systems will be compared to each other. Furthermore, the hardware configurations on both systems should also be as similar as possible.
Next, you will need to set up a separate SQL Performance Analyzer system running Oracle Database 11g Release 2. You will be using this system to build a SQL tuning set and to run SQL Performance Analyzer. Neither your production data or schema need to be available on this system, since the SQL tuning set will be built using statistics stored in the SQL trace files from the production system. SQL Performance Analyzer tasks will be executed remotely on the test system to generate the execution plan and statistics for the SQL trial over a database link that you specify. The database link must be a public database link that connects to a user with the EXECUTE privilege for the DBMS_SQLPA package and the ADVISOR privilege on the test system. You should also drop any existing PLAN_TABLE from the user's schema on the test system.
Once the upgrade environment is configured as described, perform the steps as described in the following procedure to use SQL Performance Analyzer in a database upgrade from Oracle9i or Oracle Database 10g Release 1 to a newer release.
Enable the SQL Trace facility on the production system, as described in "Enabling SQL Trace on the Production System".
To minimize the performance impact on the production system and still be able to fully capture a representative set of SQL statements, consider enabling SQL Trace for only a subset of the sessions, for as long as required, to capture all important SQL statements at least once.
On the production system, create a mapping table, as described in "Creating a Mapping Table".
This mapping table will be used to convert the user and object identifier numbers in the SQL trace files to their string equivalents.
Move the SQL trace files and the mapping table from the production system to the SQL Performance Analyzer system, as described in "Creating a Mapping Table".
On the SQL Performance Analyzer system, construct a SQL tuning set using the SQL trace files, as described in "Building a SQL Tuning Set".
The SQL tuning set will contain the SQL statements captured in the SQL trace files, along with their relevant execution context and statistics.
On the SQL Performance Analyzer system, use SQL Performance Analyzer to create a SQL Performance Analyzer task and convert the contents in the SQL tuning set into a pre-upgrade SQL trial that will be used as a baseline for comparison, then remotely test execute the SQL statements on the test system over a database link to build a post-upgrade SQL trial, as described in "Testing Database Upgrades from Oracle9i Database and Oracle Database 10g Release 1".
Compare SQL performance and fix regressed SQL.
SQL Performance Analyzer compares the performance of SQL statements read from the SQL tuning set during the pre-upgrade SQL trial to those captured from the remote test execution during the post-upgrade SQL trial. A report is produced to identify any changes in execution plans or performance of the SQL statements.
If the report reveals any regressed SQL statements, you can make further changes to fix the regressed SQL, as described in "Tuning Regressed SQL Statements After Testing a Database Upgrade".
Repeat the process of executing the SQL tuning set and comparing its performance to a previous execution to test any changes made until you are satisfied with the outcome of the analysis.
Oracle9i uses the SQL Trace facility to collect performance data on individual SQL statements. The information generated by SQL Trace is stored in SQL trace files. SQL Performance Analyzer consumes the following information from these files:
SQL text and username under which parse occurred
Bind values for each execution
CPU and elapsed times
Physical reads and logical reads
Number of rows processed
Execution plan for each SQL statement (only captured if the cursor for the SQL statement is closed)
Although it is possible to enable SQL Trace for an instance, it is recommended that you enable SQL Trace for a subset of sessions instead. When the SQL Trace facility is enabled for an instance, performance statistics for all SQL statements executed in the instance are stored into SQL trace files. Using SQL Trace in this way can have a severe performance impact and may result in increased system overhead, excessive CPU usage, and inadequate disk space. It is required that trace level be set to 4 to capture bind values, along with the execution plans.
For production systems running Oracle Database 10g Release 1, use the DBMS_MONITOR.SESSION_TRACE_ENABLE procedure to enable SQL Trace transparently in another session. You should also enable binds explicitly by setting the binds procedure parameter to TRUE (its default value is FALSE).
After enabling SQL Trace, identify the SQL trace files containing statistics for a representative set of SQL statements that you want to use with SQL Performance Analyzer. You can then copy the SQL trace files to the SQL Performance Analyzer system. Once the SQL workload is captured in the SQL trace files, disable SQL Trace on the production system.
|
See Also:
|
To convert the user and object identifier numbers stored in the SQL trace files to their respective names, you need to provide a table that specifies each mapping. The SQL Performance Analyzer system will read this mapping table when converting the trace files into a SQL tuning set.
To create a mapping table, run the following SQL statements on the production database:
CREATE TABLE mapping AS
SELECT object_id id, owner, SUBSTR(object_name, 1, 30) name FROM dba_objects
WHERE object_type NOT IN ('CONSUMER GROUP', 'EVALUATION CONTEXT', 'FUNCTION',
'INDEXTYPE', 'JAVA CLASS', 'JAVA DATA',
'JAVA RESOURCE', 'LIBRARY', 'LOB', 'OPERATOR',
'PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'QUEUE',
'RESOURCE PLAN', 'SYNONYM', 'TRIGGER', 'TYPE',
'TYPE BODY')
UNION ALL
SELECT user_id id, username owner, null name FROM dba_users;
Once the mapping table is created, you can use Data Pump to transport it to the SQL Performance Analyzer system.
Once the SQL trace files and mapping table are moved to the SQL Performance Analyzer system, you can build a SQL tuning set using the DBMS_SQLTUNE package.
Copy the SQL trace files to a directory on the SQL Performance Analyzer system.
Create a directory object for this directory.
Use the DBMS_SQLTUNE.SELECT_SQL_TRACE function to read the SQL statements from the SQL trace files.
For each SQL statement, only information for a single execution is collected. The execution frequency of each SQL statement is not captured. Therefore, when performing a comparison analysis for a production system running Oracle Database 10g Release 1 and older releases, you should ignore the workload-level statistics in the SQL Performance Analyzer report and only evaluate performance changes on an execution level.
The following example reads the contents of SQL trace files stored in the sql_trace_prod directory object and loads them into a SQL tuning set.
DECLARE
cur sys_refcursor;
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET('my_sts_9i');
OPEN cur FOR
SELECT VALUE (P)
FROM table(DBMS_SQLTUNE.SELECT_SQL_TRACE('sql_trace_prod', '%ora%')) P;
DBMS_SQLTUNE.LOAD_SQLSET('my_sts_9i', cur);
CLOSE cur;
END;
/
The syntax for the SELECT_SQL_TRACE function is as follows:
DBMS_SQLTUNE.SELECT_SQL_TRACE (
directory IN VARCHAR2,
file_name IN VARCHAR2 := NULL,
mapping_table_name IN VARCHAR2 := NULL,
mapping_table_owner IN VARCHAR2 := NULL,
select_mode IN POSITIVE := SINGLE_EXECUTION,
options IN BINARY_INTEGER := LIMITED_COMMAND_TYPE,
pattern_start IN VARCHAR2 := NULL,
parttern_end IN VARCHAR2 := NULL,
result_limit IN POSITIVE := NULL)
RETURN sys.sqlset PIPELINED;
Table 7-1 describes the available parameters for the SELECT_SQL_TRACE function.
Table 7-1 DBMS_SQLTUNE.SELECT_SQL_TRACE Function Parameters
| Parameter | Description |
|---|---|
|
|
Specifies the directory object pointing to the directory where the SQL trace files are stored. |
|
|
Specifies all or part of the name of the SQL trace files to process. If unspecified, the current or most recent trace file in the specified directory will be used. % wildcards are supported for matching trace file names. |
|
|
Specifies the name of the mapping table. If set to the default value of |
|
|
Specifies the schema where the mapping table resides. If set to |
|
|
Specifies the mode for selecting SQL statements from the trace files. The default value is |
|
|
Specifies the options for the operation. The default value is |
|
|
Specifies the opening delimiting pattern of the trace file sections to consider. This parameter is currently not used. |
|
|
Specifies the closing delimiting pattern of the trace file sections to process. This parameter is currently not used. |
|
|
Specifies the top SQL from the (filtered) source. The default value is 231, which represents unlimited. |
|
See Also:
|
Once the SQL tuning set is built, you can use SQL Performance Analyzer to build a pre-upgrade SQL trial from the execution plans and run-time statistics in the SQL tuning set. After the pre-upgrade SQL trial is built, you need to perform a test execute or generate plans of SQL statements in the SQL tuning set on the test system to build a post-upgrade SQL trial. SQL Performance Analyzer test executes the SQL statements using a public database link that you specify by connecting to the test system remotely and generating the execution plans and statistics for the SQL trial. The database link should exist on the SQL Performance Analyzer system and connect to a remote user with privileges to execute the SQL tuning set on the test system.
You can run SQL Performance Analyzer to test a database upgrade from Oracle9i Database or Oracle Database 10g Release 1 using Oracle Enterprise Manager or APIs, as described in the following sections:
To test a database upgrade from Oracle9i Database or Oracle Database 10g Release 1 using SQL Performance Analyzer:
On the Software and Support page, under Real Application Testing, click SQL Performance Analyzer.
The SQL Performance Analyzer page appears.
Under SQL Performance Analyzer Workflows, click Upgrade from 9i or 10.1.
The Upgrade from 9i or 10.1 page appears.
Under Task Information:
In the Task Name field, enter the name of the task.
In the SQL Tuning Set field, enter the name of the SQL tuning set that was built.
Alternatively, click the search icon to search for the SQL tuning set using the Search and Select: SQL Tuning Set window.
The selected SQL tuning set now appears in the SQL Tuning Set field.
In the Description field, optionally enter a description of the task.
In the Creation Method field, select:
Execute SQLs to generate both execution plans and statistics for each SQL statement in the SQL tuning set by actually running the SQL statements remotely on the test system over a public database link.
Generate Plans to create execution plans remotely on the test system over a public database link without actually running the SQL statements.
In the Per-SQL Time Limit list, determine the time limit for SQL execution during the trial by performing one of the following actions:
Select 5 minutes.
The execution will run each SQL statement in the SQL tuning set up to 5 minutes and gather performance data.
Select Unlimited.
The execution will run each SQL statement in the SQL tuning set to completion and gather performance data. Collecting execution statistics provides greater accuracy in the performance analysis but takes a longer time. Using this setting is not recommended because the task may be stalled by one SQL statement for a prolonged time period.
Select Customize and enter the specified number of seconds, minutes, or hours.
In the Database Link field, enter the global name of a public database link connecting to a user with the EXECUTE privilege for the DBMS_SQLPA package and the ADVISOR privilege on the test system.
Alternatively, click the search icon to search for and select a database link, or click Create Database Link to create a database link using the Create Database Link page.
In the Comparison Metric list, select the comparison metric to use for the comparison analysis:
Elapsed Time
CPU Time
User I/O Time
Buffer Gets
Physical I/O
Optimizer Cost
I/O Interconnect Bytes
Optimizer Cost is the only comparison metric available if you generated execution plans only in the SQL trials.
To perform the comparison analysis by using more than one comparison metric, perform separate comparison analyses by repeating this procedure with different metrics.
Under Schedule:
In the Time Zone list, select your time zone code.
Select Immediately to start the task now, or Later to schedule the task to start at a time specified using the Date and Time fields.
Click Submit.
The SQL Performance Analyzer page appears.
In the SQL Performance Analyzer Tasks section, the status of this task is displayed. To refresh the status icon, click Refresh. After the task completes, the Status field changes to Completed.
Under SQL Performance Analyzer Tasks, select the task and click the link in the Name column.
The SQL Performance Analyzer Task page appears.
This page contains the following sections:
SQL Tuning Set
This section summarizes information about the SQL tuning set, including its name, owner, description, and the number of SQL statements it contains.
SQL Trials
This section includes a table that lists the SQL trials used in the SQL Performance Analyzer task.
SQL Trial Comparisons
This section contains a table that lists the results of the SQL trial comparisons
Click the icon in the Comparison Report column.
The SQL Performance Analyzer Task Result page appears.
Review the results of the performance analysis, as described in "Reviewing the SQL Performance Analyzer Report Using Oracle Enterprise Manager".
If regressed SQL statements are found following the database upgrade, tune them as described in "Tuning Regressed SQL Statements After Testing a Database Upgrade".
After creating a SQL Performance Analyzer task on the SQL Performance Analyzer system, you can use APIs to build the pre-upgrade SQL trial from the execution plans and run-time statistics in the SQL tuning set. To do so, 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 to CONVERT SQLSET to direct SQL Performance Analyzer to treat the statistics in the SQL tuning set as a trial execution.
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.
The following example executes the SQL Performance Analyzer task named my_spa_task as a trial execution:
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'my_spa_task', -
execution_type => 'CONVERT SQLSET', -
execution_name => 'my_trial_9i');
To build the post-upgrade SQL trial using APIs, perform an explain plan or test execute using the SQL Performance Analyzer system by calling the EXECUTE_ANALYSIS_TASK procedure. Set the DATABASE_LINK task parameter to the global name of a public database link connecting to a user with the EXECUTE privilege for the DBMS_SQLPA package and the ADVISOR privilege on the test system.
If you choose to use EXPLAIN PLAN, only execution plans will be generated. Subsequent comparisons will only be able to yield a list of changed plans without making any conclusions about performance changes. If you choose to use TEST EXECUTE, the SQL workload will be executed to completion. This effectively builds the post-upgrade SQL trial using the statistics and execution plans generated from the test system. Using TEST EXECUTE is recommended to capture the SQL execution plans and performance data at the source, thereby resulting in a more accurate analysis.
The following example performs a test execute of the SQL statements remotely over a database link:
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'my_spa_task', -
execution_type => 'TEST EXECUTE', -
execution_name => 'my_remote_trial_10g', -
execution_params => dbms_advisor.arglist('database_link',
'LINK.A.B.C.BIZ.COM'));
You can use SQL Performance Analyzer to test the impact on SQL response time of a database upgrade from Oracle Database 10g Release 2 or a newer release to any later release by capturing a SQL tuning set on the production system, then executing it twice remotely over a database link on a test system—first to create a pre-change SQL trial, then again to create a post-change SQL trial.
The production system which you are upgrading from should be running Oracle Database 10g Release 2 or a newer release. Initially, the test system should also be running the same release. To ensure that the analysis made by SQL Performance Analyzer is accurate, the test system should contain an exact copy of the production data found on the production system. Furthermore, the hardware configuration should also be as similar to the production system as possible.
Next, you will need to set up a separate SQL Performance Analyzer system running Oracle Database 11g Release 2. You will be using this system to run SQL Performance Analyzer. Neither your production data or schema need to be available on this system, since the SQL tuning set will be built using statistics stored in the SQL trace files from the production system. SQL Performance Analyzer tasks will be executed remotely on the test system to generate the execution plan and statistics for the SQL trial over a database link that you specify. The database link must be a public database link that connects to a user with the EXECUTE privilege for the DBMS_SQLPA package and the ADVISOR privilege on the test system. You should also drop any existing PLAN_TABLE from the user's schema on the test system.
Once the upgrade environment is configured as described, perform the steps as described in the following procedure to use SQL Performance Analyzer in a database upgrade from Oracle Database 10g Release 2 or a newer release to any later release.
On the production system, capture the SQL workload that you intend to analyze and store it in a SQL tuning set, as described in "Capturing the SQL Workload".
Set up the test system so that it matches the production environment as closely as possible, as described in "Setting Up the Test System".
Transport the SQL tuning set to the SQL Performance Analyzer system.
For information about transporting SQL tuning sets using:
Oracle Enterprise Manager, see Oracle Database 2 Day + Performance Tuning Guide
APIs, see Oracle Database Performance Tuning Guide
On the SQL Performance Analyzer system, create a SQL Performance Analyzer task using the SQL tuning set as its input source.
Remotely test execute the SQL statements in the SQL tuning set on the test system over a database link to build a pre-upgrade SQL trial that will be used as a baseline for comparison, as described in "Testing Database Upgrades from Oracle Database 10g Release 2 and Newer Releases".
Upgrade the test system.
Remotely test execute the SQL statements a second time on the upgraded test system over a database link to build a post-upgrade SQL trial, as described in "Testing Database Upgrades from Oracle Database 10g Release 2 and Newer Releases".
Compare SQL performance and fix regressed SQL.
SQL Performance Analyzer compares the performance of SQL statements read from the SQL tuning set during the pre-upgrade SQL trial to those captured from the remote test execution during the post-upgrade SQL trial. A report is produced to identify any changes in execution plans or performance of the SQL statements.
If the report reveals any regressed SQL statements, you can make further changes to fix the regressed SQL, as described in "Tuning Regressed SQL Statements After Testing a Database Upgrade".
Repeat the process of executing the SQL tuning set and comparing its performance to a previous execution to test any changes made until you are satisfied with the outcome of the analysis.
Once the SQL tuning set is transported to the SQL Performance Analyzer system, you can use SQL Performance Analyzer to build a pre-upgrade SQL trial by executing or generating plans of SQL statements in the SQL tuning set on the test system. SQL Performance Analyzer test executes the SQL statements using a database link that you specify by connecting to the test system remotely and generating the execution plans and statistics for the SQL trial. The database link should exist on the SQL Performance Analyzer system and connect to a remote user with privileges to execute the SQL tuning set on the test system.
After the pre-upgrade SQL trial is built, you need to upgrade the test system. Once the database has been upgraded, SQL Performance Analyzer will need to execute or generate plans of SQL statements in the SQL tuning set a second time on the upgraded test system to build a post-upgrade SQL trial. Alternatively, if hardware resources are available, you can use another upgraded test system to execute the second remote SQL trial. This method can be useful in helping you investigate issues identified by SQL Performance Analyzer.
You can run SQL Performance Analyzer to test a database upgrade from Oracle Database 10g Release 2 or a newer release using Oracle Enterprise Manager or APIs, as described in the following sections:
To test a database upgrade from Oracle Database 10g Release 2 or a newer release using SQL Performance Analyzer:
On the Software and Support page, under Real Application Testing, click SQL Performance Analyzer.
The SQL Performance Analyzer page appears.
Under SQL Performance Analyzer Workflows, click Upgrade from 10.2 or 11g.
The Upgrade from 10.2 or 11g page appears.
Under Task Information:
In the Task Name field, enter the name of the task.
In the SQL Tuning Set field, enter the name of the SQL tuning set that was built.
Alternatively, click the search icon to search for the SQL tuning set using the Search and Select: SQL Tuning Set window.
The selected SQL tuning set now appears in the SQL Tuning Set field.
In the Description field, optionally enter a description of the task.
In the Creation Method field, select:
Execute SQLs to generate both execution plans and statistics for each SQL statement in the SQL tuning set by actually running the SQL statements remotely on the test system over a public database link.
Generate Plans to create execution plans remotely on the test system over a public database link without actually running the SQL statements.
In the Per-SQL Time Limit list, determine the time limit for SQL execution during the trial by performing one of the following actions:
Select 5 minutes.
The execution will run each SQL statement in the SQL tuning set up to 5 minutes and gather performance data.
Select Unlimited.
The execution will run each SQL statement in the SQL tuning set to completion and gather performance data. Collecting execution statistics provides greater accuracy in the performance analysis but takes a longer time. Using this setting is not recommended because the task may be stalled by one SQL statement for a prolonged time period.
Select Customize and enter the specified number of seconds, minutes, or hours.
In the Database Link field, enter the global name of a public database link connecting to a user with the EXECUTE privilege for the DBMS_SQLPA package and the ADVISOR privilege on the pre-upgrade system.
Alternatively, click the search icon to search for and select a database link, or click Create Database Link to create a database link using the Create Database Link page.
Under Post-upgrade Trial:
Select Use the same system as in the pre-upgrade trial to use the same system for executing both the pre-upgrade and post-upgrade trials.
Oracle recommends using this option to avoid possible errors due to different system configurations. When using this option, you will need to upgrade the test database to the higher database version before the post-upgrade trial is executed.
In the Database Link field, enter the global name of a public database link connecting to a user with the EXECUTE privilege for the DBMS_SQLPA package and the ADVISOR privilege on the post-upgrade system.
In the Comparison Metric list, select the comparison metric to use for the comparison analysis:
Elapsed Time
CPU Time
User I/O Time
Buffer Gets
Physical I/O
Optimizer Cost
I/O Interconnect Bytes
Optimizer Cost is the only comparison metric available if you generated execution plans only in the SQL trials.
To perform the comparison analysis by using more than one comparison metric, perform separate comparison analyses by repeating this procedure with different metrics.
Under Schedule:
In the Time Zone list, select your time zone code.
Select Immediately to start the task now, or Later to schedule the task to start at a time specified using the Date and Time fields.
Click Submit.
The SQL Performance Analyzer page appears.
In the SQL Performance Analyzer Tasks section, the status of this task is displayed. To refresh the status icon, click Refresh.
If you are using the same system to execute both the pre-upgrade and post-upgrade trials, you will need to upgrade the database after the pre-upgrade trial step is completed. After the database is upgraded, the post-upgrade trial can be executed. After the task completes, the Status field changes to Completed.
Under SQL Performance Analyzer Tasks, select the task and click the link in the Name column.
The SQL Performance Analyzer Task page appears.
This page contains the following sections:
SQL Tuning Set
This section summarizes information about the SQL tuning set, including its name, owner, description, and the number of SQL statements it contains.
SQL Trials
This section includes a table that lists the SQL trials used in the SQL Performance Analyzer task.
SQL Trial Comparisons
This section contains a table that lists the results of the SQL trial comparisons
Click the icon in the Comparison Report column.
The SQL Performance Analyzer Task Result page appears.
Review the results of the performance analysis, as described in "Reviewing the SQL Performance Analyzer Report Using Oracle Enterprise Manager".
If regressed SQL statements are found following the database upgrade, tune them as described in "Tuning Regressed SQL Statements After Testing a Database Upgrade".
After creating a SQL Performance Analyzer task on the SQL Performance Analyzer system, you can use APIs to build the pre-upgrade SQL trial by performing an explain plan or test execute of SQL statements in the SQL tuning set. To do so, 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 to EXPLAIN PLAN or TEST EXECUTE.
If you choose to use EXPLAIN PLAN, only execution plans will be generated. Subsequent comparisons will only be able to yield a list of changed plans without making any conclusions about performance changes. If you choose to use TEST EXECUTE, the SQL workload will be executed to completion. This effectively builds the pre-upgrade SQL trial using the statistics and execution plans generated from the test system. Using TEST EXECUTE is recommended to capture the SQL execution plans and performance data at the source, thereby resulting in a more accurate analysis.
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.
Set the DATABASE_LINK task parameter to the global name of a public database link connecting to a user with the EXECUTE privilege for the DBMS_SQLPA package and the ADVISOR privilege on the test system.
The following example executes the SQL Performance Analyzer task named my_spa_task and performs a test execute of the SQL statements remotely over a database link:
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'my_spa_task', -
execution_type => 'TEST EXECUTE', -
execution_name => 'my_remote_trial_10g', -
execution_params => dbms_advisor.arglist('database_link',
'LINK.A.B.C.BIZ.COM'));
To build the post-upgrade SQL trial using APIs, perform an explain plan or test execute using the SQL Performance Analyzer system by calling the EXECUTE_ANALYSIS_TASK procedure with the DATABASE_LINK task parameter set to the global name of a public database link connecting to a user with the EXECUTE privilege for the DBMS_SQLPA package and the ADVISOR privilege on the test system. If you choose to use EXPLAIN PLAN, only execution plans will be generated. Subsequent comparisons will only be able to yield a list of changed plans without making any conclusions about performance changes. If you choose to use TEST EXECUTE, the SQL workload will be executed to completion. This effectively builds the post-upgrade SQL trial using the statistics and execution plans generated from the test system. Using TEST EXECUTE is recommended to capture the SQL execution plans and performance data at the source, thereby resulting in a more accurate analysis.
The following example performs a test execute of the SQL statements remotely over a database link:
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'my_spa_task', -
execution_type => 'TEST EXECUTE', -
execution_name => 'my_remote_trial_11g', -
execution_params => dbms_advisor.arglist('database_link',
'LINK.A.B.C.BIZ.COM'));
In some cases, SQL Performance Analyzer may identify SQL statements whose performance regressed after you upgrade the database on the test system.
You can tune the regressed SQL statements by using the SQL Tuning Advisor or SQL plan baselines, as described in Chapter 6, "Comparing SQL Trials". This involves using APIs to build a subset of a SQL tuning set with only the regressed SQL statements, transport this subset of regressed SQL statements to the remote database, and running the SQL Tuning Advisor on the remote database.
Oracle Enterprise Manager does not provide support for fixing regressions after running SQL Performance Analyzer involving one or more remote SQL trials. For more information, see "Tuning Regressed SQL Statements From a Remote SQL Trial Using APIs".
If you are upgrading from Oracle Database 10g Release 2 and newer releases, you can also create SQL plan baselines to instruct the optimizer to select existing execution plans in the future. For more information, see "Creating SQL Plan Baselines Using APIs".
Oracle Database offers test data management features that enable you to:
Store the list of applications, tables, and relationships between table columns using Application Data Modeling
Replicate information that pertains only to a particular site using data subsetting
Replace sensitive data from your production system with fictitious data that can be used during testing using Oracle Data Masking
Part III contains the following chapters:
Real Application Testing User's Guide
11g Release 2 (11.2)
E16540-06
December 2011
Oracle Database Real Application Testing User's Guide, 11g Release 2 (11.2)
E16540-06
Copyright © 2008, 2011, Oracle and/or its affiliates. All rights reserved.
Primary Author: Immanuel Chan
Contributing Author: Mike Zampiceni
Contributors: Ashish Agrawal, Lance Ashdown, Pete Belknap, Supiti Buranawatanachoke, Romain Colle, Karl Dias, Kurt Engeleiter, Leonidas Galanis, Veeranjaneyulu Goli, Prabhaker Gongloor, Prakash Gupta, Shantanu Joshi, Prathiba Kalirengan, Karen McKeen, Mughees Minhas, Valarie Moore, Ravi Pattabhi, Yujun Wang, Keith Wong, Khaled Yagoub, Hailing Yu
This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.
The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.
If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:
U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to U.S. Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use, duplication, disclosure, modification, and adaptation shall be subject to the restrictions and license terms set forth in the applicable Government contract, and, to the extent applicable by the terms of the Government contract, the additional rights set forth in FAR 52.227-19, Commercial Computer Software License (December 2007). Oracle America, Inc., 500 Oracle Parkway, Redwood City, CA 94065.
This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.
This software or hardware and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.
Oracle Real Application Testing option enables you to perform real-world testing of Oracle Database. By capturing production workloads and assessing the impact of system changes before production deployment, Oracle Real Application Testing minimizes the risk of instabilities associated with changes.
Oracle Real Application Testing comprises the following components:
SQL Performance Analyzer and Database Replay are complementary solutions that can be used for real application testing. Depending on the nature and impact of the system change, and on which system the test will be performed (production or test), you can use either one to perform your testing.
|
Note: The use of SQL Performance Analyzer and Database Replay requires the Oracle Real Application Testing licensing option. For more information, see Oracle Database Licensing Information. |
System changes—such as a upgrading a database or adding an index—may cause changes to execution plans of SQL statements, resulting in a significant impact on SQL performance. In some cases, the system changes may cause SQL statements to regress, resulting in performance degradation. In other cases, the system changes may improve SQL performance. Being able to accurately forecast the potential impact of system changes on SQL performance enables you to tune the system beforehand, in cases where the SQL statements regress, or to validate and measure the performance gain in cases where the performance of the SQL statements improves.
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 SQL 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, you can remedy any negative outcome before the end users are affected. Furthermore, you can validate—with significant time and cost savings—that the system change to the production environment will result in net improvement.
You can use the SQL Performance Analyzer to analyze the impact on SQL performance of any type of system changes, including:
Database upgrade
Configuration changes to the operating system or hardware
Schema changes
Changes to database initialization parameters
Refreshing optimizer statistics
SQL tuning actions
Before system changes are made, such as hardware and software upgrades, extensive testing is usually performed in a test environment to validate the changes. However, despite the testing, the new system often experiences unexpected behavior when it enters production because the testing was not performed using a realistic workload. The inability to simulate a realistic workload during testing is one of the biggest challenges when validating system changes.
Database Replay enables realistic testing of system changes by essentially re-creating the production workload environment on a test system. Using Database Replay, you can capture a workload on the production system and replay it on a test system with the exact timing, concurrency, and transaction characteristics of the original workload. This enables you to fully assess the impact of the change, including undesired results, new contention points, or plan regressions. Extensive analysis and reporting is provided to help identify any potential problems, such as new errors encountered and performance divergence.
Database Replay performs workload capture of external client workload at the database level and has negligible performance overhead. Capturing the production workload eliminates the need to develop simulation workloads or scripts, resulting in significant cost reduction and time savings. By using Database Replay, realistic testing of complex applications that previously took months using load simulation tools can now be completed in days. This enables you to rapidly test changes and adopt new technologies with a higher degree of confidence and at lower risk.
You can use Database Replay to test any significant system changes, including:
Database and operating system upgrades
Configuration changes, such as conversion of a database from a single instance to an Oracle Real Application Clusters (Oracle RAC) environment
Storage, network, and interconnect changes
Operating system and hardware migrations
When production data is copied into a testing environment, there is the risk of breaching sensitive information to non-production users, such as application developers or external consultants. In order to perform real-world testing, these non-production users need to access some of the original data, but not all the data, especially when the information is deemed confidential.
Oracle Data Masking helps reduce this risk by replacing sensitive data from your production system with fictitious data so that production data can be shared safely with non-production users during testing. Oracle Data Masking provides end-to-end secure automation for provisioning test databases from production in compliance with regulations.
SQL Performance Analyzer enables you to assess the impact of system changes on the response time of SQL statements.
Part I contains the following chapters:
This preface contains the following topics:
This document provides information about how to assure the integrity of database changes using Oracle Real Application Testing. This document is intended for database administrators, application designers, and programmers who are responsible for performing real application testing on Oracle Database.
For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.
Access to Oracle Support
Oracle customers have access to electronic support through My Oracle Support. For information, visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info or visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs if you are hearing impaired.
For more information about some of the topics discussed in this document, see the following documents in the Oracle Database Release 11.2 documentation set:
The following text conventions are used in this document:
| Convention | Meaning |
|---|---|
| boldface | Boldface type indicates graphical user interface elements associated with an action, or terms defined in text or the glossary. |
| italic | Italic type indicates book titles, emphasis, or placeholder variables for which you supply particular values. |
monospace | Monospace type indicates commands within a paragraph, URLs, code in examples, text that appears on the screen, or text that you enter. |
dp Od >ȁ" 먇FC C.b *?"@&VdXGj ВkPtW˵=z?wE_ͻ{5]'oSϼ7y3 D GOқOWֻgOϽwOOA .wЏO}XϾ}_O~}OuOϿ/ h@p
` &VRhFxx( [ Π|؇krzinnĈ"!h8hvyyw G% @xa8WV[ zU ` 4Quu`
9
(HTN|F\DK(8Ix1Wer8t~bk8 mȇ| w؇(pu8ԧSxp8a(臨؊wUȈx8~HVxȊ8XgUxH Xʸ艹Ƹ؍thXȎ(8XRa(H`8xh
m(
(h9혈kxؐ$Y& c0)#ؒ/'y8N("<AHJy젎DɀДOQ9 \ٕ]y`b9dYfflIhr97) xz|ٗ{>a~9 t`1YyJ! 0 #@N P!c2a> 9y @ y `
/ 09 ٛIiiȩ0I II) 9y4*9 I8 * ڠ1 p/0
* #
'Z4 +ڢy2J01EB əq 9Qa AN pWpyɞ 3qB yab rJǙ mtJhk pfjʦqڦ}XRI
KڤPY['* p:7{ڢYJ 'J71 *rښ ٚHʙ
v@jК Ū}p'BXD
yy [jךyI 7K bڨn2 /a ٨omə9oڞQ?Ή
bʙ5 r*j2ڰ5q2AZʠJL
L%+3*
.
x`.ښ z 3z@ F+$M ?JYH
j 9 *)#@ d{Q
JUڜ)y9p
v+
>+%Ifj [:pm *ڸP˸ٹ k]++
R{D}78j1!)+K.+Jy; Щ-{#{zAٲk5; o[T֊f۱ \[b 9 BRٶi K{K;:in
P:qڥ ʝ\| 튣tj %ܜ \Qھ Z(,J:/!S [4k6k(ۿ z*<JJ̲i
,6*dj
kBq k_)|,$y I4A8?+
>\㚿=`
\*L*- ɝ9 l,yʖ;ex)Ɏ?|4Ij|RڴA˩ {QrzXZ< t/+ڌ
{KR=x%='ҡ-=5ۊ== XI @Q=S}K]RNC=ei3qu}%} =z؉=g:ٕ W١}ڃڭ}ѳ}4ֹ=ȱsŽýܕc}
c7Hc#ܽ.9B%]"ԣ#ԣ'}/`2 " `,* ,21Ȁ5+# `+0 1 c5` 8&8V"U#2 .+#80b"BM 7} !>"#B28>=*# !B1`#rR%>58@$ C%$^78`Ib/ C +3 /" +: 7C#l7n9 5>AB "^B*c9 "^~%^(B:*6ݞX #\ +t: )b2+O- 684H#-:V?߽ ?ڽǟez?Fp_?3 B <0
:|1ĉ
iPx"ƍ5r2ȈJ<2ʕ,[| 3̙4kڼ3Ν<{ '͑D=tǤ 2ԩDZ5֭\z
kJd˚E,ôj+} عtڽ7ΰv-` بŌ;~ &ߺ+[6zX;h 2 yYF2E Ъ1F
<;'ӕ<9ECCpIg-A odvܓzgZ|A?X}#ۇl|e 'u 6DxaԂ ^{(} ~b>'V&Hh<g ,Yl ZvYl@`u@k
ރd$ 9ZY)Pz+&Ԙk xK.yƨfg^o"މgzDW( sa(fA: A^UHih4F)1jfb%n@gd2Z&)AVXA:I{Kl&(DjX(lж(
A^f(jJx
Q9n^m[n%ۛ@znvYE pt!ղ'C.v*&+@3hZlMZ(kzn)dl2-'rj nۦzA 08'ɡ
?
u91VCs[iOaԱv]r^Kd\Ӯ x{
e\'hFe#Z0ER/x,QՒuC㫨}2Ӫ]v#-ciW" ~fy蝷y;]>뢺˘D/ϼԐk5y" n3
Y[馮("`陞WLNj1BiF\ 4K-1˞ 4d3 gÏuTh~#5%q62XIܠGLmTDi1pl1GSNA:Ӛ
$d@Q , E @SCELH?ϯO}V G˾Ź[\шiHd]GZR>Z_^[EOxJԚd9v1LW*!u\I=u$5ʵ
" Z"*I("ahDY VW'zbau-aP̶ʔ3 ˰\y!aX 0ux"Ҵ,yN^Q،)R`)j*DO`i( B ;5TT" rC-;|c(
HE6@dCtvR&F(PBb&$DF@dGV -d 5K DvGb j-ZdXcvy}J$ ~(d@dyq'aY#TM#Kr$K$zdO JF`b<U0owxPeW
%(VReUze%e)M!eRQdCVl⛓HeYoX%%^Q(v_&^YeaTSepQIc[
'cX0!XA@ qK&W
s
gfDA1S{&ngB