A SQL tuning set (STS) is a database object that includes one or more SQL statements along with their execution statistics and execution context.

You can use APIs to create a SQL tuning set, load SQL statements into the SQL tuning set, and transport the SQL tuning set to another system (such as a test system that is very similar to your production system).

Before you use SQL Performance Analyzer or SQL Performance Analyzer Quick Check (SPA Quick Check) in EM Express, you must specify default settings for them. These tools require a SQL tuning set that includes the SQL statement or statements whose performance you want to analyze.

You must specify default settings for SPA Quick Check before using it.

1. On any page in EM Express, from the Performance menu, select SQL Performance Analyzer.

   The SQL Performance Analyzer page appears.

2. On the Quick Check Tasks tab, click Quick Check Default Setup.

   The SPA Quick Check Default Values setup dialog box appears.

3. In the SPA Quick Check Default Values Setup dialog box, you specify:

   • A SQL tuning set

   • Default values for SPA Quick Check to use when executing the statements in the SQL tuning set

   • Default values for SPA Quck Check to use when it compares the performance of the statements in the SQL tuning set with the database's current settings and after the planned change to your database

   In the SPA Quick Check Default Values Setup dialog box, specify values for these fields:

   • SQL Tuning Set: Specify a SQL tuning set that includes the SQL statements whose performance you are interested in analyzing.

   • Disable Multiple Executions: Indicates whether the statements in the SQL tuning set should be run multiple times when SPA Quick Check executes the SQL tuning set. When Yes is selected, each SQL in the SQL tuning set is executed only once. When No is selected, SQL statements in the SQL tuning set are executed multiple times and runtime statistics are then averaged.

   • Rows to Fetch: Indicates the number of result rows to be fetched for each SQL during a SQL Performance Analyzer test execution. Permissible values are ALL_ROWS, AVERAGE, AUTO, or an integer.

     Value	Description
     ALL_ROWS	All result rows will be fetched. This is the default value.
     AVERAGE	The number of result rows will be calculated as the ratio of total rows processed and total executions for each SQL in the SQL tuning set.
     AUTO	The number of result rows will be determined using the value of the optimizer_mode parameter of the optimizer environment captured in the SQL tuning set. If value of optimizer_mode was ALL_ROWS, then all result rows will be fetched. If its value was FIRST_ROWS_n, then n result rows will be fetched by SPA Quick Check.
     Integer	The number of result rows will be equal to this specified value, or less if there were fewer rows to fetch.

   • Per-SQL Time Limit (seconds): Indicates the per-statement timeout (in seconds) for each statement when SPA Quick Check executes the statements in a SQL tuning set. The maximum value is 2^32-1. Unlimited if set to empty.

   • Total Time Limit (seconds): Indicates the global timeout (in seconds) when SPA Quick executes all the statements in a SQL tuning set. Maximum value is 2^32-1. Unlimited if set to empty.

   • Comparison Metric: Choose the metric you want SPA Quick Check to use when it compares the performance of the statements in the SQL tuning set with the database's current settings and after the planned change to your database.

   • Workload Impact Threshold (%): Indicates the threshold of a SQL statement change impact on a workload. Statements having workload change impact below the absolute value of this threshold will be considered as unchanged (that is, the performance of those statements will be considered neither improved nor regressed).

   • SQL Impact Threshold (%): Threshold of a change impact on a SQL statement. Statements having SQL change impact below the absolute value of this threshold will be considered as unchanged (that is, the performance of those statements will be considered neither improved nor regressed).

You can use SPA Quick Check to validate the impact of a change to a session-modifiable database initialization parameter.

1. On any page in EM Express, from the Configuration menu, select Initialization Parameters.

   The Initialization Parameters page appears.

2. Select a session-modifiable initialization parameter for which you would like to validate the impact of setting a new value. Session-modifiable parameters on the Initialization Parameters page have a check mark in the Session column. You can use the Name filter to reduce the number of initialization parameters displayed.

   Note that most of the parameters in the Optimizer category are session-modifiable.

3. Click Validate with SPA.

   The Validate with SPA dialog box appears, with the initialization parameter you selected in the Name field.

