 |
|
11g Integration of SQL Plan Management with Automatic SQL Tuning
Oracle 11g New Features Tips by Burleson
Consulting
June 29, 2008 |
Oracle 11g New Features Tips
The next step is also
already implemented in Oracle database 11g. It is called
Automatic SQL Tuning.
With this feature enabled, Oracle runs an automatic task job every
night in a maintenance window. It is also called
Automatic
SQL Tuning Advisor and
searches for high load SQL, then starts automatically tuning the
worst statements. Output could be recommendations about missing
indexes for instance, and if allowed, automatic implementation of
automatically created and tested SQL Profiles. This is not enabled
by default. The job would then feed the SQL plan baselines with the
plans from the automatically created and implemented SQL profiles
and mark the new plans as ENABLED and ACCEPTED.
The SYS_AUTO_SQL_TUNING_TASK runs in the daily
maintenance window and can be monitored with the data dictionary
view DBA_ADVISOR_EXECUTIONS.
The Automatic SQL Tuning job can be managed via the built in
package
DBMS_AUTO_TASK_ADMIN. The
choice can also be made to not fix the plan in the new baseline and
allow the optimizer from now on to evolve the plan baseline and use
new best cost plans found for the statement, but only if it has been
verified that they do not cause performance regression.
If the Automatic Tuning job finds a better plan
and implements a SQL Profile automatically and automatic
implementation of SQL Profiles has been allowed, it adds the new
plan to the plan baseline but does not verify existing unaccepted
plans. The criteria for automatic implementation of SQL profiles and
feeding new plans into the plan baselines would be that the
improvement of the new plan sums up to at least three times less
cost (sum of CPU and I/O time). Only High Load Repeatable SQL is
automatically tuned. Who cares about low load SQL?
The syntax to enable Automatic SQL
Tuning is as follows:
LUTZ AS
SYSDBA @ prod11g1 SQL> BEGIN
dbms_sqltune.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK', 'ACCEPT_SQL_PROFILES',
'TRUE');
END;
% The default
value for ACCEPT_SQL_PROFILES is FALSE!
It is also possible to adjust the
Automatic SQL Tuning Task in the graphical interface:
Figure 29:
Automatic SQL Tuning Settings Screen
For more detailed information on
Automatic SQL Tuning, please refer to Chapter 6 in this book.
There have been multiple techniques to
control execution plans available for quite a while. These include
hints, stored outlines and SQL profiles. What all of these methods
have in common is that they need manual intervention since they are
fixed remedies which do not adjust to changed demands automatically.
Also, they can only be used after the problem has actually occurred,
so they are reactive methods.
SQL Plan management is a proactive
approach to possible upcoming problems and can help to ensure that
performance regression is prevented and only execution plans which
are significantly better than the “well tuned and well known” plans
can be used by the optimizer.
