| |
 |
|
Goals
of Holistic SQL Tuning in 11g
Oracle 11g New Features Tips by Burleson
Consulting
July 13, 2008 |
Oracle 11g SQL New Features Tips
Holistic tuning in Oracle 11g is a broad-brush
approach that can save thousands of hours of tedious manual SQL
tuning. By applying global changes, the DBA can tune hundreds of
queries at once, and implement them via SQL profiles.
DBA’s who fail to do holistic SQL tuning first
(especially those who tune SQL with optimizer directives), may find
that subsequent global changes (e.g. optimizer parameter change) may
un-tune their SQL. By starting with system-level tuning, the DBA can
establish an "optimal baseline", before diving into the tuning of
individual SQL statements:
- Optimize the
server kernel - You must 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.
- Adjusting your
optimizer statistics - You must always collect and store
optimizer statistics to allow the optimizer to learn more about the
distribution of your data to take more intelligent execution 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 column criteria.
- Adjust optimizer
parameters - Optimizer optimizer_mode,
optimizer_index_caching, optimizer_index_cost_adj.
- Optimize your
instance - Your choice of db_block_size, db_cache_size,
and OS parameters (db_file_multiblock_read_count, cpu_count,
&c), can influence SQL performance.
- Tune your SQL
Access workload with physical indexes and materialized views -
Just as the 10g SQL Access advisor recommends missing indexes and
missing materialized views, you should always optimize your SQL
workload with indexes, especially function-based indexes, a Godsend
for SQL tuning.
Now, Oracle 11g does not have all of the
intelligence of a human SQL tuning expert, but the 11g SQL Performance
Analyzer (SPA) is a great way to test for the effect of environmental
changes to your Oracle environment.
Let's take a closer look at how Oracle has
automated the SQL tuning process with SPA.
The SPA treatment
The SQL performance analyzer allows the DBA to
define the SQL Tuning set (the STS), as a source for the test (usually
using historical SQL from the AWR tables).
The SPA receives one or more SQL statements as
input (via the SPA), and provides advice on which tuning conditions
have the best execution plans, gives the proof for the advice, shows
an estimated performance benefit, and allegedly has a facility to
automatically implement changes that are more than 3x faster than the
"before" condition".
This is an
excerpt from the new book
Oracle 11g New Features: Expert Guide to the Important New
Features by John Garmany, Steve Karam, Lutz Hartmann, V. J. Jain,
Brian Carr.
You can buy it direct from the publisher for 30% off. |
