The Best Oracle Resource on the Web
Oracle Expert Tuning Secrets
by Donald K. Burleson
database currently dominates the market for database software. Through the
release of Oracle8, Oracle8i, and Oracle9i, Oracle has evolved into one of the
world's most sophisticated database solutions. The challenge for IT
professionals is to ensure that they are able to leverage Oracle's powerful
features to improve the productivity of their organizations. One of the most
effective ways to do this is through Oracle tuning.
Over the past ten
years Oracle has evolved into one of the world's most sophisticated databases.
As such, there is a mind-boggling array of tuning parameters and techniques, all
designed to improve the performance of your Oracle database.
Oracle tuning is
a phenomenally complex subject. Entire books have been written about the nuances
of Oracle tuning. However, there are some general goals that every Oracle DBA
should follow in order to improve the performance of their systems.
overview we will briefly cover the following Oracle topics:
External tuning - We must remember that Oracle does not run in a vacuum. Here we
will look at tuning the Oracle server for high performance.
re-sequencing to reduce disk I/O - We must understand how reducing I/O is the
most important goal of Oracle tuning.
-- Oracle SQL
tuning - Oracle SQL tuning is one of the most important areas of Oracle tuning,
and it is not uncommon to dramatically improve the performance of an SQL
statement by using a few simple SQL tuning rules.
-- Tuning Oracle
sorting - Sorting is a small but very important component of Oracle performance.
-- Tuning Oracle
contention - The setting for table and index parameters has a huge impact on
UPDATE and INSERT performance.
We always start
by tuning the Oracle external environment. No amount of Oracle tuning is going
to help if the server has a shortage of RAM or CPU resources.
Our Ion tool is
the easiest way to analyze Oracle performance and Ion
allows you to spot hidden performance trends.
Oracle does not
run in a vacuum. The performance of your Oracle database depends heavily on
external considerations. These external considerations include:
- CPU -
The amount of CPU cycles available can slow-down SQL. Whenever the run-queue
exceeds the number of CPUs on your Oracle server, you are CPU bound.
- RAM memory
- The amount of available RAM memory for Oracle can effect the performance of
SQL, especially in the data buffers and in-memory sorts.
- Large amounts of Net8 traffic contribute to slow SQL performance.
beginners make the mistake of attempting to tune the Oracle database before
ensuring that the external environment is not stressed. No amount of Oracle
tuning is going to help the performance of the database when an external
There are two
simple things to monitor when you are checking the external Oracle environment:
1 - Run
queue waits - When the number of run queue waits exceeds the number of
CPUs on your server, the server is experiencing a CPU shortage. The remedy is
to add additional CPUs on the server or turn off high processing components
such as Oracle Parallel Query.
2 - RAM page
in's - When you see RAM page -in operations, your existing RAM memory has
been exceeded, and memory pages are moving in from the swap space on disk. The
remedy is to add more RAM, reduce the size of the Oracle SGAs, or turn-on
Oracle's multi-threaded server.
You can view
server stats in a variety of ways using standard server tools such as vmstat,
glance, top and sar. Your goal is to ensure that your database server always has
enough CPU and RAM resources to manage the Oracle requests.
Next let's look
at how Oracle row-resequencing can dramatically reduce disk I/O.
As we noted,
experienced Oracle DBAs know that I/O is the single greatest component of
response time and regularly work to reduce I/O. Disk I/O is expensive because
when Oracle retrieves a block from a data file on disk, the reading process must
wait for the physical I/O operation to complete. Disk operations are about
10,000 times slower than a row's access in the data buffers. Consequently,
anything you can do to minimize I/O-or reduce bottlenecks caused by contention
for files on disk-can greatly improve the performance of any Oracle database.
If response times
are lagging in your high-transaction system, reducing disk I/O is the best way
to bring about quick improvement. And when you access tables in a transaction
system exclusively through range scans in primary-key indexes, reorganizing the
tables with the CTAS method should be one of the first strategies you use to
reduce I/O. By physically sequencing the rows in the same order as the
primary-key index, this method can considerably speed up data retrieval.
Like disk load
balancing, row resequencing is easy, inexpensive, and relatively quick. With
both techniques in your DBA bag of tricks, you'll be well equipped to shorten
response times-often dramatically-in high-I/O systems.
online transaction processing (OLTP) environments in which data is accessed via
a primary index, resequencing table rows so that contiguous blocks follow the
same order as their primary index can actually reduce physical I/O and improve
response time during index-driven table queries. This technique is useful only
when the application selects multiple rows, when using index range scans, or if
the application issues multiple requests for consecutive keys. Databases with
random primary-key unique accesses won't benefit from row resequencing.
Let's explore how
this works. Consider a SQL query that retrieves 100 rows using an index:
last_name like 'B%';
This query will
traverse the last_name_index, selecting each row to obtain the rows. This query
will have at least 100 physical disk reads because the employee rows reside on
different data blocks.
Now let's examine
the same query where the rows are re-sequenced into the same order as the
last_name_index. We see that the query can read all 100 employees with only
three disk I/Os (one for the index, and two for the data blocks), resulting in a
saving of over 97 block reads.
The degree to
which resequencing improves performance depends on how far out of sequence the
rows are when you begin and how many rows you will be accessing in sequence. You
can find out how well a table's rows match the index's sequence key by looking
at the dba_indexes and dba_tables views in the data dictionary.
dba_indexes view, we look at the clustering_factor column. If the clustering
factor-an integer-roughly matches the number of blocks in the table, your table
is in sequence with the index order. However, if the clustering factor is close
to the number of rows in the table, it indicates that the rows in the table are
out of sequence with the index.
The benefits of
row resequencing cannot be underestimated. In large active tables with a large
number of index scans, row resequencing can triple the performance of queries.
Once you have
decided to re-sequence the rows in a table, you can use one of the following
tools to reorganize the table.
- Copy the table
using Oracle's Create Table As Select (CTAS) syntax
in-place table reorganization tool
Next, let's take
a look at SQL tuning.
Oracle SQL tuning
is a phenomenally complex subject, and entire books have been devoted to the
nuances of Oracle SQL tuning. However there are some general guidelines that
every Oracle DBA follows in order to improve the performance of their systems.
The goals of SQL tuning are simple:
unnecessary large-table full table scans Unnecessary full table scans cause a
huge amount of unnecessary I/O, and can drag down an entire database. The
tuning expert first evaluates the SQL based on the number of rows returned by
the query. If the query returns less and 40 percent of the table rows in an
ordered table, or 7 percent of the rows in an unordered table), the query can
be tuned to use an index in lieu of the full table scan. The most common
tuning for unnecessary full table scans is adding indexes. Standard B-tree
indexes can be added to tables, and bitmapped and function-based indexes can
also eliminate full table scans. The decision about removing a full table scan
should be based on a careful examination of the I/O costs of the index scan
vs. the costs of the full table scan, factoring in the multiblock reads and
possible parallel execution. In some cases an unnecessary full table scan can
be forced to use an index by adding an index hint to the SQL statement.
small-table full table scans In cases where a full table scan is the fastest
access method, the tuning professional should ensure that a dedicated data
buffer is available for the rows. In Oracle7 you can issue alter table xxx
cache. In Oracle8 and beyond, the small table can be cached by forcing to into
the KEEP pool.
- Verify optimal
index usage This is especially important for improving the speed of queries.
Oracle sometimes has a choice of indexes, and the tuning professional must
examine each index and ensure that Oracle is using the proper index. This also
includes the use of bitmapped and function-based indexes.
- Verify optimal
JOIN techniques Some queries will perform faster with NESTED LOOP joins,
others with HASH joins, while other favor sort-merge joins.
These goals may
seem deceptively simple, but these tasks comprise 90 percent of SQL tuning, and
they don't require a through understanding of the internals of Oracle SQL. Let's
begin with an overview of the Oracle SQL optimizers.
briefly explore Oracle sorting and see how sort operations affect performance.
As a small but
very important component of SQL syntax, sorting is a frequently overlooked
aspect of Oracle tuning. In general, an Oracle database will automatically
perform sorting operations on row data as requested by a create index or an SQL
ORDER BY or GROUP BY statement. In general, Oracle sorting occurs under the
- SQL using the
ORDER BY clause
- SQL using the
GROUP BY clause
- When an index
- When a MERGE
SORT is invoked by the SQL optimizer because inadequate indexes exist for a
At the time a
session is established with Oracle, a private sort area is allocated in RAM
memory for use by the session for sorting. If the connection is via a dedicated
connection a Program Global Area (PGA) is allocated according to the
sort_area_size init.ora parameter. For connections via the multithreaded server,
sort space is allocated in the large_pool. Unfortunately, the amount of memory
used in sorting must be the same for all sessions, and it is not possible to add
additional sort areas for tasks that require large sort operations. Therefore,
the designer must strike a balance between allocating enough sort area to avoid
disk sorts for the large sorting tasks, keeping in mind that the extra sort area
will be allocated and not used by tasks that do not require intensive sorting.
Of course, sorts that cannot fit into the sort_area_size will be paged out into
the TEMP tablespaces for a disk sort. Disk sorts are about 14,000 times slower
than memory sorts.
As we noted, the
size of the private sort area is determined by the sort_area_size init.ora
parameter. The size for each individual sort is specified by the
sort_area_retained_size init.ora parameter. Whenever a sort cannot be completed
within the assigned space, a disk sort is invoked using the temporary tablespace
for the Oracle instance.
Disk sorts are
expensive for several reasons. First, they are extremely slow when compared to
an in-memory sort. Also, a disk sort consumes resources in the temporary
tablespace. Oracle must also allocate buffer pool blocks to hold the blocks in
the temporary tablespace. In-memory sorts are always preferable to disk sorts,
and disk sorts will surely slow down an individual task as well as impact
concurrent tasks on the Oracle instance. Also, excessive disk sorting will cause
a high value for free buffer waits, paging other tasks' data blocks out of the
Next, let's take a quick look at Oracle contention and see how the table storage
setting affect the performance of SQL UPDATE and INSERT statements.
One of the
benefits of having Oracle is that it manages all of the free space within each
tablespace. Oracle handles table and index space management for us and insulates
us from the inner workings of the Oracle tables and indexes. However,
experienced Oracle tuning professionals need to understand how Oracle manages
table extents and free data blocks. This is a very important tuning
consideration for systems that have high inserts or updates.
To be proficient
at object tuning, you need to understand the behavior of freelists and freelist
groups, and their relationship to the values of the pctfree and pctused
parameters. This knowledge is especially imperative for enterprise resource
planning (ERP) applications where poor DML table performance is often directly
related to improper table settings.
The most common
mistake for the beginner is assuming that the default Oracle parameters are
optimal for all objects. Unless disk consumption is not a concern, you must
consider the average row length and database block size when setting pctfree and
pctused for a table such that empty blocks are efficiently placed back onto the
freelists. When these settings are wrong, Oracle may populate freelists with
"dead" blocks that do not have enough room to store a row, causing significant
critical to the effective reuse of space within the Oracle tablespaces and are
directly related to the pctfree and pctused storage parameters. When the
database is directed to make blocks available as soon as possible (with a high
setting of pctused), the reuse of free space is maximized. However, there is a
direct trade-off between high performance and efficient reuse of table blocks.
When tuning Oracle tables and indexes, you need to consciously decide if you
desire high performance or efficient space reuse, and set the table parameters
accordingly. Let's take a close look at how these freelists affect the
performance of Oracle.
request is made to insert a row into a table, Oracle goes to a freelist to find
a block with enough space to accept a row. As you may know, the freelist chain
is kept in the first block of the table or index, and this block is known as the
segment header. The sole purpose of the pctfree and pctused table allocation
parameters is to control the movement of blocks to and from the freelists. While
the freelist link and unlink operations are simple Oracle functions, the
settings for freelist link (pctused) and unlink (pctfree) operations can have a
dramatic impact on the performance of Oracle.
As you may know
from DBA basics, the pctfree parameter governs freelist un-links. Setting
pctfree=10 means that every block reserves 10 percent of the space for row
expansion. The pctused parameter governs freelist re-links. Setting pctused=40
means that a block must become less than 40-percent full before being re-linked
on the table freelist.
misunderstand what happens when a block is re-added to the freelist. Once a
block is re-linked onto the freelist after a delete, it will remain on the
freelist even when the space exceeds 60 percent. Only reaching pctfree will take
the database block off of the freelist.
Summary of Table and
Index Storage Parameter Rules
rules govern the settings for the storage parameters freelists, freelist groups,
pctfree, and pctused. As you know, the value of pctused and pctfree can easily
be changed at any time with the alter table command, and the observant
DBA should be able to develop a methodology for deciding the optimal settings
for these parameters.
There is a direct
trade-off between effective space utilization and high performance, and the
table storage parameters control this trade-off:
- For efficient
space reuse A high value for pctused will effectively reuse space on data
blocks, but at the expense of additional I/O. A high pctused means that
relatively full blocks are placed on the freelist. Hence, these blocks will be
able to accept only a few rows before becoming full again, leading to more
- For high
performance A low value for pctused means that Oracle will not place a data
block onto the freelist until it is nearly empty. The block will be able to
accept many rows until it becomes full, thereby reducing I/O at insert time.
Remember that it is always faster for Oracle to extend into new blocks than to
reuse existing blocks. It takes fewer resources for Oracle to extend a table
than to manage freelists.
Let's review the
general guidelines for setting of object storage parameters:
- Always set
pctused to allow enough room to accept a new row. We never want to have free
blocks that do not have enough room to accept a row. If we do, this will cause
a slowdown since Oracle will attempt to read five "dead" free blocks before
extending the table to get an empty block.
- The presence
of migrated/chained rows in a table means that pctfree is too low or that db_block_size
is too small. In most cases within Oracle, RAW and LONG RAW columns make huge
rows that exceed the maximum block size for Oracle, making migrated/chained rows
- If a table has
simultaneous insert SQL processes, it needs to have simultaneous delete
processes. Running a single purge job will place all of the free blocks on
only one freelist, and none of the other freelists will contain any free
blocks from the purge.
- The freelist
parameter should be set to the high-water mark of updates to a table. For
example, if the customer table has up to 20 end users performing insert
operations at any time, the customer table should have freelists=20.
Note that the
freelist groups parameter only applies to Oracle Parallel Server and Real
Application Clusters. For these types of Oracle, the freelist groups should be
set the number of Oracle Parallel Server instances that access the table.
In this brief
paper there has not been enough room to completely cover all of the expert tips
that relate to Oracle tuning. For more complete information, see my Oracle Press
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.