4. In the Validate with SPA dialog box, specify values for the following options and click OK:

   Option	Description
   Parameter Value	Enter the new initialization parameter value whose impact you would like to validate on the database workload.
   Task Name	Accept the default task name, or enter a different name.
   Description	This field shows the initialization parameter name, the current value, and the new value whose impact you want to validate. Accept the default description, or enter a different description.
   Total Time Limit	Accept the default value, or specify a different maximum length of time (in seconds) that SPA Quick Check can take to validate the impact of the new value.

   The Quick Check Tasks tab of the SQL Performance Analyzer page appears, and the Status column for the new task shows SPA Quick Check's progress processing the task. A check mark appears in the Status column when the task execution is complete.

5. To view a comparison report, click the name of the report in the Comparison Report Name column.

   The SQL Performance Analyzer Report page for the task appears. The SQL Trials section lists the SQL trials that SPA Quick Check performed for the task, and the SQL Trials Comparisons section lists one or more comparisons performed for those trials. The Top SQL Statements section shows a comparison of the top SQL statements for the two trials.

   The Status column shows whether performance improved, regressed, or was unchanged for each of the statements.

   Use the Category filter to show all the top SQL statements, or to show only the SQL statements in one of the available categories.

   Any SQL statement for which a new execution plan is recommended includes a check mark in the New Plan column.

6. In the Top SQL Statements section at the bottom of the page, click the SQL ID for a SQL statement to see more detailed information about the statement execution in the two trials.
   The Summary section, and the Statistics, Plans, and Findings tabs in the Execution Details section provide more information about the SQL statement execution for the two trials.

You can use SPA Quick Check to validate the impact of implementing a SQL profile that SQL Tuning Advisor has recommended.

1. In the Select Recommendation section on Tuning Result page for the SQL statement, select the SQL Profile recommendation and click Validate with SPA.
   
   
   Description of the illustration validate_prof.gif
   

   The Validate with SPA dialog box appears.

2. In the Validate with SPA dialog box, specify values for the following options and click OK:

   Option	Description
   Task Name	Accept the default task name, or enter a different name.
   Description	This field shows the initialization parameter name, the current value, and the new value whose impact you want to validate. Accept the default description, or enter a different description.
   Total Time Limit	Accept the default value, or specify a different maximum length of time (in seconds) that SPA Quick Check can take to validate the impact of the new value.

   The Quick Check Tasks tab of the SQL Performance Analyzer page appears, and the Status column for the new task shows SPA Quick Check's progress processing the task. A check mark appears in the Status column when the task execution is complete.

3. After the task execution is complete, click the task name.

   The SQL Performance Analyzer Task page for the task appears. The SQL Trials section lists the SQL trials that SPA Quick Check performed for the task, and the SQL Trials Comparisons section lists one or more comparisons performed for those trials.

4. To view a comparison report, click the name of the report in the Comparison Report Name column.

   The SQL Performance Analyzer Report page for the task appears. The SQL Trials section lists the SQL trials that SPA Quick Check performed for the task, and the SQL Trials Comparisons section lists one or more comparisons performed for those trials. The Top SQL Statements section shows a comparison of the top SQL statements for the two trials.

   The Status column shows whether performance improved, regressed, or was unchanged for each of the statements.

   Use the Category filter to show all the top SQL statements, or to show only the SQL statements in one of the available categories.

   Any SQL statement for which a new execution plan is recommended includes a check mark in the New Plan column.

5. In the Top SQL Statements section at the bottom of the page, click the SQL ID for a SQL statement to see more detailed information about the statement execution in the two trials.
   The Summary section, and the Statistics, Plans, and Findings tabs in the Execution Details section provide more information about the SQL statement execution for the two trials.

Before you implement multiple SQL profiles that have been recommended by SQL Tuning Advisor, you can use SPA Quick Check to validate the impact of implementing those SQL profiles on your workload.

Multiple SQL profile recommendations can appear on these EM Express pages:

• If Automatic SQL Tuning Advisor is enabled, the Automatic tab on the SQL Tuning Advisor page can include multiple SQL profile recommendations for statements listed in the Top SQL Statements section at the bottom of the tab.

• On the Tuning Task Result page for a SQL tuning task. A SQL tuning task can include multiple SQL statements for which SQL Tuning Advisor recommends implementing SQL profiles. The Manual tab on the SQL Tuning Advisor page lists SQL tuning tasks, and you can select a task and click View Result to see all the recommendations for that task.

