Improving SQL Statement Tuning with Automatic SQL Tuning
Improving SQL Statement Tuning with Automatic SQL Tuning
This tutorial describes how to benefit from Automatic SQL
Tuning to automatically tune your high loaded SQL statements.
Approximately 20 minutes
This tutorial covers the following topics:
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.
Oracle Database 11g further automates the SQL Tuning process
by identifying problematic SQL statements, running SQL Tuning Advisor on them,
and implementing the resulting SQL profile recommendations to tune the statement
without requiring user intervention.
Back to Topic List
Before you perform this tutorial, you should:
| 1. |
Install Oracle Database 11g.
|
| 2. |
Download and unzip the ast.zip
file into your working directory (that is, wkdir ) and navigate into
your working directory.
|
| |
Modify the directories in all of the .sh scripts to point to the directory where you placed the files. |
Back to Topic List
Automatic SQL Tuning uses the AUTOTASK framework through
a new task called “Automatic SQL Tuning” that runs every night by
default. Here is a brief description of the automated SQL tuning process in
Oracle Database 11g:
- Step 1: Based on the AWR Top SQL identification, Automatic SQL Tuning targets
for automatic tuning.
- Steps 2 and 3: While the Automatic SQL Tuning task is executing during the
maintenance window, the previously identified SQL statements are automatically
tuned by invoking SQL Tuning Advisor. As a result, SQL profiles will be created
for them if needed. However, before making any decision, the new profile is
carefully tested.
- Step 4: At any point in time, you can request a report about these automatic
tuning activities. You then have the option of checking the tuned SQL statements
to validate or remove the automatic SQL profiles that were generated.
The following scenario illustrates the above behavior
by forcing the Automatic SQL Tuning task to run after you executed some high
loaded SQL statements. You force Automatic SQL Tuning execution simply because
you do not want to wait for the task to run at night. To force its execution,
you simply open the maintenance window at the time you execute this OBE.
Perform the following steps to understand the use
of Automatic SQL Tuning:
| 1. |
Connect as user SYS under Enterprise Manager Database
Control.
|
| 2. |
Once on the Home page, click the Server tab. On the
Server sub-page, click the Automated Maintenance Tasks link.
|
| 3. |
On the Automated Maintenance Tasks page, click the Configure button. On the Automated Tasks Configuration page, click the Configure botton located in front of the Automatic SQL Tuning line.
|
| 4. |
On the Automatic SQL Tuning Settings page, select Yes for Automatic Implementation of SQL Profiles. By default, automatic SQL
profile implementation is turned off because it requires licensing with
the Tuning pack. Once done, click Apply. Then, click the Automated Maintenance
Tasks Configuration breadcrumb followed by the Automated Maintenance Tasks
breadcrumb.
|
| 5. |
Open a terminal window and connect as user oracle. Navigate to the directory where you unzipped the ast.zip file. Execute
the ast_setup.sh
script. This script creates a new schema that is used for this OBE. The
script also clears the previous executions of Automatic SQL Tuning.
./ast_setup.sh
|
| 6. |
From the same terminal window, execute the run_workload_stream.sh
script. This script executes multiple times a SQL statement that was voluntarily
rendered inefficient. Note the time it takes to complete its execution.
./run_workload_stream.sh
|
| 7. |
From the same terminal window, execute the run_ast.sh
script. This script forces the maintenance window to open that triggers
the Automatic SQL Tuning task to execute and thus automatically tune high
loaded SQL statements. This is done to avoid waiting the following night
for the maintenance window to open automatically. In real life, you will
not have to force the maintenance window to open.
./run_ast.sh
|
| 8. |
From the same terminal window, re-execute the run_workload_stream.sh
script. This script executes multiple times a SQL statement that was voluntarily
rendered inefficient. Note the time it takes to complete its execution.
In the example, it took around 5 seconds to execute the script. This is
due to the fact that Automatic SQL Tuning tuned this particular statement,
and a profile that gives a much better plan was automatically implemented.
./run_workload_stream.sh
|
| 9. |
From your terminal session, connect as user ast (password
ast) under SQL*Plus, and force an AWR snapshot creation. Once done, exit
from your SQL*Plus session.
|
| 10. |
Back to the Enterprise Manager Automated Maintenance
Tasks page, click the Automatic SQL Tuning link.
|
| 11. |
This takes you to the Automatic SQL Tuning Results Summary
page where you can see that only one SQL statement was tuned during the
last session, and that two recommendations were spotted: One SQL profile
was automatically implemented, and one index creation was only recommended
but not implemented. This page also reports the Profile Effect Statistics.
It is clear that the recommended profile was very beneficial. On this
page, click the View Report button.
|
| 12. |
On the Automatic SQL Tuning Result Details page, you
can check all the statements that were considered for automatic tuning.
Note that even if automatic SQL profile implementation is not enabled,
you still get information about the recommended SQL profile. In this case,
the profile was automatically implemented. Select the SQL statement corresponding
to the AST schema, and click the View Recommendations button.
|
| 13. |
On the Recommendations for SQL ID page, you can see
the complete text for the corresponding SQL statement. Select the recommendation
corresponding to the SQL profile, and click the associated Compare Explain
Plans icon. You can see the different plans that were generated: before
and after SQL profile implementation. Once done, click the SQL ID breadcrumb
link.
|
| 14. |
Back to the Recommendations For SQL ID page, click the
SQL text corresponding to the tuned statement.
|
| 15. |
On the SQL Details, Tuning History sub-page, you can
clearly see that this statement was tuned by the Automatic SQL Tuning
task. On the Plan Control sub-page, notice that a SQL profile was automatically
implemented.
|
| 16. |
However, you still have the possibility to retrieve
the same information using PL/SQL. From your terminal session, execute
the get_task_report.sh
script. This script shows you the result of the previous analysis using
PL/SQL.
./get_task_report.sh
|
Back to Topic List
In this tutorial, you learned how to use SQL Access Advisor.
Back to Topic List
Place the cursor over this icon to hide all screenshots.
|
Bookmark
