Databases Reference
In-Depth Information
You can mark a table or index for caching in a specific buffer pool. This helps to keep
more desirable objects in the cache and avoids the “churn” of all objects fighting for
space in one central cache. Of course, to use these features properly you must be aware
of the access patterns for the various objects used by your application.
Oracle Database 10
g
simplified management of buffer cache size by introducing a new
dynamic parameter, DB_CACHE_SIZE. This parameter can be used to specify cache
memory size and replaced the DB_BLOCK_BUFFERS parameter present in previous
Oracle releases.
DB_CACHE_SIZE is automatically sized if MEMORY_TARGET or SGA_TARGET is
set. Other initialization parameters include DB_KEEP_CACHE_SIZE and DB_RECY‐
CLE_CACHE_SIZE and these must be manually sized if used.
Shared pool
The shared pool caches various constructs that can be shared among users. For example,
SQL queries and query fragments issued by users and results are cached so they can be
reused if the same statement is submitted again. PL/SQL functions are also loaded into
the shared pool for execution and the functions and results are cached, again using an
LRU algorithm. As of Oracle Database 11
g
, a PL/SQL function can be marked in such
a way that its result will be cached to allow lookup rather than recalculation when it is
called again using the same parameters. The shared pool is also used for caching infor‐
mation from the Oracle data dictionary, which is the metadata that describes the struc‐
ture and content of the database itself.
You can specify a SHARED_POOL_SIZE initialization parameter, or it will be auto‐
matically sized if MEMORY_TARGET or SGA_TARGET is specified. Note that prior
to Oracle Database 10
g
, “out of memory” errors were possible if the shared pool was
undersized, but current Oracle Database releases now can leverage automatic shared
memory tuning.
Redo log buffer
The redo log buffer caches redo information until it is written to the physical redo logfiles
stored on a disk. This buffer also improves performance. Oracle caches the redo until
it can be written to a disk at a more optimal time, which avoids the overhead of constantly
writing the redo logs to disk.
Other pools in the SGA
The SGA includes several other pools:
Large pool
Provides memory allocation for various I/O server processes, backup, and recovery,
and provides session memory where shared servers and Oracle XA for transaction
processing are used.
