Automatic SQL Tuning
The Oracle SQL optimizer has always been a key component of
execution (explain) plan management. The optimizer was charged
with the responsibility of making decisions about execution plans.
However, the optimizer has not always been given sufficient time to
gather the information necessary for the best possible decision.
Automatic SQL tuning and SQL Profiles have made this process easier for the optimizer.
The evolution of the internal aids to the optimizer, including
SQL Profiles, is as follows:
- Stored Outlines: Also called
Optimizer Plan Stability, stored outlines were cumbersome to
manage. Additionally it was difficult to swap out
execution plans when using plan stability.
- SQL Profiles -
A SQL Profile is a bundle of improved optimizer statistics that
is stored in the data dictionary. SQL Profiles are create
by running Automatic SQL Tuning.
The SQL
Profile approach first appeared in Oracle 10g. Allowed to
run in tuning mode, the optimizer can gather additional
information for making tuning recommendations on specific SQL
statements. Tuning mode also allows the optimizer to
recommend the gathering of statistics on objects with stale or
missing statistics. These additional statistics are stored
in an SQL Profile. A SQL tuning Set (STS) could be tested
as a workload, and Oracle would allow the DBA to implement
changes to execution plans.
- SQL Plan management -
Starting in 11g, there is an even easier to use approach to
locking-down SQL execution plans. The 10g SQL Profile approach
is deprecated, and uses only two parameters,
optimizer_capture_sql_plan_baselines and
optimizer_use_sql_plan_baselines. Also see
dba_sql_plan_baselines tips.
SQL Profiles
Alluded to above, the automatic tuning advisor allowed the
implementation of tuning suggestions in the form of SQL profiles
(enhanced CBO statistics, stored in the data dictionary) that will improve performance. SQL profiles can be used the
same way as their historic counterparts, stored outlines.
However, the SQL Profile is easier to use and manage with the help
of the automatic tuning advisor.
Simply put, the SQL Profile is a collection of historical
information from prior runs of the SQL statement. The SQL
Profile can contain comparison details of the actual and estimated
cardinality and predicate selectivity, etc. A SQL Profile is stored
persistently in the data dictionary, so it does not require any
application code changes.
In comparison to normal optimization, a SQL profile helps generate a
better execution plan because it is tested against a
real-world workload in the SQL Tuning Set (STS). The SQL
statement is thoroughly profiled with the aid of additional tasks
like checking for advanced predicate selectivity, correlation
between columns, join skews, and complex predicates such as
functions. Once a SQL statement is profiled and stored, differing
execution plans can be invoked at will.
The optimizer may also be able to improve performance by altering
session specific parameters such as the optimizer_mode.
The SQL Profile stores the information on possible improvements.
The information in the SQL Profile, if accepted, is available to the
optimizer when running in normal rather than tuning mode.
Where stored outlines fixed the execution plan, a SQL Profile can
continue to be beneficial as the contents of the table change.
Thus, it is not necessary or preferred to update SQL Profiles as
long as they are beneficial.
Swapping SQL Profiles
While it may not be necessary to swap SQL Profiles as often as it
was necessary to swap stored outlines, there are times when it
becomes desirable. The concept of
swapping SQL profiles is
fairly simple.
A SQL profile that specifies the SQL statement
is defined for the statement to be tuned. The new SQL Profile
includes an alternative execution plan, in the form of hints.
When this SQL is executed and hits the library cache, Oracle
detects that a SQL profile exists for this statement, and
automatically applies the hints to change the execution plan.
This approach allows SQL statements to be tuned
without ever having to touch the SQL statement itself.
To do this, we use the DBMS_SQLTUNE package which has an
import_sql_profile procedure which allows you to swap hints
from one SQL profile into another SQL profile.
dbms_sqltune.import_sql_profile(
sql_text => 'select * from emp',
profile =>
sqlprof_attr('ALL_ROWS','IGNORE_OPTIM_EMBEDDED_HINTS')
category => 'DEFAULT',
name => 'change_emp',
force_match => &&6
);
To see how you can swap-out SQL profiles,
examine the
dbms_sqltune package and the import_sql_profile
procedure.
Although SQL Profiles have been an improvement over stored
outlines,
execution plan management in 11g goes further to help lock down
critical SQL execution plans, and tests execution timings before
implementing changes.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
