| Lesson 6 | Predict and maximize memory |
| Objective | Predict the amount of spare memory. |
Predict Amount of Spare Memory
Today, many databases reside alone on a host. When this is the case, you can predict the amount of "spare" memory and run your Oracle SGA up to that amount. For example, assume you have a host machine with 350MB of available memory. The UNIX kernel consumes 50MB, leaving 300MB available for your Oracle database. Each online user will need to allocate a PGA when accessing the application, and the largest share of the PGA is determined by the value of the sort_area_size init.ora parameter. Therefore, assuming that you have a sort_area_size of 20MB and 10 online users, you can assume that about 200MB of real memory must be reserved for end user sessions, leaving 100MB for the Oracle SGA[1].
Decreasing SGA size
In many cases, you will see conditions where memory can be subtracted from the SGA without causing any serious performance hits.
Oracle provides the X$KCBCBH table for this purpose, and you can query this table to track the number of buffer misses that would occur if the SGA was decreased in size, as shown below.
REM lessbuff.sql - predicts losses from subtracting db_block_buffer values
SET LINESIZE 100;
SET PAGES 999;
COLUMN "Additional Cache Misses" FORMAT 999,999,999;
COLUMN "Interval" FORMAT a20;
SELECT 250*TRUNC(indx/250)+1
||' To '||250*(TRUNC(indx/250)+1) "Interval",
SUM(count) "Additional Cache Misses"
FROM X$KCBCBH
WHERE indx > 0
GROUP BY TRUNC(indx/250);
The following diagram shows an example and explanations of the output from querying the X$KCBCBH table.
Fewer buffers in Oracle
- As you can see, this database has some shared information, with nearly four million cache hits in the first 250 buffer blocks.
- From 250 on up, you can see a slowly decreasing downward trend, indicating that this application is doing some full-table scans or is not referencing a lot of common information.
Because buffer busy waits are due to contention between particular blocks, there is nothing you can do until you know which blocks are in conflict and why the conflicts are occurring. Tuning therefore involves identifying and eliminating the cause of the block contention.
Controlling Buffer block size
For more sophisticated databases, you can control not only the number of buffer blocks but also the block size for each buffer.On an IBM mainframe, you might want to make the buffer blocks very large so that you can minimize I/O contention. For example, an I/O for 32,000 bytes is not a great deal more expensive than an I/O for 12,000 bytes.
A database designer might choose to make the buffer blocks large to minimize I/O if the application "clusters" records on a database page. If a customer record is only 100 bytes, you will not gain by retrieving 32,000 bytes to get the 100 bytes that you need. However, if your cluster orders are physically near the customer (i.e., on the same database page), and if I/O usually proceeds from customer to order, you will not need further I/O to retrieve orders for the customer.
A database designer might choose to make the buffer blocks large to minimize I/O if the application "clusters" records on a database page. If a customer record is only 100 bytes, you will not gain by retrieving 32,000 bytes to get the 100 bytes that you need. However, if your cluster orders are physically near the customer (i.e., on the same database page), and if I/O usually proceeds from customer to order, you will not need further I/O to retrieve orders for the customer.
Oracle data buffer cache
The ability to compute the optimal size of the data buffers is a critical task for large databases.
As databases grow to billions of bytes, it becomes economically impractical to cache the entire database in RAM.
The Oracle consultant must find the point of diminishing marginal returns for the addition of RAM resources.
The ability to do this can save the company hundreds of thousands of dollars in RAM expenses.
Oracle9i introduces a new view, v$db_cache_advice, that can predict the benefit of additional data buffers in the data buffer cache.
Oracle9i introduces a new view, v$db_cache_advice, that can predict the benefit of additional data buffers in the data buffer cache.
Functionality behind Oracle data buffer cache
With the data buffer set to a very small size, a small increase to the size of the RAM data buffers results in a large reduction in Disk I/O:
Furthermore, the high reduction in Disk I/O does not continue forever. As the RAM size approaches the database size, the marginal reduction in Disk I/O is smaller because all databases have infrequently accessed data.
As a general rule, all available memory on the host should be tuned, and RAM resources should be given to db_cache_size up to a point of diminishing returns. There is a point where the addition of buffer blocks will not significantly improve the buffer hit ratio, and this gives the Oracle DBA the ability to find the optimal amount of buffers. In the next lesson, you will examine the multiple data buffers.
Furthermore, the high reduction in Disk I/O does not continue forever. As the RAM size approaches the database size, the marginal reduction in Disk I/O is smaller because all databases have infrequently accessed data.
As a general rule, all available memory on the host should be tuned, and RAM resources should be given to db_cache_size up to a point of diminishing returns. There is a point where the addition of buffer blocks will not significantly improve the buffer hit ratio, and this gives the Oracle DBA the ability to find the optimal amount of buffers. In the next lesson, you will examine the multiple data buffers.
Oracle Data Buffer - Quiz
Before you move on to the next lesson, click the Quiz link below to answer some questions with respect to adding data buffers.
Oracle Data Buffer - Quiz
Oracle Data Buffer - Quiz
[1](SGA)System Global Area: The System Global Area (SGA) is a group of shared memory areas that are dedicated to an Oracle instance, where the instance is your database programs and RAM.