12 Testing a Database Upgrade

This chapter describes how to use SQL Performance Analyzer in a database upgrade. For information about using SQL Performance Analyzer in other cases, see "SQL Performance Analyzer".

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 Oracle Database 10g Release 1 and previous releases 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 in this chapter.

This chapter contains the following sections:

Upgrading from Oracle Database 10g Release 1 and Older Releases

SQL Performance Analyzer supports testing database upgrades of Oracle9i and Oracle Database 10g Release 1 to Oracle Database 10g Release 2 and later releases by executing the SQL tuning set on the upgraded database remotely over a database link, as illustrated in Figure 12-1. 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, a SQL tuning set needs to be constructed so that it can be used as an input source for SQL Performance Analyzer if you are upgrading from Oracle9i.

Figure 12-1 SQL Performance Analyzer Workflow for Database Upgrade from Oracle9i to Oracle Database 10g Release 2

Description of Figure 12-1 follows
Description of "Figure 12-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 contain an exact copy of the data found on the production system 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 1 or a newer release. The database version should be release 11.1.0.7 or higher. 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.

  1. 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.

  2. 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.

  3. 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".

  4. 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.

  5. On the SQL Performance Analyzer system, use SQL Performance Analyzer to build a pre-upgrade SQL trial and a post-upgrade SQL trial:

    1. Create a new 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, as described in "Building the Pre-Upgrade SQL Trial for Oracle 10g Release 1 and Older Releases".

    2. Remotely test execute the SQL statements on the test system over a database link to build a post-upgrade SQL trial, as described in "Building the Post-Upgrade SQL Trial Upgrading from Oracle Database 10g Release 1 and Older Releases".

  6. 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. You can then repeat the process of executing the SQL tuning set and comparing its performance to a previous execution to test any fixes or additional changes made. Repeat these steps until you are satisfied with the outcome of the analysis.

Enabling SQL Trace on the Production System

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:

Creating a Mapping Table

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.

See Also:

Oracle Database Utilities for information about using Data Pump

Building a SQL Tuning Set

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.

To build a SQL tuning set:

  1. Copy the SQL trace files to a directory on the SQL Performance Analyzer system.

  2. Create a directory object for this directory.

  3. 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 := 'mapping',
    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 12-1 describes the available parameters for the SELECT_SQL_TRACE function.

Table 12-1 DBMS_SQLTUNE.SELECT_SQL_TRACE Function Parameters

Parameter Description

directory

Specifies the directory object pointing to the directory where the SQL trace files are stored.

file_name

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.

mapping_table_name

Specifies the name of the mapping table. The default mapping table name is mapping. Note that the mapping table name is not case-sensitive.

mapping_table_owner

Specifies the schema where the mapping table resides. If set to NULL, the current schema will be used.

select_mode

Specifies the mode for selecting SQL statements from the trace files. The default value is SINGLE_EXECUTION. In this mode, only statistics for a single execution per SQL statement will be loaded into the SQL tuning set. The statistics are not cumulative, as is the case with other SQL tuning set data source table functions.

options

Specifies the options for the operation. The default value is LIMITED_COMMAND_TYPE, only SQL types that are meaningful to SQL Performance Analyzer (such as SELECT, INSERT, UPDATE, and DELETE) are returned from the SQL trace files.

pattern_start

Specifies the opening delimiting pattern of the trace file sections to consider. This parameter is currently not used.

pattern_end

Specifies the closing delimiting pattern of the trace file sections to process. This parameter is currently not used.

result_limit

Specifies the top SQL from the (filtered) source. The default value is MAXSB4.

See Also:

Oracle Database PL/SQL Packages and Types Reference for information about the DBMS_SQLTUNE package

Building the Pre-Upgrade SQL Trial for Oracle 10g Release 1 and Older Releases

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.

You can build the pre-upgrade SQL trial using Oracle Enterprise Manager or APIs, as described in the following sections:

Building the Pre-Upgrade SQL Trial for Oracle 10g Release 1 and Older Releases Using Enterprise Manager

Once the SQL tuning set is built, you can build a pre-upgrade SQL trial by running SQL Performance Analyzer on the SQL Performance Analyzer system.

