How to Tuning SQL Advisor in Oracle SQL Developer

SQL Tuning Advisor in Oracle SQL Developer


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. 

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).
Screenshot for Step

2. 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.
Screenshot for Step

3. Your Oracle SQL Developer opens up.
Screenshot for Step
4. In the Connections navigator, right-click Connections and select New Connection.
Screenshot for Step
5. 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>
Screenshot for Step
6. 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.
Screenshot for Step
7. In the Connections navigator, to create a new connection to the scott schema, right-click Connections and select New Connection.
Screenshot for Step
8. The New / Select Database Connection dialog opens. Enter the connection details as follows and click Test.
Connection Name: scott
Username: scott
Password: <your_password> (Select Save Password)
Hostname: localhost
SID: <your_own_SID>
Screenshot for Step
9. 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.
Screenshot for Step
10 .
The connection is saved and you can view the two newly created connections in the Connections list.
Screenshot for Step

Providing Privileges and Removing the existing Statistics on the Scott User

A user requires certain privileges to run the SQL Tuning Advisor. Also, in order to collect and manage statistics on the SCOTT schema, the existing statistics have to be cleared. Below are the steps to grant SQL Tuning Advisor privileges and remove the existing statistics on the scott user.

1. Click SQL Worksheet and select system user.
Screenshot for Step

2. To grant privileges to the scott user to run the SQL Tuning Advisor, enter the following lines of code. Click Run Statement .
grant advisor to scott;
grant administer sql tuning set to scott;
Screenshot for Step

3. The output for the statements is displayed.
Screenshot for Step
4. 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 SCOTT schema.
To delete the schema statistics, enter the following line of code.
exec DBMS_STATS.DELETE_SCHEMA_STATS ('scott');
Select the statement and click Run Statement . Screenshot for Step
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.
 
5. The output for the statement appears.
Screenshot for Step

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.
1. Open the SQL Worksheet for the scott user by clicking SQL Worksheet .
Screenshot for Step
 
2. Enter the following SQL statement in the worksheet.
select sum(e.sal), avg(e.sal), count(1), e.deptno from dept d, emp e group by e.deptno order by e.deptno;
Screenshot for Step
Note that the above SQL statement has an unused reference to the "dept" table.


3. Select the SQL statement and click SQL Tuning Advisor .
Screenshot for Step

4. The SQL Tuning Advisor output appears.
Screenshot for Step

5. 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.
Screenshot for Step

6. 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.
Screenshot for Step

7. Click the Detail tabbed page to view the SQL Profile Finding.
Screenshot for Step

8. 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.
Screenshot for Step

9. Click the Overview tabbed page. In this case, there are no index recommendations.
Screenshot for Step

10. In the left navigator, click Restructure SQL. In this analysis, relevant suggestions are made to restructure selected SQL statements for improved performance.
Screenshot for Step

Implementing SQL Tuning Advisor recommendations

You can implement the SQL Tuning Advisor recommendation feature. This will enable you to update the statistics in scott schema. Perform the following steps to implement the SQL Tuning Advisor recommendations:
1. In the Connections navigator, right-click scott and select Gather Schema Statistics....
Screenshot for Step
 
2. 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.
Screenshot for Step

3. Click Apply.
Screenshot for Step

4. A confirmation message appears. Click OK.
Screenshot for Step

5. To run the SQL Tuning Advisor on the SQL statement again, select the SQL statement and click SQL Tuning Advisor .
Screenshot for Step

6. The SQL Tuning Advisor output appears. By gathering statistics, the Statistics and SQL Profile advice is now removed.
Screenshot for Step

7. In the left navigator, click each of the SQL Tuning Advisor Implement Type to check if all the recommendations have been implemented.
Screenshot for Step
Note the issues reported to you:
Screenshot for Step
Note the issues reported to you:

Screenshot for Step

Screenshot for Step
Note that the Restructure SQL recommendation to remove an unused table remains.

8. Remove the "dept" table in the SQL statement and click SQL Advisor .
Screenshot for Step

9. The output appears. All of the advice recommendations have been removed.
Screenshot for Step

No comments:

ORA-01552: cannot use system rollback segment for non-system tablespace 'TEMP'

 ORA-01552: cannot use system rollback segment for non-system tablespace "string" Cause: Used the system rollback segment for non...