Oracle by Example branding

Create and Compare SQL Trials Using SQL Performance Analyzer

section 0Before You Begin

This 10-minute tutorial shows you how to create and compare SQL Trials.

Background

Select the Oracle Database release:

A SQL Performance Analyzer (SPA) task is a container that encapsulates all of the data about a complete SPA analysis. A SPA analysis comprises at least two SQL trials and a comparison. A SQL trial encapsulates the execution performance of a SQL tuning set under specific environmental conditions. When you create a SPA task, you select a SQL tuning set as its input source, and when you run SQL trials, the SQL tuning set is used as the source for SQL statements.

After you create a SPA task, you create a pre-change SQL trial. Then you make the database change whose SQL performance impact you want to assess. After making the database change, you create a post-change SQL trial. Finally, you compare the two trials.

A SPA analysis shows the impact of the environmental differences between the two trials.

What Do You Need?

  • Oracle Database 18c19c

section 1Create a Pre-Change SQL Trial

  1. On the Manual Tasks tab of the SQL Performance Analyzer page, click the name of the SPA task that includes the SQL statements for which you want to analyze the impact of a database change.
    Enterprise Manager login page
    Description of the illustration a1
  2. In the SQL Trials section, click Create to create a pre-change SQL trial.
    SQL Trial page
    Description of the illustration a2
  3. On the Trial Information tab of the Create SQL Trial dialog box, enter the values and click OK.
    create SQL Trial page
    Description of the illustration a3
  4. The SQL Trial is successfully created. Click OK.

section 2Create a Post-Change SQL Trial

After making the database change whose SQL performance impact you want to assess, follow these steps to create a post-change SQL trial using SPA:

  1. In the SQL Trials section of the SPA Task page, click Create to create a post-change SQL trial.
  2. On the Trial Information tab of the Create SQL Trial dialog box, enter the values and click OK.
    Create SQL Trial page
    Description of the illustration b2
  3. The SQL Trial is successfully created. Click OK.

section 3Compare Two SQL Trials

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

  1. Click Create in the SQL Trial Comparisons section of the SPA Task page for the task.
  2. On the Compare Information tab of the Create SQL Comparison Report dialog box, enter the values and click OK.
    Create SQL Comparison page
    Description of the illustration c2
  3. The SQL Trial is successfully created. Click OK.
  4. Click the name of the report in the Comparison Report Name column in the SQL Trial Comparisons section to view the comparison report.
    SQL Trial Comparisons page
    Description of the illustration c4
  5. The SQL Performance Analyzer Report page for the task shows the comparison report for the two trials.
    SQL Performance Analyzer report
    Description of the illustration c5

    Note: The output that you receive may vary from what is shown in this tutorial depending on the SQL Tuning Set used.