Creating a Post-Change SQL Trial

5 Creating a Post-Change SQL Trial

After computing the pre-change SQL performance data, you can perform the system change on the test system. Before making the system change, ensure that you have executed the SQL workload in the initial environment to generate the pre-change performance data. For example, if you are testing how changing a database initialization parameter will affect SQL performance, execute the SQL workload once before changing the database initialization parameter to a new value. Depending on the type of change you are making, it may be necessary to reconfigure the environment on the test system to match the new environment for which you want to perform SQL performance analysis.

Note:

You can optionally run SQL trials on a remote system by providing access to a public database link. When conducting remote SQL trials, the database version of the remote database where the SQL statements are executed must be less than or equal to the database version of the database to which it connects. Starting with Oracle Database release 11.2.0.2, the remote database can be a read-only database, such as an Oracle Active Data Guard instance.

"SQL Performance Analyzer" lists examples of possible system changes that can be analyzed using SQL Performance Analyzer. For example, you may want to determine how a database initialization parameter change or database upgrade will affect SQL performance. You may also decide to change the system based on recommendations from an advisor such as Automatic Database Diagnostic Monitor (ADDM), SQL Tuning Advisor, or SQL Access Advisor.

After you have made the system change, you can build the post-change version of performance data by executing the SQL workload again. SQL Performance Analyzer will store the results from executing the SQL statements in a post-change SQL trial.

This section describes how to create the post-change SQL trial and contains the following topics:

Note:

The primary interface for creating a post-change SQL trial is Oracle Enterprise Manager. If for some reason Oracle Enterprise Manager is unavailable, you can create a post-change SQL trial using the DBMS_SQLPA PL/SQL package.

5.1 Creating a Post-Change SQL Trial Using Oracle Enterprise Manager

This section describes how to collect the post-change SQL performance data using Oracle Enterprise Manager.

Before making the system change creating a post-change SQL trial, you need to create a pre-change SQL trial, as described in Creating a Pre-Change SQL Trial.

To create a post-change SQL trial using Enterprise Manager:

On the Guided Workflow page, click the Execute icon for the Create SQL Trial in Changed Environment step.

The Create SQL Trial page appears.
In the SQL Trial Name field, enter the name of the SQL trial.
In the SQL Trial Description field, enter a description of the SQL trial.
In the Creation Method list, determine how the SQL trial is created and what contents are generated by performing one of the following actions:
- Select Execute SQLs Locally.
  
  The SQL trial generates both execution plans and statistics for each SQL statement in the SQL tuning set by actually running the SQL statements locally on the test system.
- Select Execute SQLs Remotely.
  
  The SQL trial generates both execution plans and statistics for each SQL statement in the SQL tuning set by actually running the SQL statements remotely on another test system over a public database link.
- Select Generate Plans Locally.
  
  The SQL trial invokes the optimizer to create execution plans locally on the test system without actually running the SQL statements.
- Select Generate Plans Remotely.
  
  The SQL trial invokes the optimizer to create execution plans remotely on another test system over a public database link without actually running the SQL statements.
For each of these creation methods, the application schema and data should already exist on the local or remote test system.
In the Per-SQL Time Limit list, determine the time limit for SQL execution during the trial by performing one of the following actions:
- Select 5 minutes.
  
  The execution will run each SQL statement in the SQL tuning set up to 5 minutes and gather performance data.
- Select Unlimited.
  
  The execution will run each SQL statement in the SQL tuning set to completion and gather performance data. Collecting execution statistics provides greater accuracy in the performance analysis but takes a longer time. Using this setting is not recommended because the task may be stalled by one SQL statement for a prolonged time period.
- Select Customize and enter the specified number of seconds, minutes, or hours.
Ensure that the system change you are testing has been performed on the test system, and select Trial environment established.
In the Schedule section:
1. In the Time Zone list, select your time zone code.
2. Select Immediately to start the task now, or Later to schedule the task to start at a time specified using the Date and Time fields.
Click Submit.

The Guided Workflow page appears when the execution begins.

The status icon of this step changes to a clock while the execution is in progress. To refresh the status icon, click Refresh. Depending on the options selected and the size of the SQL workload, the execution may take a long time to complete. After the execution is completed, the Status icon will change to a check mark and the Execute icon for the next step is enabled.
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.

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

Before making the system change creating a post-change SQL trial, you need to create a pre-change SQL trial, as described in Creating a Pre-Change SQL Trial.

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.

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.