Inside the Oracle 11g SQL
Oracle 11g New Features Tips by Burleson
July 13, 2008
Oracle 11g SQL New Features Tips
The Oracle 11g SQL Performance Analyzer is a step in the
direction of fully automated SQL tuning, allowing the database
administrator to create a STS “workload”, a unified set of SQL which
comes from either the cursor cache (Shared Pool) or from the AWR
(the Automated Workload Repository). The Remote DBA can use exception
thresholds to select the SQL for each STS, based on execution
criteria such as disk reads, consistent gets, executions, etc. Once
the Remote DBA has chosen their STS, SPA allows them to run the workload
while changing Oracle environmental factors, namely the CBO release
level, init.ora parameters and customized hypothesis testing using
the guided workflow option.
The central question becomes which Oracle
initialization parameters would be the most appropriate within the
SQL performance analyzer? Because the SPA is used to measure
changes in SQL execution plans, it only makes sense that we would
want to choose those Oracle parameters which will influence the
behavior of the Oracle optimizer.
These would include the basic Oracle optimizer
parameters (including optimizer_index_cost_adj,
optimizer_mode, optimizer_index_caching), as well as
other important initialization parameters. We also have
non-optimizer parameters which effect SQL execution plan decisions.
db_file_multiblock_read_count - when this parameter is set
to a high value the Oracle cost based optimizer recognizes that
scattered multiblock reads may be less expensive than sequential
reads. (i.e. full table scans and full index scans).
10gr2 Note: Starting in
Oracle 10g release2, Oracle recommends not setting the
db_file_multiblock_read_count parameter, allowing Oracle to
empirically determine the optimal setting.
parallel_automatic_tuning - when
parallel_automatic_tuning is set to “on” the Oracle optimizer
will parallelize legitimate full table scans. Because we have told
Oracle that parallel full table scans can be done very quickly
using parallel query Oracle’s cost based optimizer will assign a
higher cost index access, making the optimizer friendlier to full
hash_area_size (if not overridden by
pga_aggregate_target) - the setting for hash_area_size
governs the propensity of Oracle’s optimizer to favor hash joins
over nested loop and for merge joins. This makes it an ideal
testing parameter for changes to Oracle memory regions so that you
can see how they would be affected within a production
pga_aggregate_target - the settings for
Pga_aggregate_target have a profound impact on the behavior of
Oracle SQL statements, making this an interesting test case for
the SQL performance analyzer, especially with regard of the
propensity of the Oracle optimizer to do in memory sorts, and hash
sort_area_size (if not overridden by
pga_aggregate_target) the sort_area_size parameter
influences the cost based optimizer when deciding whether or not
to perform index access, or to perform a sort of the ultimate
results set from the SQL query. The higher the value for
sort_area_size the more likely it will be that the Oracle 11g
optimizer will invoke a backend sort, because it knows that the
sort can be performed in memory. Of course, this depends upon the
Oracle optimizers estimated cardinality for the results set of the
Of course, we can change any parameters we
like. Let’s now see how the SPA captures change in SQL execution
A trip to the SPA
Until the advent of the Oracle 10g intelligent
SQL tuning advisors (The SQL Access advisor and SQL Tuning Advisor),
SQL tuning was a time-consuming and tedious task. That all started
to change in Oracle 10g, and it's even more exciting in Oracle 11g,
where Oracle has promised "fully automated" SQL tuning, via the new
SQL Performance Analyzer and improvements in the SQL advisories.
The Oracle 10g automatic tuning advisor allowed
us to implement tuning suggestions in the form of SQL profiles that
will improve performance. Now with Oracle11g, the Remote DBA can tell
Oracle to automatically apply SQL profiles for statements whenever
the suggested profile give 3-times better performance that the
existing statement. These performance comparisons are done by a new
11g administrative task that is executed during a user-specified
maintenance window. In a nutshell, the 11g fully automated SQL
tuning works like this:
1 - Define the SQL
workload - The Remote DBA defines a "set" of problematic SQL statements
using exception thresholds (e.g. all SQL with > 100,000 disk reads),
select from the cursor cache or the AWR. This is called the SQL
Tuning set, or STS.
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 feature when testing upgrades) or
conduct "custom experiments" on the effect of environmental changes
on your SQL tuning set.
3 - Schedule & run
your 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
4 - Implement the
changes – You can flag SQL statements for changes and tune them
with the 10g SQL Tuning advisor.
Tip: Also related is the
Oracle 11g automated SQL tuning Advisor, whereby you can
automatically implement changes that cause your SQL to run more than
The Oracle 11g automated SQL tuning advisor
will implement all execution plan changes via "SQL Profiles", a tool
that is conceptually similar to stored outlines, a method to
bypasses the generation of execution plans for incoming SQL,
replacing it with a pre-tuned access plan.
The automatic SQL tuning advisor also
recommends restructuring badly-form SQL, and adding missing indexes
and materialized views, but these require a manual decision.
Before we examine the nuances of the 11g fully
automated SQL tuning features, let's briefly review the goals of SQL
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.