Databases Reference
In-Depth Information
In addition, not all operations read from the database buffer cache. For example, large
full table scans are limited to a small number of buffers to avoid adversely impacting
other users by dominating the cache. If your application performs a lot of table scans,
increasing the buffer cache may not help performance because the cache will not contain
the needed data blocks. Parallel table scans completely bypass the buffer cache and pass
rows directly to the requesting user process, as do Smart Scan operations on Exadata.
As with most performance issues, your understanding of how your application is ac‐
tually using your data is the key that will help guide your database buffer cache tuning.
The shared pool
The shared pool is used at several points during the execution of every operation that
occurs in the Oracle Database. For example, the shared pool is accessed to cache the
SQL sent to the database and for the data dictionary information required to execute
the SQL. Because of its central role in database operations, a shared pool that is too small
may have a greater impact on performance than a database buffer cache that is too small.
If the requested database block isn't in the database buffer cache, Oracle will perform
an I/O to retrieve it, resulting in a one-time performance hit.
A shared pool that is too small will cause poor performance for a variety of reasons,
affecting all users. These reasons include the following:
• Not enough data dictionary information can be cached, resulting in frequent disk
access to query and update the data dictionary.
• Not enough SQL can be cached, leading to memory churn or the flushing of useful
statements to make room for incoming statements. A well-designed application
issues the same statements repeatedly. If there isn't enough room to cache all the
SQL the application uses, the same statements get parsed, cached, and flushed over
and over, wasting valuable CPU resources and adding overhead to every
transaction.
• Not enough stored procedures can be cached, leading to similar memory churn
and performance issues for the program logic stored and executed in the database.
If you are manually managing the shared pool and you've diagnosed which of these
problems is occurring, the solution is fairly simple: increase the size of the shared pool
using the SHARED_POOL_SIZE initialization parameter. For more information about
examining shared pool activity to identify problems, see the appropriate
Oracle Perfor‐
mance Tuning Guide
, as well as the other topics on this topic.
The redo log buffer
While the redo log buffer consumes a very small amount of memory in the SGA relative
to the Oracle Database buffer cache and the shared pool, it is critical for performance.
Transactions performing changes to the data in the database write their redo informa‐
tion to the redo log buffer in memory. The redo log buffer is flushed to the redo logs on
