Monitoring the Data Buffer (Oracle)

The database buffer cache (also known as the data buffer or Oracle data buffer) is the area of the System Global Area (SGA) used to hold copies of data blocks read from the disk. Oracle user processes cannot read data directly from data files, which is why all data must first be read into this buffer cache.

When a user process requests a data block which is already in the data buffer, it can be read without having to access the disk again (providing the block has not been changed since it was last read into the buffer). This saves considerable processing time. In this situation, the user process has made a "hit" on that data block. When a user process requests a data block which is not in the data buffer, this is called a "miss". The relationship between hits and misses is known as the "hit ratio" Hit ratio can also be thought of as the "quality" of the database buffer cache.

Choose Tools → Administration → Computing Center → Management System → Control → Performance Menu → Database → Activity.

Alternatively, use transaction code ST04.

The following data is displayed:

The following data buffer information is displayed in the section Data buffer:

• The size of this data buffer is 160 MB
• The overall quality of the data buffer is 99%
• There have been 1,090,831,434 total Oracle blocks read from the data buffer since database instance startup
• Of these total reads, 9,247,979 reads have resulted in blocks being physically read from disk
• 1,269,110 Oracle blocks have been written to disk by the Database Writer process
• There was a total of 34,150 waits when accessing blocks of various classes in the data buffer
• The total wait time was 720.880 milliseconds

Data Buffer Size

Data buffer size is determined by the product of the block size (DB_BLOCK_SIZE (Oracle)) and the number of database block buffers specified in the init<SID>.ora parameter file (DB_BLOCK_BUFFERS (Oracle)). SAP uses a default db_block_size of 8192 Bytes for most Oracle databases. Once the database is created, this value cannot be changed. The value for db_block_buffers can however be changed as required.

SAP recommends that you maintain a data buffer quality of at least 97% on a production SAP system.

Statistics for read and write operations let you quickly determine the level of activity of a database since instance startup. If the number of physical writes is on the same scale as the number of physical reads, you should also monitor the activities of the database writer, the rollback activities and the redo log activity. This situation may occur particularly in online transaction environments when there are many updates of individual tables lines.

A wait situation in a buffer occurs when an Oracle process attempts to access a block that is still in an inconsistent status. The number of wait situations displayed on the main screen is the average number for all Oracle block classes. There are a number of Oracle block classes that may play a part in the occurrence of wait situations, but only four are commonly found when monitoring the SAP system. These are: data block, segment header, undo header and undo block.

If the total number of wait situations exceeds 5% of the total number of reads, you should analyze the situation more closely. In the Detail Analysis Menu (Oracle), choose the Buffer Busy Waits (Oracle).pushbutton. This gives you a breakdown of wait situations.

If the number of waits on any one of the block classes specified exceeds 1% of the reads, this might indicate excessive contention for this class.

Waits on the undo header and undo block classes can be reduced by adding more rollback segments to the database. Waits for data blocks may be due to the data buffer size not being large enough (check quality ratio above). Waits on segment headers often indicate contention for freelists. For more information, refer to the relevant Oracle documentation.