To build the pre-upgrade SQL trial using Enterprise Manager:

  1. On the Database Home page, click Advisor Central.

    The Advisor Central page appears.

  2. Click SQL Performance Analyzer.

    The SQL Performance Analyzer page appears.

  3. Click Guided Workflow.

    The Guided Workflow page appears.

  4. Create a SQL Performance Analyzer task using the SQL tuning set as its input source:

    1. On the Guided Workflow page, click the Execute icon for the Step 1: Create SQL Performance Analyzer Task based on SQL Tuning Set.

      The Create SQL Performance Analyzer Task page appears.

    2. In the Name field, enter the name of the task.

    3. In the Description field, optionally enter a description of the task.

    4. Under SQL Tuning Set, in the Name field, enter the name of the SQL tuning set that was built.

    5. Click Create.

      The Guided Workflow page appears.

  5. Convert the contents in the SQL tuning set into a pre-upgrade SQL trial that will be used as a baseline for comparison.

    1. On the Guided Workflow page, click the Execute icon for the Replay SQL Tuning Set in Initial Environment step.

      The Create SQL Trial page appears.

    2. In the SQL Trial Name field, enter the name of the SQL trial.

    3. In the SQL Trial Description field, enter a description of the SQL trial.

    4. In the Creation Method list, select Build From SQL Tuning Set.

    5. Ensure that the database environment on the test system matches the production environment as closely as possible, and select Trial environment established.

    6. Under Schedule, select Immediately to start the task now, or Later to schedule the task to start at a time specified using the Date, Time, and Time Zone fields.

    7. Click Submit.

      The Guided Workflow page appears when the execution begins.

      The status icon of this step changes to a clock while the execution is in progress. To refresh the status icon, click Refresh. Depending on the options selected and the size of the SQL workload, the execution may take a long time to complete. After the execution is completed, the Status icon will change to a check mark and the Execute icon for the next step is enabled.

See Also:

Building the Pre-Upgrade SQL Trial for Oracle 10g Release 1 and Older Releases Using APIs

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');

See Also:

Building the Post-Upgrade SQL Trial Upgrading from Oracle Database 10g Release 1 and Older Releases

After the pre-upgrade SQL trial is built, you need to perform a test execute or explain plan of SQL statements in the SQL tuning set on the test system to build a post-upgrade SQL trial. SQL Performance Analyzer remotely test executes the SQL statements using a database link that you need to specify, so that the SQL Performance Analyzer system can connect to the test system and generate the execution plans and statistics for the SQL trial. The database link should exist on the SQL Performance Analyzer system and connect to the test system.

You can build the post-upgrade SQL trial using either Oracle Enterprise Manager or APIs, as described in the following sections:

Building the Post-Upgrade SQL Trial Upgrading from Oracle Database 10g Release 1 and Older Releases Using Enterprise Manager

To build the post-upgrade SQL trial using Enterprise Manager:

  1. On the Guided Workflow page, click the Execute icon for the Replay SQL Tuning Set in Changed Environment step.

    The Create SQL Trial page appears.

  2. In the SQL Trial Name field, enter the name of the SQL trial.

  3. In the SQL Trial Description field, enter a description of the SQL trial.

  4. In the Creation Method list, select Execute SQLs Remotely.

  5. In the Per-SQL Time Limit list, select Customize and specify a reasonable time limit for executing the SQL workload.

  6. 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 new database link using the Create Database Link page.

  7. Ensure that the database environment on the test system matches the production environment as closely as possible, and select Trial environment established.

  8. Under Schedule, select Immediately to start the task now, or Later to schedule the task to start at a time specified using the Date, Time, and Time Zone fields.

  9. Click Submit.

    The Guided Workflow page appears when the execution begins.

    The status icon of this step changes to a clock while the execution is in progress. To refresh the status icon, click Refresh. Depending on the options selected and the size of the SQL workload, the execution may take a long time to complete. After the execution is completed, the Status icon will change to a check mark and the Execute icon for the next step is enabled.

See Also:

Building the Post-Upgrade SQL Trial Upgrading from Oracle Database 10g Release 1 and Older Releases Using APIs

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'));

See Also:

Upgrading from Oracle Database 10g Release 2 and Newer Releases

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 1 or a newer release. The database version should be release 11.1.0.7 or higher. 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.

  1. 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".

  2. Set up the test system so that it matches the production environment as closely as possible, as described in "Setting Up the Test System".

  3. Transport the SQL tuning set to the SQL Performance Analyzer system.

    For information about transporting SQL tuning sets using:

  4. On the SQL Performance Analyzer system, create a new 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 "Building the Pre-Upgrade SQL Trial for Oracle Database 10g Release 2 and Newer Releases".

  5. Upgrade the test system.

  6. 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 "Building the Post-Upgrade SQL Trial Upgrading from Oracle Database 10g Release 2 and Newer Releases".

  7. Compare SQL performance and fix regressed SQL:

    SQL Performance Analyzer compares the performance of SQL statements captured during the pre-upgrade SQL trial to those captured 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. You can then repeat the process of executing the SQL tuning set and comparing its performance to a previous execution to test any fixes or additional changes made. Repeat these steps until you are satisfied with the outcome of the analysis.

Building the Pre-Upgrade SQL Trial for Oracle Database 10g Release 2 and Newer Releases

Once the SQL tuning set is transported to the SQL Performance Analyzer system, you need to perform a test execute or explain plan of SQL statements in the SQL tuning set on the test system to build a pre-upgrade SQL trial. SQL Performance Analyzer remotely test executes the SQL statements using a database link that you need to specify, so that the SQL Performance Analyzer system can connect to the test system to generate the execution plan and statistics for the SQL trial. The database link should exist on the SQL Performance Analyzer system and connect to the test system.

You can build the pre-upgrade SQL trial using Oracle Enterprise Manager or APIs, as described in the following sections:

