Monitor buffers

The statistical information includes the percentage of data writes that are cached to buffers and the number of times that threads were required wait to obtain a buffer. The percentage of writes that are cached is an important measure of performance. The number of waits for buffers gives a measure of system concurrency.

Information about specific buffers includes a listing of all the buffers in shared memory that are held by a thread. You can use this information to track the status of a particular buffer. For example, you can determine whether another thread is waiting for the buffer.

You can obtain statistics that relate to buffer availability and information about the buffers in each LRU queue. The statistical information includes the number of times that the database server attempted to exceed the maximum number of buffers and the number of writes to disk (categorized by the event that caused the buffers to flush). These statistics help you determine if the number of buffers is appropriate. Information about the buffers in each LRU queue consists of the length of the queue and the percentage of the buffers in the queue that were modified.

You can obtain information about buffer pool activity from the onstat utility, the sysprofile SMI table.

onstat commands to monitor buffers

You can use the following onstat commands to monitor buffers:

onstat -g buf

Run the onstat -g buf command to obtain statistics about how active and efficient each buffer is. The following types of statistics are shown:

• Page reads and writes
• Caching percentages
• Waits for buffers
• Flushes
• Extensions of the buffer pool
• Buffer pool segments
• Fast cache

onstat -B

Run the onstat -B command to obtain information about all of the buffers that are not on the free-list, including:

• The shared memory address of the buffer
• The address of the thread that currently holds the buffer
• The address of the first thread that is waiting for each buffer
• Information about buffer pools

onstat -b

Run the onstat -b command to obtain the following information about each buffer:

• Address of each buffer that is currently held by a thread
• Page numbers for the page that is held in the buffer
• Type of page that is held in the buffer (for example, data page, tblspace page, and so on)
• Type of lock that is placed on the buffer (exclusive or shared)
• Address of the thread that is holding the buffer
• Address of the first thread that is waiting for each buffer
• Information about buffer pools

You can compare the addresses of the user threads to the addresses that are shown in the onstat -u output to obtain the session ID number.

onstat -X

Run the onstat -X command to obtain the same information as for onstat -b, along with the complete list of all threads that are waiting for buffers, not just the first waiting thread.

onstat -R

Run the onstat -R command to show information about buffer pools, the number of buffers in each LRU queue, and the number and percentage of the buffers that are modified or free.

onstat -F

Run the onstat-F command to obtain a count by write type of the writes that are performed and the following information about the page cleaners:

• Page-cleaner number
• Page-cleaner shared-memory address
• Current state of the page cleaner
• LRU queue to which the page cleaner was assigned

The sysprofile SMI table