If SQL Performance Analyzer has recommended implementing multiple SQL profiles on an EM Express page, the Top SQL Statements section of the page includes the Validate All Profiles with SPA button.

You can use SPA Quick Check to validate the impact of implementing multiple SQL profiles that SQL Tuning Advisor has recommended.

1. In the Top SQL Statements of an EM Express page that lists one or more SQL statements for which SQL Tuning Advisor has recommended implementing SQL profiles, click the Validate All Profiles with SPA button. The Validate All Profiles with SPA button is not available if SQL Tuning Advisor has not recommended implementing multiple SQL profiles.
   
   
   Description of the illustration validate_all_profs.gif
   

   The Validate with SPA dialog box appears.

2. In the Validate with SPA dialog box, specify values for the following options and click OK:

   Option	Description
   Task Name	Accept the default task name, or enter a different name.
   Description	This field shows the initialization parameter name, the current value, and the new value whose impact you want to validate. Accept the default description, or enter a different description.
   Total Time Limit	Accept the default value, or specify a different maximum length of time (in seconds) that SPA Quick Check can take to validate the impact of the new value.

   The Quick Check Tasks tab of the SQL Performance Analyzer page appears, and the Status column for the new task shows SPA Quick Check's progress processing the task. A check mark appears in the Status column when the task execution is complete.

3. After the task execution is complete, click the task name.

   The SQL Performance Analyzer Task page for the task appears. The SQL Trials section lists the SQL trials that SPA Quick Check performed for the task, and the SQL Trials Comparisons section lists one or more comparisons performed for those trials.

4. To view a comparison report, click the name of the report in the Comparison Report Name column.

   The SQL Performance Analyzer Report page for the task appears. The SQL Trials section lists the SQL trials that SPA Quick Check performed for the task, and the SQL Trials Comparisons section lists one or more comparisons performed for those trials. The Top SQL Statements section shows a comparison of the top SQL statements for the two trials.

   The Status column shows whether performance improved, regressed, or was unchanged for each of the statements.

   Use the Category filter to show all the top SQL statements, or to show only the SQL statements in one of the available categories.

   Any SQL statement for which a new execution plan is recommended includes a check mark in the New Plan column.

5. In the Top SQL Statements section, click the SQL ID for a SQL statement to see more detailed information about the statement execution in the two trials.
   The Summary section, and the Statistics, Plans, and Findings tabs in the Execution Details section provide more information about the SQL statement execution for the two trials.

You create a SQL Performance Analyzer task using a SQL tuning set as its input source.

1. On any EM Express page, choose SQL Performance Analyzer from the Performance menu.

   The SQL Performance Analyzer page appears.

2. On the Manual Tasks tab, click Create.

   The Create SQL Performance Analyzer Task dialog box appears.

3. In the Create SQL Performance Analyzer Task dialog box, enter values for these fields and then click OK:

   • Task Name: Accept the default task name, or enter the name you want to use for the task.

   • Description: Optionally, enter a description for the task.

   • SQL Tuning Set: Choose the SQL tuning set you want to use to create the task.

   The new SQL Performance Analyzer task appears in the list of SQL Performance Analyzer tasks on the Manual Tasks tab.

After you create a SQL Performance Analyzer task, you can use SQL Performance Analyzer to create a pre-change SQL trial.

1. On any EM Express page, choose SQL Performance Analyzer from the Performance menu.

   The SQL Performance Analyzer page appears.

2. On the Manual Tasks tab, click the name of the SQL Performance Analyzer task that includes the SQL statements for which you want to analyze the impact of a database change.

   The SQL Performance Analyzer Task page for that task appears.

3. In the SQL Trials section, click Create to create a pre-change SQL trial.
4. On the Trial Information tab of the Create SQL Trial dialog box:

   • Trial Name: Accept the default trial name, or enter the name you want to use for the trial.

   • Description: Optionally, enter a description for the trial.

   • Execution Type: Choose the type of execution you want to use for the trial:

     • Test Execute: This method test executes SQL statements through SQL Performance Analyzer on the database running SQL Performance Analyzer.

     • Explain Plan: This method generates execution plans only for SQL statements through SQL Performance Analyzer on the database running SQL Performance Analyzer. Unlike the EXPLAIN PLAN statement, SQL trials using the explain plan method take bind values into account and generate the actual execution plan.

     • Convert SQL Tuning Set: This method converts the execution statistics and plans stored in a SQL tuning set to a trial. You can then compare this trial with other trials. A tuning set does not contain all the data that is collected from a test execute trial, so if you create a comparison of a convert SQL tuning set trial with a test execute trial, there will be fewer comparison metrics available.

   Click the right arrow button to go to the Schedule tab if you want to schedule a time for the trial to run. Or, click OK to run the trial immediately.

   When the trial is executing, its status is displayed in the Status column in the SQL Trials section of the SQL Performance Analyzer Task page for the task.

   When the trial is complete, a check mark icon appears in the Status column for the trial in the SQL Trials section of the SQL Performance Analyzer Task page for the task.

After you create a pre-change SQL trial and make a database change, you can create a post-change SQL trial.

1. On any EM Express page, choose SQL Performance Analyzer from the Performance menu.

   The SQL Performance Analyzer page appears.

2. On the Manual Tasks tab, click the name of the SQL Performance Analyzer task that includes the SQL statements for which you want to analyze the impact of a database change.

   The SQL Performance Analyzer Task page for that task appears.

3. In the SQL Trials section, click Create to create a post-change SQL trial.
4. On the Trial Information tab of the Create SQL Trial dialog box:

   • Trial Name: Accept the default trial name, or enter the name you want to use for the trial.

   • Description: Optionally, enter a description for the trial.

   • Execution Type: Choose the type of execution you want to use for the trial:

     • Test Execute: This method test executes SQL statements through SQL Performance Analyzer. This can be done on the database running SPA Performance Analyzer or on a remote database.

     • Explain Plan: This method generates execution plans only for SQL statements through SQL Performance Analyzer. This can be done on the database running SPA Performance or on a remote database. Unlike the EXPLAIN PLAN statement, SQL trials using the explain plan method take bind values into account and generate the actual execution plan.

     • Convert SQL Tuning Set: This method converts the execution statistics and plans stored in a SQL tuning set to a trial. You can then compare this trial with other trials. A tuning set does not contain all the data that is collected from a test execute trial, so if you create a comparison of a convert SQL tuning set trial with a test execute trial, there will be fewer comparison metrics available.

   Click the right arrow button to go to the Schedule tab if you want to schedule a time for the trial to run. Or, click OK to run the trial immediately.

   When the trial is executing, its status is displayed in the Status column in the SQL Trials section of the SQL Performance Analyzer Task page for the task.

   When the trial is complete, a check mark icon appears in the Status column for the trial in the SQL Trials section of the SQL Performance Analyzer Task page for the task.

After a pre-change SQL trial and a post-change SQL trial have been created, you can use SQL Performance Analyzer to compare the two SQL trials to assess the impact of the database change on SQL performance.

1. On any EM Express page, choose SQL Performance Analyzer from the Performance menu.

   The SQL Performance Analyzer page appears.

2. On the Manual Tasks tab, click the name of the SQL Performance Analyzer task that includes the SQL statements for which you want to analyze the impact of a database change.

   The SQL Performance Analyzer Task page for that task appears.

3. To compare two SQL trials, click Create in the SQL Trial Comparisons section of the SQL Performance Analyzer Task page for the task.
4. On the Compare Information tab of the Create SQL Comparison Report dialog box:

   • Comparison Report Name: Accept the default comparison report name, or enter the name you want to use for the report.

   • Description: Optionally, enter a description for the comparison.

   • Trial 1 Name: Select the first trial to use in the comparison. Although you can select any trial, typically you will choose a pre-change SQL trial as the first trial.

   • Trial 2 Name: Select the second trial to use in the comparison. Although you can select any trial, typically you will choose a post-change SQL trial as the second trial.

   • Comparison Metric: Select the metric to use for the comparison.

   Click the right arrow button to go to the Schedule tab if you want to schedule a time for the comparison to run. Or, click OK to run the comparison immediately.

   When the comparison is being performed, its status is displayed in the Status column in the SQL Trial Comparisons section of the SQL Performance Analyzer Task page for the task.

5. To view the new comparison report, click the name of the report in the Comparison Report Name column in the SQL Trial Comparisons section.