Building the Pre-Upgrade SQL Trial for Oracle Database 10g Release 2 and Newer Releases Using Enterprise Manager

To build the pre-upgrade SQL trial using Enterprise Manager:

  1. On the Database Home page, click Advisor Central.

    The Advisor Central page appears.

  2. Click SQL Performance Analyzer.

    The SQL Performance Analyzer page appears.

  3. Click Guided Workflow.

    The Guided Workflow page appears.

  4. Create a SQL Performance Analyzer task using a SQL tuning set as its input source:

    1. On the Guided Workflow page, click the Execute icon for the Step 1: Create SQL Performance Analyzer Task based on SQL Tuning Set.

      The Create SQL Performance Analyzer Task page appears.

    2. In the Name field, enter the name of the task.

    3. In the Description field, optionally enter a description of the task.

    4. Under SQL Tuning Set, in the Name field, enter the name of the SQL tuning set that contains the SQL workload to be analyzed

    5. Click Create.

      The Guided Workflow page appears.

  5. Convert the contents in the SQL tuning set into a pre-upgrade SQL trial that will be used as a baseline for comparison.

    1. On the Guided Workflow page, click the Execute icon for the Replay SQL Tuning Set in Initial Environment step.

      The Create SQL Trial page appears.

    2. In the SQL Trial Name field, enter the name of the SQL trial.

    3. In the SQL Trial Description field, enter a description of the SQL trial.

    4. In the Creation Method list, select Execute SQLs Remotely.

    5. In the Per-SQL Time Limit list, select Customize and specify a reasonable time limit for executing the SQL workload.

    6. 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 new database link using the Create Database Link page.

    7. Ensure that the database environment on the test system matches the production environment as closely as possible, and select Trial environment established.

    8. Under Schedule, select Immediately to start the task now, or Later to schedule the task to start at a time specified using the Date, Time, and Time Zone fields.

    9. Click Submit.

      The Guided Workflow page appears when the execution begins.

      The status icon of this step changes to a clock while the execution is in progress. To refresh the status icon, click Refresh. Depending on the options selected and the size of the SQL workload, the execution may take a long time to complete. After the execution is completed, the Status icon will change to a check mark and the Execute icon for the next step is enabled.

  6. Upgrade the test system.

See Also:

Building the Pre-Upgrade SQL Trial for Oracle Database 10g Release 2 and Newer Releases Using APIs

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'));

See Also:

Building the Post-Upgrade SQL Trial Upgrading from Oracle Database 10g Release 2 and Newer Releases

After the pre-upgrade SQL trial is built, you need to upgrade the test system. Once the database has been upgraded, perform a test execute or explain plan of SQL statements in the SQL tuning set on the upgraded test system to build a post-upgrade SQL trial. SQL Performance Analyzer remotely test executes the SQL statements using a database link that you need to specify, so that it can connect to the upgraded test system to generate the execution plan and statistics for the SQL trial. The database link should exist on the SQL Performance Analyzer system and connect to the upgraded test system.

You can build the post-upgrade SQL trial using either Oracle Enterprise Manager or APIs, as described in the following sections:

Building the Post-Upgrade SQL Trial Upgrading from Oracle Database 10g Release 2 and Newer Releases Using Enterprise Manager

To build the post-upgrade SQL trial using Enterprise Manager:

  1. On the Guided Workflow page, click the Execute icon for the Replay SQL Tuning Set in Changed Environment step.

    The Create SQL Trial page appears.

  2. In the SQL Trial Name field, enter the name of the SQL trial.

  3. In the SQL Trial Description field, enter a description of the SQL trial.

  4. In the Creation Method list, select Execute SQLs Remotely.

  5. In the Per-SQL Time Limit list, select Customize and specify a reasonable time limit for executing the SQL workload.

  6. 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 new database link using the Create Database Link page.

  7. Ensure that the database on the test system is upgraded and the environment matches the production environment as closely as possible, and select Trial environment established.

  8. Under Schedule, select Immediately to start the task now, or Later to schedule the task to start at a time specified using the Date, Time, and Time Zone fields.

  9. Click Submit.

    The Guided Workflow page appears when the execution begins.

    The status icon of this step changes to a clock while the execution is in progress. To refresh the status icon, click Refresh. Depending on the options selected and the size of the SQL workload, the execution may take a long time to complete. After the execution is completed, the Status icon will change to a check mark and the Execute icon for the next step is enabled.

See Also:

Building the Post-Upgrade SQL Trial Upgrading from Oracle Database 10g Release 2 and Newer Releases Using APIs

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'));

See Also:

Tuning Regressed SQL Statements After Testing a Database Upgrade

In some cases, SQL Performance Analyzer may identify SQL statements whose performance regressed after you upgrade the database on the test system.

If you are upgrading from Oracle Database 10g and newer releases, you can tune the regressed SQL statements by using the SQL Tuning Advisor or SQL plan baselines, as described in Chapter 11, "Comparing SQL Trials".

If you are upgrading from Oracle 9i Database, you will need to use the DBMS_SQLTUNE package to tune the regressed SQL statements one at a time.

Tip: