Keeping your Oracle database tuned for peak
performance requires an understanding of a number of issues. In my previous
article, I explained how to tune the server and network.
Once you've addressed
those tasks, the next place to look is within the Oracle instance itself. An
Oracle instance consists of the memory region of the database, called the System
Global Area or SGA, and the Oracle background processes. Once the instance has
been tuned, we look at tuning individual objects (tables and indexes) within the
database, and finally we tune the SQL statements that are run against the
database.
If you haven't read the first two articles in this
series, you can still catch up. The first article, "Oracle
tuning: Start by looking at the big picture," provides an
overview of Oracle tuning. The second, "Oracle
tuning: Monitoring hardware with UNIX tools," looks at tuning
the database server.
Oracle initialization parameters
This instance-tuning phase of Oracle tuning examines the overall database and
the instance-wide parameters that affect performance.
With many hundreds of Oracle initialization parameters, it's important for the
database administrator (DBA) to focus on those parameters that have the most
impact on the performance of the Oracle database. Oracle has a huge number of
parameters in the Oracle initialization files (init.ora) that control the
overall configuration of the Oracle instance. While there are dozens of init.ora
parameters that affect performance, these are some of the most important:
- shared_pool_size—The memory region allocated for the library
cache and internal control structures
- db_block_size—The size of each data block
- db_cache_size—The number of data buffers to allocate for the
instance
- sort_area_size—The amount of RAM reserved for each user to
perform sorting operations
- optimizer_mode—The default mode in which all SQL statements will
be optimized for an execution plan
- db_file_multiblock_read_count—The parameter that controls the
number of blocks that are read asynchronously in full-table scan operations
We have to remember that all Oracle instances are
generally constrained by I/O operations. I/O is the single most
dramatic component of Oracle performance, and any initialization
parameters that can be used in order to reduce I/O will improve the
performance of the database.
For example, the db_block_size and
db_cache_size initialization parameters are very important in terms
of reducing I/O. As a general rule, the larger the Oracle block size,
the more the database can read in a single disk I/O and the less I/O
contention the system will have.
- Shared pool delays—Delays in the shared pool are most commonly
caused by large amounts of nonreusable SQL in the library cache. When a new
SQL statement is parsed, Oracle will check in the library cache to see if
the SQL statement has already been processed by an earlier task. This
checking can slow down SQL possessing, especially for a database whose SQL
is not reusable.
- Buffer busy waits—Buffer busy waits occur when a data block is in
the buffer but can't be accessed because another task has a lock on the data
block. This is most frequently caused by concurrent insert or update
activity against a table that does not have multiple freelists defined.
- Buffer hit ratio—This metric is a measure of the effectiveness of
the Oracle data block buffer. The higher the buffer hit ratio, the more
frequently Oracle found a data block in memory and avoided an I/O. 2015 Update: Since the days of Oracle 7
when Oracle Corporation recommended keeping the buffer cache hit ratio above
a fixed threshold, people are now recognizing the limitations of ratio
analysis. Please read: Is the
Oracle buffer hit ratio a useless metric for monitoring and tuning?
When tuning an Oracle instance, most DBAs begin by taking overall views of the
performance of the database. In Oracle8 through Oracle9i, most DBAs use
the Oracle STATSPACK utility to collect, store, and report on performance
information.
For details about capturing and plotting database STATSPACK information, see my
article "Oracle
Database Trend Analysis Using STATSPACK" in Oracle magazine.
Now that we have a high-level understanding of the main areas of instance
tuning, let's examine the tuning of the Oracle data buffers.
Data buffer tuning
The tuning of the Oracle data buffers is an important aspect of Oracle tuning
because the data buffer has a direct impact on disk I/O. Oracle provides an
in-memory cache for data blocks that have been retrieved by earlier SQL
requests. When a request is made to Oracle to retrieve data, Oracle will first
check the internal memory structures to see if the data is already in the
buffer. If the block is in memory, Oracle reads the data from the RAM buffer and
avoids doing unnecessary disk I/O.
It would be ideal if you could create one buffer for each database page,
ensuring that Oracle would read each block only once. With Oracle8i and
the very large memory (VLM) features, it's now possible to specify a data buffer
that's large enough to hold an entire multigigabyte database, but most large
databases do not have enough RAM to allow for the full caching of data pages.In Oracle8i, we have three buffer pools for holding data blocks:
- DEFAULT pool—Used for all data blocks that are not marked for the
KEEP or RECYCLE pools
- KEEP pool—Reserved for tables and indexes that are used very
frequently
- RECYCLE pool—Reserved for data blocks that are read when
performing large, full-table scans
Because most Oracle databases do not have enough RAM to cache the whole
database, the data buffers manage the data blocks to reduce disk I/O. Oracle
utilizes a least recently used algorithm to determine which database pages are
to be flushed from memory.
As I mentioned earlier, the measure of the effectiveness of the data buffer is
called the data buffer hit ratio. This ratio computes the likelihood that a data
block is present in the data buffer when the block is requested. The more data
blocks that are found in the buffer, the higher the data buffer hit ratio.
Oracle recommends that all databases exhibit a data buffer hit ratio of at least
90 percent.
It's important to note that the data buffer hit ratio is an elapsed-time
measurement. If you use the Oracle STATSPACK utility to compute the data
buffer hit ratio over short intervals (every five minutes), you will see
that the buffer hit ratio varies from 50 percent to 100 percent,
depending upon the type of SQL requests that are being processed. To see
the average buffer hit ratio since the database was started, you can run
the Oracle dictionary queries shown below
This will show you the average data buffer hit ratio since your database was
started.
Many Oracle shops will keep their buffer hit ratio information in the STATSPACK
tables and plot it to show trends in the effectiveness of the data buffer to
reduce I/O. Below is an example of a plot of STATSPACK data for the
data buffer hit ratio.
Multiblock reads and Oracle
The db_block_size parameter can have a dramatic impact on system performance. In
general, db_block_size should never be set to less than 8K, regardless of the
type of application. Even online transaction processing systems (OLTP) will
benefit from using 8K blocks, while systems that perform many full-table scans
will benefit from even larger block sizes. Depending on the operating system,
Oracle can support up to 16K block sizes. Systems that perform full-table scans
can benefit from this approach.
In addition, note the relationship between
db_block_size and the
db_file_multiblock_read_count parameter. Note that the
db_file_multiblock_read_count has been automated in 10gr2 and
beyond.
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.
At the physical level in UNIX, Oracle always
reads in a minimum of 64K blocks. Therefore, the values of
db_file_multiblock_read_count and
db_block_size should be set such that their product
is 64K. For example, the following table shows the recommended settings based
upon the
db_block_size:
| Blocksize |
db_file_multiblock_read_count |
| 4K |
16 |
| 8K |
8 |
| 16K |
4 |
| 32K |
2 |
Note that the block size for an Oracle database can be changed when the database
is reorganized. Eventually, all Oracle databases should be compressed
(export/import) to reduce fragmentation. At that point, it becomes trivial to
alter the value of
db_block_size.
Because the
db_cache_size is expressed in the number of blocks,
increasing the size of
db_block_size will increase the size of the Oracle SGA.
The values of
db_block_size are multiplied by the value of
db_cache_size to
determine the total amount of memory to allocate for Oracle's I/O buffers.
and include the URL for the page.
