Improving SQL Statement Tuning with Automatic SQL Tuning

Purpose

This tutorial describes how to benefit from Automatic SQL Tuning to automatically tune your high loaded SQL statements.

Time to Complete

Approximately 20 minutes

Topics

This tutorial covers the following topics:

 Overview
 Prerequisites
 Using Automatic SQL Tuning
 Summary

Viewing Screenshots

 Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

Oracle Database 11g further automates the SQL Tuning process by identifying problematic SQL statements, running SQL Tuning Advisor on them, and implementing the resulting SQL profile recommendations to tune the statement without requiring user intervention.

Back to Topic List

Prerequisites

Before you perform this tutorial, you should:

1.

Install Oracle Database 11g.

2.

Download and unzip the ast.zip file into your working directory (that is, wkdir ) and navigate into your working directory.

  Modify the directories in all of the .sh scripts to point to the directory where you placed the files.

Back to Topic List

Using Automatic SQL Tuning

Automatic SQL Tuning uses the AUTOTASK framework through a new task called “Automatic SQL Tuning” that runs every night by default. Here is a brief description of the automated SQL tuning process in Oracle Database 11g:

The following scenario illustrates the above behavior by forcing the Automatic SQL Tuning task to run after you executed some high loaded SQL statements. You force Automatic SQL Tuning execution simply because you do not want to wait for the task to run at night. To force its execution, you simply open the maintenance window at the time you execute this OBE.

Perform the following steps to understand the use of Automatic SQL Tuning:

1.

Connect as user SYS under Enterprise Manager Database Control.

 

2.

Once on the Home page, click the Server tab. On the Server sub-page, click the Automated Maintenance Tasks link.

 

3.

On the Automated Maintenance Tasks page, click the Configure button. On the Automated Tasks Configuration page, click the Configure botton located in front of the Automatic SQL Tuning line.

 

4.

On the Automatic SQL Tuning Settings page, select Yes for Automatic Implementation of SQL Profiles. By default, automatic SQL profile implementation is turned off because it requires licensing with the Tuning pack. Once done, click Apply. Then, click the Automated Maintenance Tasks Configuration breadcrumb followed by the Automated Maintenance Tasks breadcrumb.

 

5.

Open a terminal window and connect as user oracle. Navigate to the directory where you unzipped the ast.zip file. Execute the ast_setup.sh script. This script creates a new schema that is used for this OBE. The script also clears the previous executions of Automatic SQL Tuning.

./ast_setup.sh

 

6.

From the same terminal window, execute the run_workload_stream.sh script. This script executes multiple times a SQL statement that was voluntarily rendered inefficient. Note the time it takes to complete its execution.

./run_workload_stream.sh

 

7.

From the same terminal window, execute the run_ast.sh script. This script forces the maintenance window to open that triggers the Automatic SQL Tuning task to execute and thus automatically tune high loaded SQL statements. This is done to avoid waiting the following night for the maintenance window to open automatically. In real life, you will not have to force the maintenance window to open.

./run_ast.sh

 

8.

From the same terminal window, re-execute the run_workload_stream.sh script. This script executes multiple times a SQL statement that was voluntarily rendered inefficient. Note the time it takes to complete its execution. In the example, it took around 5 seconds to execute the script. This is due to the fact that Automatic SQL Tuning tuned this particular statement, and a profile that gives a much better plan was automatically implemented.

./run_workload_stream.sh

 

9.

From your terminal session, connect as user ast (password ast) under SQL*Plus, and force an AWR snapshot creation. Once done, exit from your SQL*Plus session.

 

10.

Back to the Enterprise Manager Automated Maintenance Tasks page, click the Automatic SQL Tuning link.

 

11.

This takes you to the Automatic SQL Tuning Results Summary page where you can see that only one SQL statement was tuned during the last session, and that two recommendations were spotted: One SQL profile was automatically implemented, and one index creation was only recommended but not implemented. This page also reports the Profile Effect Statistics. It is clear that the recommended profile was very beneficial. On this page, click the View Report button.

 

12.

On the Automatic SQL Tuning Result Details page, you can check all the statements that were considered for automatic tuning. Note that even if automatic SQL profile implementation is not enabled, you still get information about the recommended SQL profile. In this case, the profile was automatically implemented. Select the SQL statement corresponding to the AST schema, and click the View Recommendations button.

 

13.

On the Recommendations for SQL ID page, you can see the complete text for the corresponding SQL statement. Select the recommendation corresponding to the SQL profile, and click the associated Compare Explain Plans icon. You can see the different plans that were generated: before and after SQL profile implementation. Once done, click the SQL ID breadcrumb link.

 

14.

Back to the Recommendations For SQL ID page, click the SQL text corresponding to the tuned statement.

 

15.

On the SQL Details, Tuning History sub-page, you can clearly see that this statement was tuned by the Automatic SQL Tuning task. On the Plan Control sub-page, notice that a SQL profile was automatically implemented.

 

16.

However, you still have the possibility to retrieve the same information using PL/SQL. From your terminal session, execute the get_task_report.sh script. This script shows you the result of the previous analysis using PL/SQL.

./get_task_report.sh

 

Back to Topic Lis t

Summary

In this tutorial, you learned how to use SQL Access Advisor.

Back to Topic List

 Place the cursor over this icon to hide all screenshots.

Left Curve
Popular Downloads
Right Curve
Untitled Document