Tuning with Parameters
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".
10gr2 Note: Starting in
Oracle 10g release 2, 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.
While all SQL tuning professionals advocate tuning each individual
SQL statement to reduce logical I/O, there are many cases where you do
not have the luxury of tuning each-and-every SQL statement in an
application. In these cases, the best you can hope to do is adjust the
global optimizer parameters to optimizer as many SQL statements as
Cases where you may not be able to tune individual SQL statements
- Vendor applications - If you do not have access to the
source code, if can be very challenging too change the SQL execution
- Ad-Hoc query tools - Tools that generate dynamic SQL are
notoriously difficult to tune because there is no SQL source code.
- Economic constraints - If IT management will not pay to
tune 10,000 SQL statements then you are forced to adjust system-wide
While system-wide tuning is not the best solution to SQL tuning it
can be extremely helpful in tuning system with regular patterns of SQL
processing. It is not uncommon to see the Oracle DBA change the
optimizer parameters depending on the time-of-day and day-of-week to
accommodate changes in the type of SQL in the library cache.
Approaches to system-wide SQL tuning include these steps:
- Changes to Oracle parameters - Changes to optimizer_mode,
optimizer_index_cost_adj and optimizer_index_caching can make a huge
difference in the execution plans of SQL.
- Changes to statistics - Using the dbms_stats package to
import specialized statistics (geared to the current processing
mode) can make a huge difference in SQL execution speed.
- Using automatic query re-write - Using Oracle
Materialized Views you can pre-aggregate and pre-summarize data to
reduce the amount of run-time table joins. For low-update databases,
you can also pre-join tables together to improve processing speed.
Tuning the SQL workload by adjusting the tuning
parameters is a part of Oracle 11g Real Application Testing (RAT) and
the SQL Performance Analyzer (SPA), a codification of the top-down
approach to Oracle tuning:
For testing, you can quickly test the effect of
another optimizer parameter value at the query level without using an
"alter session" command, using the new
opt_param SQL hint:
select /*+ opt_param('optimizer_mode','first_rows_10')
*/ col1, col2 . . .
select /*+ opt_param('optimizer_index_cost_adj',20)
*/ col1, col2 . .
Let's start by looking at the important Oracle optimizer
In Oracle9i there are many optimizer modes, all determined by the
value of the optimizer_mode parameter. The values are rule,
choose, all_rows, first_rows, first_rows_1,
first_rows_10 and first_rows_100.
We need to start by defining what the "best" execution plan is. At
any given time, all SQL statement in the library cache need to have
the "best" execution plan. (Of course this may change frequently
because at any given time the processing demands may change) Is the
best execution plan the one that begins to return row the fastest, or
is the best execution plan the one that executes with the smallest
amount of computing resources? Of course, the answer depends on the
processing for your database, and Oracle offers two optimizer modes
that allow you to choose your definition of the "best" execution plan
- optimizer_mode=first_rows - This optimizer_mode favors
index access over full table scan access and is used when you want a
query to start returning rows quickly, even if the overall amount of
logical I/O is higher than a full-table scan. The first_rows
optimizer_mode is generally used in online system where the end-user
wants to see the first page of query results as quickly as possible.
- optimizer_mode=all_rows - This optimizer mode favors
full-table scans (especially parallel full-table-scans) in cases
where the server resources will be minimized. The all_rows mode is
generally used during batch-oriented processing and for data
warehouses where the goal is to minimize server resource
- optimizer_mode=first_rows_n - Starting with Oracle9i we
also have a new optimizer_mode to optimizer a query for a smaller
result set. The values are first_rows_1, first_rows_10 and
first_rows_100, and you can use this parameter to ensure that Oracle
optimizes the SQL
The all_rows optimizer mode is designed to minimize computing
resources and it favors full-table scans. Index access
(first_rows) adds additional I/O overhead, but they return rows
faster, back to the originating query:
Oracle full-table scan
While the optimizer_mode parameter controls the overall
behavior of the CBO, there are other Oracle parameters that have a
great effect on CBO behavior. Oracle provides several important
parameters to control the choices made by the CBO:
- optimizer_index_cost_adj - This is an important CBO
parameter because it adjusts the propensity of the CBO to favor
index access over full-table scan access. The smaller the value, the
more like that the CBO will use an available index.
- optimizer_index_caching - This is the parameter that
tells Oracle how much of your index is likely to be in the RAM data
buffer cache. The setting for optimizer_index_caching effects
the CBOs decision to use an index for a table join (nested loops),
or to favor a full-table scan.
- db_file_multiblock_read_count - When set to a high value
(with larger servers), the CBO recognizes that scattered
(multi-block) reads may be less expensive than sequential reads.
This makes the CBO friendlier to full-table scans.
- parallel_automatic_tuning - When set "on", full-table
scans are parallelized on Oracle servers with many CPUs. Because
parallelized full-table scans can be very fast, the CBO will give a
higher cost to index access, and be friendlier to full-table scans.
- hash_area_size (if not using pga_aggregate_target)
- The setting for hash_area_size parameter governs the
propensity of the CBO to favor hash joins over nested loop and sort
merge table joins.
- sort_area_size (only if you are not using the
pga_aggregate_target) - The sort_area_size influences the CBO
when deciding whether to perform an index access or a sort of the
result set. The higher the value for sort_area_size, the more likely
that a sort will be performed in RAM (thousands of time faster than
the TEMP tablespace), and the more likely that the CBO will favor a
sort over pre-sorted index retrieval.
In later installments we will examine these parameters more closely
and see how Oracle can optimize whole batches of SQL queries with a
Ion tool is
the easiest way to analyze Oracle table behavior (average
CPU cost per table access shown above) and Ion
allows you to spot hidden table-related performance trends.
our favorite Oracle tuning tool, and the only 3rd party
tool that we use.
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.
If you like Oracle tuning, you might enjoy my latest book "Oracle Tuning: The Definitive Reference" by Rampant TechPress. It's only $41.95(I don't think it is right to charge a fortune for books!) and you can
buy it right now at this link: