Creating a Post-Change SQL Trial Using APIs

This section describes how to collect the post-change SQL performance data using the DBMS_SQLPA package.

Note:

If you are running the SQL statements remotely on another test system over a database link, the remote user calling this procedure needs to have the EXECUTE privilege for the DBMS_SQLPA package.

To create a post-change SQL trial:

  • Call the EXECUTE_ANALYSIS_TASK procedure using the parameters described in "Creating a Pre-Change SQL Trial Using APIs".

    Be sure to specify a different value for the execution_name parameter. It is also highly recommended that you create the post-change SQL trial using the same method as the pre-change SQL trial by using the same value for the execution_type parameter.

    Note:

    If you want to run an Oracle Exadata simulation, you should first set the CELL_SIMULATION_ENABLED task parameter to TRUE. For more information, see "Configuring an Analysis Task for Exadata Simulation Using APIs".

    The following example illustrates a function call made after a system change:

    EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'my_spa_task', -
           execution_type => 'TEST EXECUTE', - 
           execution_name => 'my_exec_AFTER_change');
    
    

Once the post-change performance data is built, you can compare the pre-change SQL trial to the post-change SQL trial by running a comparison analysis, as described in Comparing SQL Trials .

See Also: