SQL Tuning Advisor in Oracle SQL Developer 3.2
Overview
- 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.
Purpose
This tutorial shows you how to use the SQL Tuning Advisor feature in Oracle SQL Developer 3.2.
Time to Complete
Approximately 40 minutes
Introduction
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:
Prerequisites
Note: For best results, use Firefox or Chrome browsers to view this tutorial.
Creating a Database Connection
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.
Open the directory where the SQL Developer 3.2 is located, right-click sqldeveloper.exe (on Windows) or sqldeveloper.sh (on Linux) and select Send to > Desktop (create shortcut).
On the desktop, you will find an icon named Shortcut to
sqldeveloper.exe. Double-click the icon to open SQL
Developer 3.1.
Note: To rename it, select the icon and then press F2 and
enter a new name.
Your Oracle SQL Developer opens up.
In the Connections navigator, right-click Connections and select New Connection.
The New / Select Database Connection dialog opens. Enter the connection details as follows and click Test.
Connection Name: system
Username: system
Password: <your_password
>(Select Save Password)
Hostname: localhost
SID: <your_own_SID>
Check for the status of the connection on the left-bottom side(above the Help button). It should read Success. Click Save and then click Connect.
In the Connections navigator, to create a new connection to the hr schema, right-click Connections and select New Connection.
The New / Select Database Connection dialog opens. Enter the connection details as follows and click Test.
Connection Name: hr_conn
Username: hr
Password: <your_password
>(Select Save Password)
Hostname: localhost
SID: <your_own_SID>
Check for the status of the connection on the left-bottom side(above the Help button). It should read Success. Click Save and then click Connect.
Providing Privileges and Removing the Existing Statistics on the HR User
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.
Click SQL Worksheet and select system user.
To grant privileges to the hr user to run the SQL Tuning
Advisor, enter the following lines of code. Click Run
Script.
grant advisor to hr;
grant administer sql tuning set to hr;
The output for the statements is displayed.
The Oracle
database allows you to collect statistics of many different
kinds in order to improve performance. To illustrate some of
the features the SQL Tuning Advisor offers, clear the
existing statistics from the HR schema.
To delete the
schema statistics, enter the following line of code.
exec
DBMS_STATS.DELETE_SCHEMA_STATS ('hr');
Select the
statement and click Run Statement
With the DBMS_STATS package you can view and modify optimizer statistics gathered for database objects.The DELETE_SCHEMA_STATS procedure deletes statistics for an entire schema.
The outputs for the statements are displayed.
Running the SQL Tuning Advisor on a SQL statement
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.
Open the SQL Worksheet for the hr user by clicking SQL Worksheet.
Enter the following SQL statement in the worksheet.
select sum(e.salary),
avg(e.salary), count(1), e.department_id from
departments d, employees e group by e.department_id
order by e.department_id;
Select the SQL statement and click SQL Tuning Advisor .
The SQL Tuning Advisor output appears.
In the left navigator, click Statistics. In this analysis, objects with stale or missing statistics are identified and appropriate recommendations are made to remedy the problem.
In the left navigator, click SQL Profile. Here, the SQL Tuning Advisor recommends to improve the execution plan by the generation of a SQL Profile.
Click the Detail tabbed page to view the SQL Profile Finding.
In the left navigator, click Indexes. This recommends whether the SQL statement might benefit from an index. If necessary, new indexes that can significantly enhance query performances are identified and recommended.
Click the Overview tabbed page. In this case, there are no index recommendations.
In the left navigator, click Restructure SQL. In this analysis, relevant suggestions are made the restructure selected SQL statements for improved performance.
Implementing SQL Tuning Advisor Recommendations
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 the Connections navigator, right-click hr and select Gather Schema Statistics....
In Gather Schema Statistics, select Estimate Percent as 100 from the drop-down list so that all rows in each table are read. This ensures that the statistics are as accurate as possible.
Click Apply.
A confirmation message appears. Click OK.
To run the SQL Tuning Advisor on the SQL statement again, select the SQL statement and click SQL Tuning Advisor.
The SQL Tuning Advisor output appears. By gathering statistics, the Statistics and SQL Profile advice is now removed.
In the left
navigator, click each of the SQL Tuning Advisor Implement
Type to check if all the recommendations have been
implemented.
Note the issues reported to you:
Note the issues reported to you:
Note that the Restructure SQL recommendation to remove an unused table remains.
Remove the
"departments" table in the SQL statement and click SQL
Advisor.
select
sum(e.salary), avg(e.salary), count(1), e.department_id
from employees e
group by e.department_id order by e.department_id;
The output appears. All of the advice recommendations have been removed.
Summary
- 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
In this tutorial, you have learned how to:
Credits
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.
- Print:
- 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.