This section describes how to create a SQL Performance Analyzer task by using the DBMS_SQLPA.CREATE_ANALYSIS_TASK function. A task is a database container for SQL Performance Analyzer execution inputs and results.
Before proceeding, capture the SQL workload to be used in the performance analysis into a SQL tuning set on the production system, then transport it to the test system where the performance analysis will be performed, as described in "Capturing the SQL Workload".
To create an analysis task:
Call the CREATE_ANALYSIS_TASK function using the following parameters:
Set task_name to specify an optional name for the SQL Performance Analyzer task.
Set sqlset_name to the name of the SQL tuning set.
Set sqlset_owner to the owner of the SQL tuning set. The default is the current schema owner.
Set basic_filter to the SQL predicate used to filter the SQL from the SQL tuning set.
Set order_by to specify the order in which the SQL statements will be executed.
You can use this parameter to ensure that the more important SQL statements will be processed and not skipped if the time limit is reached.
Set top_sql to consider only the top number of SQL statements after filtering and ranking.
The following example illustrates a function call:
VARIABLE t_name VARCHAR2(100);
EXEC :t_name := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => 'my_sts', -
task_name => 'my_spa_task');
Once the analysis task is created, you can build the pre-change performance data by executing the SQL statements stored in the SQL tuning set, as described in Creating a Pre-Change SQL Trial .
Oracle Database PL/SQL Packages and Types Reference to learn more about the DBMS_SQLPA.CREATE_ANALYSIS_TASK function