<P>The library cache contains the shared SQL and PL/SQL areas. You can improve performance
by increasing the cache-hit rate in the library cache and by speeding access to the
library cache by holding infrequently used SQL statements in cache longer.</P>
<P>The <TT>V$LIBRARYCACHE</TT> table contains statistics about how well you are using
the library cache. The important columns to view in this table are <TT>PINS</TT>
and <TT>RELOADS</TT>:

<UL>
	<LI><TT>PINS</TT>--The number of times the item in the library cache was executed.
</UL>


<UL>
	<LI><TT>RELOADS</TT>--The number of times the library cache missed and the library
	object was reloaded.
</UL>

<P>A small number of reloads relative to the number of executions indicates a high
cache-hit rate.</P>
<P><FONT COLOR="#000077"><B>The Data-Dictionary Cache</B></FONT></P>
<P>The data-dictionary cache contains a set of tables and views that Oracle uses
as a reference to the database. Here Oracle stores information about the logical
and physical structure of the database.</P>
<P>To check the efficiency of the data-dictionary cache, check the cache-hit rate.
Statistics for the data-dictionary cache are stored in the dynamic performance table
<TT>V$ROWCACHE</TT> (the data-dictionary cache is sometimes known as the <I>row cache</I>).
The important columns to view in this table are <TT>GETS</TT> and <TT>GETMISSES</TT>:

<UL>
	<LI><TT>GETS</TT>--The total number of requests for the particular item.
</UL>


<UL>
	<LI><TT>GETMISSES</TT>--The total number of requests resulting in cache misses.
</UL>

<P>To determine your cache-hit ratio, use the following SQL statement or look at
<TT>PINHITRATIO</TT> in the output of <TT>UTLESTAT</TT>:</P>
<PRE><FONT COLOR="#0066FF">SELECT SUM(reloads) &quot;Cache Misses&quot;,
SUM(pints) &quot;Executions&quot;,
100 * (SUM(reloads) / SUM(pins) ) &quot;Cache Miss Percent&quot;
FROMB V$librarycache;
</FONT></PRE>
<P>This will show you the cache-miss percent:</P>
<PRE><FONT COLOR="#0066FF">Cache Hit % = 100% - Cache Miss %
</FONT></PRE>
<P>A low number of cache misses is expected, especially during startup when the cache
has not been populated.</P>
<P><FONT COLOR="#000077"><B>Shared Session Information</B></FONT></P>
<P>In a multithreaded server configuration, the session information is also stored
in the shared pool. This information includes the private SQL areas as well as sort
areas. Ensure that you do not run out of memory for this shared session information.</P>
<P>To determine whether you need to increase space for these shared sessions, you
can extract the sum of memory allocated for all sessions and the maximum amount of
memory allocated for sessions from the dynamic performance table <TT>V$SESSTAT</TT>.
If the maximum amount of memory used is high, it might be necessary to increase the
size of the shared pool. Because the shared pool is used for other functions as well,
such as the library cache and the data-dictionary cache, it is a good idea to increase
the size of the shared pool to accommodate this additional memory usage. If you have
enough memory in your system, increase the shared pool by the maximum amount of memory
used by the shared server processes; if you have a limited amount of memory, use
the sum of memory allocated to sessions that was obtained when an average number
of users was connected and running as a basis for the amount of memory by which to
increase the shared pool.</P>
<P><FONT COLOR="#000077"><B>Database Block Buffer Cache</B></FONT></P>
<P>The most important Oracle cache in the system is probably the buffer cache. The
buffer cache makes up the majority of the Oracle SGA and is used for every query
and update in the system. The statistics for the buffer cache are kept in the dynamic
performance table <TT>V$SYSSTAT</TT> and are included in the output from <TT>UTLESTAT</TT>.
The important columns to view in this table are listed here:

<UL>
	<LI><TT>PHYSICAL READS</TT>--This indicates the total number of requests that result
	in a disk access. This is a cache miss.
	<P>
	<LI><TT>DB BLOCK GET</TT>--This indicates the number of requests for blocks in current
	mode. Buffers are typically retrieved in current mode for <TT>INSERT</TT>, <TT>UPDATE</TT>,
	and <TT>DELETE</TT> statements.
	<P>
	<LI><TT>CONSISTENT GET</TT>--This indicates the number of requests for blocks in
	consistent mode. Buffers are typically retrieved in consistent mode for queries.
</UL>

<P>The sum of the values in <TT>DB BLOCK GETS</TT> and <TT>CONSISTENT GETS</TT> represents
the total number of requests for data. The cache-hit ratio is determined using this
formula:</P>
<P>Cache-hit ratio = 1 - (<TT>PHYSICAL READS</TT> / (<TT>DB BLOCK GETS</TT> + <TT>CONSISTENT
GETS</TT>))</P>
<P>The block buffers are the most important area of the SGA and must be tuned because
of the large effect they have on the system and the number of resources they consume.
<H4><FONT COLOR="#000077"><B>Performance Enhancements</B></FONT></H4>
<P>I prefer to separate the performance-enhancement options from the general tuning
of Oracle. Performance enhancements tend to be things that might or might not help
your configuration and application; in fact, they might hurt. On the other hand,
tuning parameters always help, based on the correct interpretation of Oracle statistics.
The following sections review a few of the enhancements you have seen throughout
the book.</P>
<P><FONT COLOR="#000077"><B>Block Size</B></FONT></P>
<P>Depending on your configuration and data-access patterns, you might be able to
benefit from using a larger block size. With a larger block size, you get the benefit
of less wasted space and more efficient I/O in certain conditions. Here are a few
guidelines that might help you decide whether changing the size of <TT>DB_BLOCK_SIZE</TT>
can help you:

<UL>
	<LI>OLTP systems benefit from smaller blocks--If your application is OLTP in nature,
	you will not benefit from larger blocks. OLTP data typically fits well in the default
	block size; larger blocks unnecessarily eject blocks from the SGA.
	<P>
	<LI>DSS systems benefit from larger blocks--In the DSS system in which table scans
	are common, retrieving more data at a time results in a performance increase.
	<P>
	<LI>Larger databases benefit from larger blocks--Larger databases see a space benefit
	from less wastage per block.
	<P>
	<LI>Databases with large rows benefit from larger blocks--If your rows are extremely
	large (as is sometimes the case with images or text) and don't fit in the default
	block, you will see a definite benefit from a larger block size.
</UL>

<P>Because unnecessarily changing the block size increases I/O overhead, this change
<I>does</I> carry some risk. Change the block size with caution.</P>
<P><FONT COLOR="#000077"><B>Clusters</B></FONT></P>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B>A <I>cluster</I>, sometimes
called an <I>index cluster</I>, is an optional method of storing tables in an Oracle
database. Within a cluster, multiple related tables are stored together to improve
access time to the related items. Clusters are useful in cases where related data
is often accessed together. The existence of a cluster is transparent to users and
applications; the cluster affects only how data is stored.</P>
<P>A cluster can be useful for tables in which data is primarily accessed together
in a join. In such situations, the reduced I/O needed to bring the additional data
into the SGA and the fact that the data is already cached can be a big advantage.
However, for situations in which the tables have a large number of <TT>INSERT</TT>
statements or the data is not frequently accessed together, a cluster is not useful
and should not be used. Do not cluster tables if full-table scans are often performed
on only one of the tables in the cluster. The additional space required by the cluster
and the additional I/O reduces performance.</P>
<P><FONT COLOR="#000077"><B>Reduction of Fragmentation</B></FONT></P>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B><I>Fragmentation</I> occurs
when pieces of the database are no longer contiguous. Fragmentation can consist of
<I>disk fragmentation</I> or <I>tablespace fragmentation</I>. Both of these types
of fragmentation usually affect performance. Disk fragmentation usually causes multiple
I/Os to occur when one I/O would have been sufficient (for example, with chained
or migrated rows). Disk fragmentation can occur when the extents that comprise the
database segments are noncontiguous; this is sometimes caused by excessive dynamic
growth.</P>
<P>Tablespace fragmentation is caused by the dropping and creating of segments. This
can produce large free areas between segments, which results in the inefficient use
of space and excessive disk seeks over the empty areas. Tablespace fragmentation
can also prevent Oracle from taking advantage of multiblock reads.</P>
<P>One way to eliminate fragmentation is to export the table or tablespace data,
remove and re-create the table or tablespace, and import the data. By eliminating
fragmentation, you can reduce excessive I/Os and CPU usage, streamlining data access.
Any overhead and unnecessary I/Os you can reduce will improve system performance.</P>
<P><FONT COLOR="#000077"><B>Hash Clusters</B></FONT></P>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B>A <I>hash cluster</I> is
similar to a cluster except that it uses a hash function rather than an index to
reference the cluster key. A hash cluster stores the data based on the result of
a hash function. The hash function is a numeric function that determines the data
block in the cluster based on the value of the cluster key. To achieve good performance
from a hash cluster, you must meet the following criteria:

<UL>
	<LI>The cluster key value must be unique.
	<P>
	<LI>The majority of queries must be equality queries on the cluster key.
	<P>
	<LI>The size of the table must be static (little growth occurs).
	<P>
	<LI>The value of the cluster key must not change.
</UL>

<P>If you can take advantage of hashing by meeting this strict criteria, you will
see good performance. Hashing is extremely efficient under the right conditions;
however, having a hash cluster under the wrong conditions can degrade performance.</P>
<P><FONT COLOR="#000077"><B>Indexes</B></FONT></P>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B>An <I>index</I>, like the
index in this book, is an optional structure designed to help you achieve faster
access to your data. When optimally configured and used, indexes can significantly
reduce I/O to the datafiles and greatly improve performance. You must first decide
whether an index is appropriate for the data and access patterns in your particular
system. Having decided to use an index, you must decide which columns to index. Indexing
appropriately can greatly improve performance by reducing I/Os and speeding access
times.</P>
<P>Careful planning and periodic testing with SQL Trace can lead to the effective
use of indexes, with optimal performance being the outcome. Even though indexes have
been built on tables in your database, they might not necessarily be used. If the
SQL statement is not correctly formed, the index might be bypassed and a full-table
scan might be the result. This is why the application developer and the system designer
must communicate constantly throughout the development and deployment cycle. The
application should be tested and the SQL statements analyzed to ensure that indexes
are being used where intended. Indexes are covered in detail on Day 13, &quot;Using
Indexes and Sequences.&quot;</P>
<P><FONT COLOR="#000077"><B>Multiblock Reads</B></FONT></P>
<P>When performing table scans, Oracle can read more than one block at a time, thus
speeding I/Os. Consequently, a larger chunk of data can be read from the disk, thus
eliminating some disk seeks. The reduction of disk seeks and the reading of larger
blocks reduces both I/O and CPU overhead.</P>
<P>The amount of data read in a multiblock read is specified by the Oracle initialization
parameter <TT>DB_FILE_MULTIBLOCK_READ_COUNT</TT>. The value for this parameter should
always be set high because there is rarely any disadvantage in doing so. The size
of the individual I/O requests depends on <TT>DB_FILE_MULTIBLOCK_READ_COUNT</TT>
and <TT>DB_BLOCK_SIZE</TT>. A good value for multiblock reads is 64KB.</P>
<P><FONT COLOR="#000077"><B>Multiblock Writes</B></FONT></P>
<P>Multiblock writes are similar to multiblock reads and have many of the same requirements.
Multiblock writes are available through the direct path loader as well as through
sorts and index creations. As with multiblock reads, the multiblock writes reduce
I/O and CPU overhead by writing multiple database blocks in one larger I/O operation.</P>
<P>The amount of data written in a multiblock write is specified by the Oracle initialization
parameter <TT>DB_FILE_MULTIBLOCK_WRITE_COUNT</TT>. The size of the individual I/O
requests depends on both <TT>DB_FILE_MULTIBLOCK_WRITE_COUNT</TT> and <TT>DB_BLOCK_SIZE</TT>.
As with multiblock reads, a good value is 64KB.</P>
<P><FONT COLOR="#000077"><B>The Oracle Parallel Query Option</B></FONT></P>
<P>The Oracle Parallel Query option makes it possible for some Oracle functions to
be processed by multiple server processes. The functions affected are queries, index
creation, data loading, and recovery. For each of these functions, the general principle
is the same: Keep processing while Oracle waits for I/O.</P>
<P>For most queries, the time spent waiting for the data to be retrieved from disk
usually overshadows the amount of time actually spent processing the results. With
the Parallel Query option, you can compensate for this wasted