Testing Database Upgrades from Releases 9.x and 10.1 Using APIs

This section describes how to test database upgrades from Oracle Database releases 9.x and 10.1 using APIs.

To test a database upgrade from releases 9.x and 10.1:

  1. On the system running SQL Performance Analyzer, create an analysis task.

  2. Build the pre-upgrade SQL trial from the execution plans and run-time statistics in the SQL tuning set by calling 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');

  3. Build the post-upgrade SQL trial by performing an explain plan or test execute using the EXECUTE_ANALYSIS_TASK procedure:

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

    • 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 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: