This section describes how to test database upgrades from Oracle Database releases 10.2 and higher using APIs.
To test a database upgrade from releases 10.2 and higher:
On the system running SQL Performance Analyzer, create an analysis task.
Build the pre-upgrade SQL trial by performing an explain plan or test execute of SQL statements in the SQL tuning set.
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'));
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_12c', -
execution_params => dbms_advisor.arglist('database_link',
'LINK.A.B.C.BIZ.COM'));
Oracle Database PL/SQL Packages and Types Reference to learn about the DBMS_SQLPA.EXECUTE_ANALYSIS_TASK function
Oracle Database PL/SQL Packages and Types Reference to learn about the DBMS_SQLPA.EXECUTE_ANALYSIS_TASK function