Proactively Tuning Your Database Using the SQL Tuning Advisor

Purpose

This tutorial describes how to use Enterprise Manager and the SQL Tuning Advisor to proactively tune your database.

Time to Complete

Approximately 30 minutes

Topics

This tutorial covers the following:

	Overview
	Prerequisites
	Creating a Tuning Scenario
	Using the SQL Tuning Advisor
	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

New SQL Tuning and Diagnostics Features of Enterprise Manager

The Automatic Workload Repository collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. The Automatic Database Diagnostic Monitor (ADDM) reduces the amount of effort required to diagnosis and tune Oracle systems. The SQL Tuning Advisor feature allows a quick and efficient technique for optimizing SQL statements.

Performance diagnostic information can be viewed in Oracle Enterprise Manager screens after diagnostic monitoring has completed on the data. When performance problems are encountered, you can launch Oracle advisors to further define and correct the problems. For example, ADDM can identify high load SQL statements. Then you can tune these statements with the SQL Tuning Advisor.

Proactive Versus Reactive Database Tuning

With the new integrated and automatic tuning features of Oracle Database 10g, you can use Enterprise Manager to detect problems as they occur and devise solutions for the tuning problems. As the DBA, you would simply implement the recommendations. This is referred to as Proactive Tuning.

You can also use the same tools to perform your own analysis of database performance. You can create SQL Tuning Tasks to resolve problems in the method best suited for your business. This is referred to as Reactive Tuning.

Back to Topic List

Prerequisites

Before starting this tutorial, you should:

1.	Perform the Installing Oracle Database 10g on Windows tutorial.
2.	Download and unzip the sqltune.zip file into your working directory (c:\wkdir).

Back to Topic List

Creating a Tuning Scenario

Perform the following steps to start a workload on your database and execute SQL statements that need tuning:

1.	Open a browser and launch Enterprise Manager: http://<your host name>:1158/em Enter sys as the username, oracle as the password , and connect as SYSDBA. Click Login.
2.	Click the Performance tab.
3.	On the Performance page, ensure that the View Data field is set to Real Time: Manual Refresh.
4.	Invoke SQLPlus and connect to the database as SYSDBA. Execute the setup_dina.sql* script to configure a short flushing interval, change snapshot settings, adjust ADDM timing, and grant privileges. Execute the script as follows: @c:\wkdir\setup_dina.sql
5.	Make note of the current time.
6.	Invoke a second SQLPlus session and connect as the SH user with a password of SH. Execute the dina.sql* script to invoke a looping SQL script that queries for a customer with the first name of "Dina." Execute the script as follows: @c:\wkdir\dina.sql
7.	Return to Enterprise Manager and observe the Performance page for approximately five minutes. You can set the View Data field to Real Time: 1 Minute Refresh during your observation period. Re-set the View Data field to Real Time: Manual following your observation.
8.	After observing the performance for approximately five minutes, scroll to the bottom of the Performance Page and select Top Activity.
9.	Select the Session ID corresponding to your SQL*Plus session connected as the SH user.
10.	Click Kill Session to terminate the SH session.
11.	Click Yes to confirm the termination of the SH session.
12.	Click Database Instance to return to the Performance page.
13.	Again, make note of the time.
14.	Return to your Database Home page. You will now determine the problem. If the time corresponding to the problematic time period corresponds with the latest ADDM run detected by Database Control, you should find the link corresponding to the correct performance analysis directly in the Diagnostic Summary section of the Database Control home page. If there is no link in ADDM Findings, access the Advisor Central page and search for the ADDM task as outlined in the next section, Using the SQL Tuning Advisor.

Back to Topic List

Using the SQL Tuning Advisor

Perform the following steps to invoke the SQL Tuning Advisor:

1.	Click the Advisor Central link in the Related Links section of your Database Home page.
2.	Select ADDM in the Advisory Type drop-down menu. Select Last 24 Hours in the Advisor Runs drop-down menu. Click Go.
3.	Select the ADDM task that executed within the time period that your workload was executing. You can check the time values you recorded in steps 5 and 13 of the previous section. Click View Result.
4.	On the ADDM page you see the results in the Performance Analysis section. Click the finding with the highest impact on the database time. It should correspond to a SQL Tuning recommendation.
5.	On the Performance Finding Details page you see the high-load SQL statement captured by the ADDM analysis. The information provided indicates that there will be a significant benefit if you tune this statement. Click Run Advisor Now for the highest high-load SQL statement detected.
6.	The SQL Tuning task is created.
7.	Recommendations are displayed. In this case, the recommendation is to create a SQL Profile in order to get a better execution plan. Click the eyeglass icon to review detailed information about the new explain plan.
8.	You can compare the original explain plan and the new explain plan on this page. Click the Recommendations link.
9.	Click Implement to create the SQL Profile.
10.	The SQL Profile is created. Click the SQL ID link to access the SQL Details page.
11.	On the SQL Details Statistics page, you can view real-time or historical execution activity and statistics. Click the Activity link.
12.	On the SQL Details Activity page, you can view real-time or historical active sessions and details. Click the Plan tab.
13.	The SQL Details Plan page provides detailed information on the execution plan. The execution plan information may no longer be available depending on the amount of time that has transpired. Click the Tuning Information tab.
14.	The SQL Details Tuning Information page provides information on SQL profiles and outlines. In addition, it provides tuning task historical information. Return to the Performance page by clicking the Database Instance link.
15.	Return to your SQLPlus session where you are connected as the SYS user. Execute the cleanup_dina.sql* script to change the snapshot interval, reset the ADDM sensitivity to normal, and re-create the SALES_TIME_BIX index. @c:\wkdir\cleanup_dina.sql

Back to Topic List

Summary

In this tutorial, you learned how to:

	Use Enterprise Manager and the SQL Tuning Advisor to proactively tune SQL statements
	Use Enterprise Manager to review detailed information on SQL statement execution

Back to Topic List

Move your mouse over this icon to hide all screenshots.