Using SQL Tuning Advisor through OEM
-
Choose Top SQL. The Top SQL page
appears as shown below. (The page had to be captured in two
separate screenshots as it had to be scrolled down for a
complete view.) This page has two tabs: Spot SQL and
Period SQL. Spot SQL lists the top SQL from the cursor
cache, while Periodic SQL from AWR.
Figure 7.3 - SQL Tuning Advisor
-
Select the Top SQL statement by
checking the box to the left of the statement and clicking
the Run SQL Tuning Advisor button.
-
The following screen (three screen
shots) appears with the SQL tuning options page. On this
page, the name of the task
can be entered along with a
description, the scope and analysis methods
can be selected
(Comprehensive or Limited), and a start time
for the task
provided. Choose Immediately and click
OK.
Figure 7.4 - SQL Tuning Advisor
Figure 7.5 - SQL Tuning Advisor
Figure 7.6 - SQL Tuning Advisor
-
Go back to the Advisor Central Page.
The status for the advisor tasks are listed here. Wait
until the task status changes to COMPLETED. Refresh the
browser to view the updated status. When the task status is
COMPLETED, select the task and click View Result. The
following is a representative figure:
