Oracle fully automated SQL
Oracle11g Tips by Burleson Consulting
This is a work in
progress excerpt from
the book "Oracle
11g New Features" by Rampant TechPress.
Oracle has touted their new 11g
?holistic? approach to SQL tuning as ?fully automated SQL tuning?,
but the marketing hype must be separated from the reality. The main
benefit is that the DBA can now test changes to global parameters
against a real-world workload, using a SQL Tuning Set (STS).
Holistic tuning in Oracle 11g is offered through several functional
areas, most importantly the SQL Performance Advisor (SPA) and the
automated SQL Plan Management (SPM) facility. SPA is the natural
evolution of the SQLAccess advisor:
10g SQLAccess Advisor ? The 10g SQL access advisor tests
real-world SQL workloads, recommending missing indexes and
11g SQL Performance Analyzer ? The SPA takes the SQLAccess
advisor one step further and implements tuning recommendations
for any SQL statements that run 3x faster (when tested with a
new workload). For more details, see
Inside the 11g
SQL Performance Advisor .
How fully automated SQL tuning works
In a nutshell, the
11g fully automated SQL tuning is a series of processes and tools,
loosely coupled for maximum flexibility.
A ? The Setup for fully automatic
SQL tuning ? Here we capture representative SQL workloads (SQL
tuning sets) and set-up a testing environment:
1 - Define the SQL workload -
The DBA defines a "set" of problematic SQL statements (or chooses a
representative workload). This is called the SQL Tuning set, or
STS. This uses the
2 - Set-up a changed environment
- Here you can chose to change your initialization parms, test your
performance against a previous release of the CBO (a very useful
features when testing upgrades) or conduct "custom experiments" on
the effect of environmental changes on your SQL tuning set.
B ? Initial SQL tuning ? Using
the SQL Performance Analyzer, we optimize our environment using the
SQL tuning set:
1 - Schedule & run workload tests
- The workload is scheduled for execution during "low usage"
periods, so that an empirical sample of real-world execution times
can be collected and compared, using different execution plans from
the two SQL tuning sets. To do this we run the
dbms_sqlpa package. You can
also use the OEM SPA ?Guided Workflow? wizard.
2 - Implement the changes - For
any statements that execute more then 3x faster, after the changes,
Oracle 11g will automatically implement the changes via "SQL
Profiles", a tool that bypasses the generation of an execution plans
for incoming SQL, replacing it with the pre-tuned access plan.
C ? Gather Baseline - Create the
SQL Plan Baseline - To enable automatic SQL plan capture, set the
optimizer_capture_sql_plan_baselines initialization parameter to
D ? Regression testing and
implementation ? We test global changes with the SQL Plan
Manager (SPM). As the system characteristics change, you can use
the SQL Plan Manager to test against real workloads and ensure that
all changed execution plans result in at least 3x faster
This is a huge
improvement over the hit-and-miss SQL tuning techniques of the past,
but it?s not a truly ?fully-automated? approach either. We must
remember that there will always be ?outlier? SQL statements that
must be tuned manually.
Fully Automated SQL Tuning
is not a Panacea
There are many
internal and external factors that influence the elapsed time for a
given SQL statement, and the 11g SQL Performance Analyzer (SPA) and
SQL Plan Management (SPM), the DBA can establish an "optimal
baseline" before diving into the tuning of individual SQL
Optimize the server kernel - Always tune your disk and
network I/O subsystem (RAID, DASD bandwidth, network) to
optimize the I/O time, network packet size and dispatching
frequency. Kernel settings have an indirect effect on SQL
performance. For example, a kernel setting may speed up I/O, a
change which is noted by the CBO workload statistics (using
dbms_stats.gather_workload_stats). This, in turn, directly
influences the optimizer's access decisions.
Adjust your optimizer statistics - Always collect and store
optimizer statistics to allow the optimizer to learn more about
the distribution of your data to take more intelligent SQL
access plans. Also, histograms can hypercharge SQL in cases of
determining optimal table join order, and when making access
decisions on skewed WHERE clause predicates. Also new in 11g,
multi-column statistics can be gathered for use by the optimizer
to determine optimal ways to run queries based upon multiple
Adjust optimizer parameters ? You can empirically determine
the best settings for optimizer_mode,
Optimize the instance - Your choice of db_block_size,
db_cache_size, and OS parameters (db_file_multiblock_read_count,
cpu_count, etc.) can influence SQL performance.
Tune with indexes and materialized views - Just as the 10g
SQLAccess Advisor recommends missing indexes and missing
materialized views, you should always optimize your SQL workload
with indexes, especially function-based indexes, a godsend for
Get the Complete
Oracle SQL Tuning Information
The landmark book
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
for 30% off directly from the publisher.