This tutorial shows you how to use the SQL Tuning Advisor feature in Oracle SQL Developer 3.2.
Time to Complete
Approximately 40 minutes
The SQL Tuning Advisor analyzes high-volume SQL statements and offers tuning recommendations. It takes one or more SQL statements as an input and invokes the Automatic Tuning Optimizer to perform SQL tuning on the statements. It can run against any given SQL statement. The SQL Tuning Advisor provides advice in the form of precise SQL actions for tuning the SQL statements along with their expected performance benefits. The recommendation or advice provided relates to the collection of statistics on objects, creation of new indexes, restructuring of the SQL statement, or creation of a SQL profile. You can choose to accept the recommendation to complete the tuning of the SQL statements. Oracle Database can automatically tune SQL statements by identifying problematic SQL statements and implementing tuning recommendations using the SQL Tuning Advisor. You can also run the SQL Tuning Advisor selectively on a single or a set of SQL statements that have been identified as problematic.
In this tutorial, you learn how to run and review the recommendations of the SQL Tuning Advisor.
Note: Tuning Advisor is part of the Tuning Pack, one of the Oracle management packs and is available for purchase with Enterprise Edition. For more information see The Oracle Technology Network or the online documentation.
Hardware and Software Requirements
The following is a list of hardware and software requirements:
Oracle Database 11g Enterprise Edition with access to the Tuning and Diagnostic management packs and with the sample schema installed.
Oracle SQL Developer 3.2.
Download Oracle SQL Developer 3.2 here.
Note: For best results, use Firefox or Chrome browsers to view this tutorial.
The first step to managing database objects using Oracle SQL Developer 3.2 is to create a database connection. Perform the following steps to create a database connection:
Note: If you already have database connections for HR and SYSTEM, you do not need to perform the following steps. You can move to Providing Privileges to the Hr User topic.
A user requires certain privileges to run the SQL Tuning Advisor. Also, in order to collect and manage statistics on the HR schema, the existing statistics need to be cleared. Below are the steps to grant SQL Tuning Advisor privileges and to remove the existing statistics on the hr user.
In this topic, you run the SQL Tuning Advisor on a SQL statement. Four types of analysis are performed by the SQL Tuning Advisor on the SQL statement. All the recommendations are displayed in the Overview. You can also view each recommendation individually.
You can implement the SQL Tuning Advisor recommendation feature. This will enable you to update the statistics in hr schema. Perform the following steps to implement the SQL Tuning Advisor recommendations:
In this tutorial, you have learned how to:
Create a Database Connection
Provide Privileges to the HR User
Run the SQL Tuning Advisor on a SQL statement
Implement SQL Tuning Advisor Recommendations
Lead Curriculum Developer: Dimpi Sarmah, Sharon Stephen
Other Contributors: Ashley Chen, Swarnapriya Shridhar, Nancy Greenberg
To help navigate this Oracle by Example, note the following:
Hiding Header Buttons:
Click the Title to hide the buttons in the header. To show the buttons again, simply click the Title again.
Topic List Button:
A list of all the topics. Click one of the topics to navigate to that section.
Expand/Collapse All Topics:
To show/hide all the detail for all the sections. By default, all topics are collapsed
Show/Hide All Images:
To show/hide all the screenshots. By default, all images are displayed.
To print the content. The content currently displayed or hidden will be printed.
To navigate to a particular section in this tutorial, select the topic from the list.