SQL tuning advisor

Purpose

This tutorial shows you how to use the SQL Tuning Advisor feature in Oracle SQL Developer 3.0.

Complete time

Approximately 20 minutes.

Overview

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

  

Software and Hardware Requirements 

The following is a list of 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.0.  

Prerequisites

Before starting this tutorial, you should: 

1 .	Install Oracle SQL Developer 3.0 from OTN. Follow the readme instructions here.
2 .	Install Oracle Database 11g with the Sample schema.

Creating a Database Connection

The first step to managing database objects using Oracle SQL Developer 3.0 is to create a database connection.

Perform the following steps to create a database connection:

Note: If you already have database connections for SCOTT and SYSTEM, you do not need to perform the following steps. You can move to Providing Privileges to the Scott User topic.

1. If you have installed the SQL Developer icon on your desktop, click the icon to start your SQL Developer and move to Step 4. If you do not have the icon located on your desktop, perform the following steps to create a shortcut to launch SQL Developer 3.0 from your desktop.

Open the directory where the SQL Developer 3.0 is located,right-click             sqldeveloper.exe(on Windows) or sqldeveloper.sh (on Linux) and select Send to > Desktop (create shortcut). 

1. On the desktop, you will find an icon named Shortcut to sqldeveloper.exe. Double-click the icon to open SQL Developer 3.0.

        Note: To rename it, select the icon and then press F2 and enter a new name.