Inside the 11g SQL Performance Analyzer (SPA)
Oracle Tips by Burleson Consulting
This is a preview from the
11g New Features", by Rampant TechPress, featuring John Garmany, plus Oracle
ACE's Steve Karam, Lutz Hartmann, and VJ Jain.
A trip to the SPA - Inside the 11g SQL performance analyzer
The declarative nature of the
SQL syntax has always made it difficult to perform SQL tuning. The basic tenet
of cost-based SQL optimization is that the person who writes a SQL query simply
"declares" what columns they want to see (the SELECT clause), the tables where
the columns reside (the FROM clause), and the filtering conditions (the WHERE
clause). It's up to the SQL optimizer to always determine the optimal
execution plan. This is a formidable challenge, especially in a dynamic
environment, which is why Oracle introduced the 10g new feature of CBO dynamic
Some people are calling the SPA with an synonym, the
SQL Performance Manager, or SPM.
Oracle tuning consultants
have know for many years that the best way to tune an Oracle system is to take a
top-down approach, finding the optimal configuration for external factors (i.e.
OS kernel settings, disk I/O subsystem), and determining the best overall
setting for the Oracle instance (i.e. init.ora parameters).
Holistic tuning involves
tuning a representative workload, adjusting global parameters in order to
optimize as much SQL as possible. Only then, is it prudent to start tuning
individual SQL statements. Many Oracle professional who adopt a bottom-up
approach (tune the SQL first), find all of their hard-work un-done when a change
is made to a global setting, such as one of the SQL optimizer parameters or
recomputing optimizer statistics.
SQL tuning approach is new, and given the misleading marketing name "fully
automated SQL tuning". Holistic tuning is well-known to working DBA's who
have been doing manual workload-based optimization since Oracle6. Now in
11g, Oracle gives us an automated method.
The Oracle 11g SQL
performance analyzer (SPA), is primarily designed to speed up the holistic SQL tuning
process, automating much of the tedium.
Once you create a workload
(called a SQL Tuning Set, or STS), Oracle will repeatedly execute the workload,
using sophisticated predictive models (using a regression testing approach) to
accurately identify the salient changes to execution plans, based on your
Using SPA, we can predict the
impact of system changes on a workload, and we can forecast changes in response
times for SQL after making any change, like parameter changes, schema changes,
hardware changes, OS changes, or Oracle upgrades; any change that influence SQL
plans is a good candidate for SPA.
Decision Support and Expert Systems Technology
Oracle had made a commitment
to Decision Support Systems (DSS) Technology starting in Oracle 9i when they
started to publish 'advisory' utilities, the result of monitoring the Oracle
instance and coming up with estimated benefits for making a change to the
database configuration. In the world of applied artificial intelligence, an
expert system (e.g. AMM, ASM) solves a well-structured problem for the DBA,
while a decision support system solves a semi-structured problem with the DBA,
who supplies the human intuition required to solve a complex problem.
Oracle has made a commitment
to distinguishing themselves in the database marketplace, and this is one of the
major reasons that they command a major market share. One of the most exciting
areas of Oracle technology is automation, especially the self-management
features. Oracle has now automated many critical components, including memory
advisors (AMM), automated storage management (ASM), and Oracle is now working to
enhance more intelligent utilities including ADDM, the Automated Database
Diagnostic Monitor, and the brand new 11g SQL performance analyzer (SPA).
The Oracle 11g SPA functions
as a DSS, helping the DBA by automating the well-structured components of a
complex tuning task, such as hypothesis testing. In SPA, the DBA defines a
representative workload and then tests this workload empirically, running the
actual queries against the database and collecting performance metrics.
allows the DBA to obtain real-world performance results for several types of
- Optimizer software
levels - You can compare SQL
execution between different release of the cost-based optimizer (CBO)
parameters - You can pre-test
changes to global parameters, most often the Oracle optimizer parameters (optimizer_mode,
Prior to Oracle
10g, adjusting these optimizer parameters was the only way to compensate for
sample size issues with dbms_stats.
As of 10g, the use of
dbms_stats.gather_system_stats and improved sampling within dbms_stats had made adjustments to these parameters far less important.
Ceteris Parabus, always adjust CBO statistics before adjusting optimizer
parms. For more details on optimizer parameters, see my latest book "Oracle
Tuning: The Definitive Reference".
- Guided workflow
- This is a hypothesis testing
option that allows the DBA to create customized experiments and validate
their hypotheses using empirical methods.
Instead of using theory and
mathematical calculations, Oracle SPA tests the SQL Tuning Set (STS) workload in
a real-world environment, running the workload repeatedly while using heuristic
methods to tally the optimal execution plan for the SQL. The DBA can then
review the changes to execution plans and tune the SQL (using the SQLTuning
Advisor) to lock-in the execution plans using SQL profiles.
Let's take a closer look at
SPA and see how holistic SQL tuning can remove the tedium of tuning SQL
Inside the Oracle 11g SQL performance analyzer
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 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 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.
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. For more details,
see my notes on
10gR2 automatically tuned multi-block reads.
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
he optimizer friendlier to full table scans.
(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 environment.
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 joins.
(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 SQL query.
- This controls the automatic capture of new SQL plan baselines for
repeatable SQL statements.
- This parameter allows the CBO to seek out plans in SQL plan baselines for
the SQL statement being compiled. If any are found, then the optimizer will
cost each plan in the SQL plan baseline and pick the one with the lowest
There are several new queries to display the statis of SQL plan
s.exact_matching_signature = b.signature
s.sql_plan_baseline = b.plan_name;
Of course, we can change any
parameters we like. Let's now see how the SPA captures changes in SQL execution
A trip to the SPA
Until the advent of the
Oracle 10g intelligent SQL tuning advisors (The SQLAccess advisor and SQLTuning
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 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:
Define the SQL workload - The 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
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.
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 plans.
Implement the changes - You can flag SQL statements for changes and tune
them with the 10g SQLTuning 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 3x faster.
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 tuning.
The goals of holistic SQL tuning
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
- 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 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 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".
Gathering the SQL Tuning set
The SQL workload (the STS)
can be thought of as a container for conducting and analyzing many SQL
statements. The STS is fed to the SPA for real-world execution with
before-and-after comparisons of changes to holistic "environmental" conditions,
specifically CBO levels or changed init.ora parameters.
Internally, the SPA is stored
as a database object that contains one or more SQL statements combined with
their execution statistics and context such as particular schema, application
module name, list of bind variables, etc. The STS also includes a set of basic
execution statistics such as CPU and elapsed times, disk reads and buffer gets,
number of executions, etc.
When creating a STS, the SQL statements can be filtered by different patterns
such as application module name or execution statistics, such as high disk
reads. Once created, STS can be an input source for the SQL Tuning Advisor.
Typically, the following steps are used to define the STS using the
dbms_sqltune package. The steps within the new 11g OEM screen for "guided
workflow" are simple and straightforward, and serve as an online interface to
the dbms_sqltune.create_sqlset procedure:
Options ' Choose a name for your SQL tuning set (STS). This encapsulated
SQL workload is created using the dbms_sqltune.create_sqlset procedure.
For example, the following script can be used to create a STS called SQLSET1:
Load methods - Here is where you can choose the source for your SQL
workload, and to take historical SQL statements from AWR.
Filter options - You can choose 'filtering' conditions, based on your
specific tuning needs. For example, if your database is disk I/O bound, you
might choose only SQL statements that have more than 100k disk reads.
Schedule ' This is an interface to the dbms_scheduler package,
allowing you to define and schedule a job.
Review ' Here you can see the actual source calls to
dbms_sqltune.create_sqlset and the dbms_scheduler.create_job
procedure call syntax.
There is an interface to the
SQL Performance Analyzer in the enterprise manager in the OEM Advisor Central
area, and a number of new to dba_advisor views have been added in 11g
which will display information from the SQL Performance Advisor.
The technology behind SPA is
encapsulated inside a new package called dbms_sqlpa. Here is an overview
for the procedures of the dbms_sqlpa package:
- CANCEL_ANALYSIS_TASK -'
This procedure cancels the currently executing task analysis of one or more
- CREATE_ANALYSIS_TASK -
This function Creates an advisor task to process and analyze one or more SQL
- DROP_ANALYSIS_TASK -
This procedure drops a SQL analysis task.
- EXECUTE_ANALYSIS_TASK -
This function & procedure executes a previously created analysis task.
- This procedure interrupts the currently executing analysis task.
- REPORT_ANALYSIS_TASK -
This function displays the results of an analysis task.
- RESET_ANALYSIS_TASK -
This procedure resets the currently executing analysis task to its initial
- RESUME_ANALYSIS_TASK -
This procedure resumes a previously interrupted analysis task that was
created to process a SQL tuning set.
SET_ANALYSIS_TASK_PARAMETER - This procedure sets the SQL analysis task
SET_ANALYSIS_DEFAULT_PARAMETER - This procedure Sets the SQL analysis task
parameter default value
In sum, the new 11g SQL
Performance Analyzer is a great way to test for holistic tuning changes.
Remember, the savvy Oracle DBA will always adjust their Oracle initialization
parameters to optimizer as much of the workload as possible before diving into
the tuning of specific SQL statements.
Oracle 11g guided workflow screen
The OEM screen for the SPA
'guided workflow' contains a pre-defined set of steps for holistic SQL workload
1 - Create SQL
Performance Analyzer Task, based on SQL Tuning Set
2 - Replay SQL Tuning Set
in Initial Environment
3 - Create replay Trial
using changed environment
4 - Create Replay trial
comparison (using trials from step 2 and step3)
5 - View trial comparison
Using the guided workflow
functionality, we can take our SQL tuning set and execute it twice (before and
after), saving the SQL execution results (disk reads, buffer gets) using some of
the common SQL execution metrics found in the dba_hist_sqlstat table:
Guided Workflow Items
Here it's important to note
that the guided workflow does not measure these important SQL execution metrics
such as sorts and fetches.
Comparing the results SPA Results
The final step in SPA allows
the DBA to quickly isolate sub-optimal SQL statement and tune them with the 11g
SQLTuning Advisor. When viewing the results, you can use OEM for a visually
display of all delta values between the execution run, but most important, you
can do a side-by-side comparison of the before-and-after execution plans.
Oracle has always been ahead
of the curve in automating well-structured DBA tasks, and the SPA is just the
latest incarnation in real-world SQL tuning tools. Tools such as SPA free-up
the DBA to pursue other important DBA tasks, relieving them of the tedium of
individually tuning SQL statements.
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.