This section describes how to build the pre-change performance data by using the DBMS_SQLPA package.
To create a pre-change SQL trial:
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 one 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.
Set to CONVERT SQLSET to refer to a SQL tuning set for the execution statistics and plans for the SQL trial. Values for the execution parameters SQLSET_NAME and SQLSET_OWNER should also be specified.
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.
To perform a remote test execute, 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.
To fully execute DML statements—including acquiring row locks and modifying row—set the EXECUTE_FULLDML parameter to TRUE. SQL Performance Analyzer will issue a rollback after executing the DML statements to prevent persistent changes from being made. The default value for this parameter is FALSE, which executes only the query portion of the DML statement without modifying the data.
To restore the relevant captured init.ora settings during a test execute, set the APPLY_CAPTURED_COMPILENV parameter to TRUE. This is not the default behavior because typically you are running SQL trials to test changes when changing the environment. However, this method may be used in cases when the init.ora settings are not being changed (such as creating an index). This method is not supported for remote SQL trials.
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');
Once the pre-change performance data is built, you can make the system change and build the post-change performance data by re-executing the SQL statements in the SQL tuning set on the post-change test system, as described in Creating a Post-Change SQL Trial .
Oracle Database PL/SQL Packages and Types Reference to learn about the DBMS_SQLPA.EXECUTE_ANALYSIS_TASK function