Validating the Impact of Implementing Key SQL Profiles

Before you implement key SQL profiles for SQL statements, you can validate the impact of using those profiles by using SPA Quick Check. You can validate the impact of key SQL profiles on the Automatic SQL Tuning Result Summary page. Key SQL profiles are profiles verified to yield at least a 3 times performance improvement, and which would have been implemented automatically if auto-implementation had been enabled for Automatic SQL Tuning Advisor.

Note:

You can use SPA Quick Check to validate the impact of implementing key SQL profiles in databases running Oracle Database 11g Release 1 (11.1) and later.

To validate the impact of key SQL profiles:

  1. On the Database Home page in Cloud Control, from the Performance menu, select Advisors Home.The Advisor Central page appears.

  2. In the Advisors section, click SQL Advisors.The SQL Advisors page appears.

  3. In the SQL Tuning Advisor section, click Automatic SQL Tuning Results.The Automatic SQL Tuning Result Summary page appears.

  4. The Key SQL Profiles field in the Task Status section lists the number of key SQL profiles for the current automatic SQL tuning task. If a value of 0 appears in the field, there are no key SQL profiles to use (or validate). If a value greater than 0 appears in the Key SQL Profiles field, click the value to validate the impact of using the key SQL profile or profiles.The Automatic SQL Tuning Result Details: SQLs with Key SQL Profile page appears.

  5. The key SQL profiles appear in the Recommendations section. Click Validate All Profiles with SPA.
    Description of the illustration sqc_key_sql_profiles.gif follows
    Description of the illustration sqc_key_sql_profiles.gif
    A Confirmation statement appears at the top of the page that indicates that a SPA task for validating the SQL profiles has been submitted.

  6. Click the link for the SPA task in the Confirmation statement.The SQL Performance Analyzer Home page appears, and the SPA task for validating the key SQL profiles appears in the SQL Performance Analyzer Tasks table at the bottom of the page.

  7. Select the task and click View Latest Report.The SQL Performance Analyzer Task Report page appears.

  8. View the table at the bottom of the page to see what the result would be of implementing the key SQL profiles recommended on the Automatic SQL Tuning Result Summary page on the most impactful SQL statements in the workload.