Database Reference
In-Depth Information
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_buffer integer 1703936
SYS@ORA12CR1> select 1703936/1024/1024 from dual;
1703936/1024/1024
-----------------
1.625
The smallest log buffer I can really have, regardless of my settings, is going to be 1.625MB on this system.
■
For most database applications, the default value for the
LOG_BUFFER
parameter is sufficient. If you see a large
number of waits associated with the
log buffer space
event, then consider increasing the
LOG_BUFFER
parameter.
Note
Block Buffer Cache
So far, we have looked at relatively small components of the SGA. Now we are going to look at one that is potentially
huge in size. The block buffer cache is where Oracle stores database blocks before writing them to disk and after
reading them in from disk. This is a crucial area of the SGA for us. Make it too small and our queries will take forever to
run. Make it too big and we'll starve other processes (e.g., we won't leave enough room for a dedicated server to create
its PGA, and we won't even get started).
In earlier releases of Oracle, there was a single block buffer cache, and all blocks from any segment went into this
single area. Starting with Oracle 8.0, we had three places to store cached blocks from individual segments in the SGA:
•
Default pool
: The location where all segment blocks are normally cached. This is the original—
and, previously, the only—buffer pool.
•
Keep pool
: An alternate buffer pool where by convention you assign segments that are
accessed fairly frequently, but still get aged out of the default buffer pool due to other
segments needing space.
•
Recycle pool
: An alternate buffer pool where by convention you assign large segments that
you access very randomly, and which would therefore cause excessive buffer flushing of many
blocks from many segments. There's no benefit to caching such segments because by the time
you wanted the block again, it would have been aged out of the cache. You would separate
these segments out from the segments in the default and keep pools so they would not cause
those blocks to age out of the cache.
Note that in the keep and recycle pool descriptions I used the phrase “by convention.” There is nothing in
place to ensure that you use either the keep pool or the recycle pool in the fashion described. In fact, the three pools
manage blocks in a mostly identical fashion;
they do not have radically different algorithms for aging or caching blocks
.
The goal here was to give the DBA the ability to segregate segments to hot, warm, and “do not care to cache” areas.
The theory was that objects in the default pool would be hot enough (i.e., used enough) to warrant staying in the
cache all by themselves. The cache would keep them in memory since they were very popular blocks. If you had some
