Tuning SQL Manually Using SQL Tuning Advisor
As described in Identifying High-Load SQL Statements , Automatic Database Diagnostic Monitor (ADDM) automatically identifies high-load SQL statements. If ADDM identifies such statements, then click Schedule/Run SQL Tuning Advisor on the Recommendation Detail page to run SQL Tuning Advisor.
To tune SQL statements manually using SQL Tuning Advisor:
-
Access the Database Home page.
See "Accessing the Database Home Page" for more information.
-
From the Performance menu, select Advisors Home
If the Database Login page appears, then log in as a user with administrator privileges. The Advisor Central page appears.
-
In the Advisors section, click SQL Advisors. The SQL Advisors page appears.
-
In the SQL Tuning Advisor section, click SQL Tuning Advisor.
The Schedule SQL Tuning Advisor page appears.
-
In the Name field, enter a name for the SQL tuning task.
If unspecified, then SQL Tuning Advisor uses a system-generated name.
-
Do one of the following:
-
To run a SQL tuning task for one or more high-load SQL statements, under Overview click Top Activity.
The Top Activity page appears.
Under Top SQL, select the SQL statement that you want to tune.
In the Actions list, select Schedule SQL Tuning Advisor and click Go.
See Also:
"Identifying High-Load SQL Statements Using Top SQL" to learn how to identify high-load SQL statements using the Top Activity page
-
To run a SQL tuning task for historical SQL statements from the Automatic Workload Repository (AWR), under Overview click Historical SQL (AWR).
The Historical SQL (AWR) page appears.
Under Historical SQL (AWR), click the band below the chart, and select the 24-hour interval for which you want to view SQL statements that ran on the database. Under Detail for Selected 24 Hour Interval, select the SQL statement you want to tune, and click Schedule SQL Tuning Advisor.
-
To run a SQL tuning task for a SQL tuning set, under Overview click SQL Tuning Sets.
The SQL Tuning Sets page appears.
Select the SQL tuning set that contains the SQL statements you want to tune and then click Schedule SQL Tuning Advisor.
See Also:
"Creating a SQL Tuning Set" to learn how to create SQL tuning sets
The Schedule SQL Tuning Advisor page reappears.
-
-
To display the SQL text of the selected statement, expand SQL Statements.
-
Under Scope, select the scope of tuning to perform. Do one of the following:
-
Select Limited.
A limited scope takes approximately 1 second to tune each SQL statement but does not recommend a SQL profile.
-
Select Comprehensive, and then set a time limit (in minutes) for each SQL statement in the Time Limit per Statement field, and a total time limit (in minutes) in the Total Time Limit field. Note that setting the time limit too small may affect the quality of the recommendations.
Comprehensive mode may take several minutes to tune a single SQL statement. This mode is both time and resource intensive because each query must be hard-parsed. You should only use comprehensive scope for high-load SQL statements that have a significant impact on the entire system.
See Also:
"Managing SQL Profiles" to learn more about SQL profiles
-
-
Under Schedule, do one of the following:
-
Select Immediately and then click Submit to run the SQL tuning task immediately.
The Processing: SQL Tuning Advisor Task page appears.
-
Select Later to schedule a specific time in the future, and then click OK.
-
-
From the Performance menu, select Advisors Home.
The Advisor Central page appears.
Under Advisor Tasks, the Results sections lists the result of advisors.
-
Select a SQL Tuning Advisor type result from the table and then click View Result.
The Recommendations for SQL ID page appears.
If you used a SQL tuning set, then multiple recommendations may be shown. To help you decide whether to implement a recommendation, an estimated benefit of implementing the recommendation is displayed in the Benefit (%) column. The Rationale column displays an explanation of why the recommendation is made.
