Databases Reference
In-Depth Information
• Buffering of redo log entries in the redo log buffer before they're written to disk
In versions prior to Oracle 9
i
, the amount of memory allocated to each of these areas
within the SGA was determined at instance startup using initialization parameters and
could not be altered without restarting the instance. The majority of tuning efforts
focused on the database buffer cache and the shared pool.
Automatic sizing for the SGA
Oracle Database 10
g
eliminated manual tuning of SGA pools with automatic sizing for
the SGA. Using automatic shared memory management, the database automatically
allocates memory for the following SGA pools: database buffer cache, shared pool, large
pool, Java pool, and Streams pool. You have to specify only the total amount of memory
required by setting the SGA_TARGET initialization parameter.
Since Oracle Database 10
g
, the database proactively monitors the memory requirements
for each pool and dynamically reallocates memory when appropriate. You can also
specify the minimum amount of memory for any of the SGA pools while using auto‐
matic SGA sizing using the following initialization parameters: DB_CACHE_SIZE,
SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE, and
STREAMS_POOL_SIZE. A few of the SGA pools are still manually managed by spec‐
ifying parameters such as LOG_BUFFER, DB_KEEP_CACHE_SIZE, and DB_RECY‐
CLE_CACHE_SIZE.
The database buffer cache
If you decide to disable SGA_TARGET by setting it to 0, you will need to manually set
initialization parameters for the memory pools. For the database buffer cache, you
would assess the percentage of the database blocks requested by users read from the
cache versus from the disk. This percentage is termed the
hit ratio
. If query response
times are too high and the hit ratio is lower than 90 percent (as a rule of thumb), in‐
creasing the value of the initialization parameter DB_CACHE_SIZE can improve
performance.
You can use Oracle Enterprise Manager to get information about the
cache hit ratio.
It is tempting to assume that continually increasing the size of the database buffer cache
will translate into better performance. However, this is true only if the database blocks
in the cache are actually being reused. Most OLTP systems have a relatively small set of
core tables that are heavily used (for example, lookup tables for things such as valid
codes). The rest of the I/O tends to be random, accessing a row or two in various database
blocks in the course of the transaction. Because of this, having a larger buffer cache may
not contribute to performance since there isn't much reuse of data blocks occurring.
