onstat -F Page Flushers (cleaners)

The onstat -F command provides information on what types of buffer pool flushing have occurred as well as the status of each of the system page cleaner threads.

Output Description

Heading Description Format See Also
Fg Writes The number of times a foreground write has occurred. This is caused from the sqlexec thread having to request a flush of the buffer pool page itself. Dec  
LRU Writes The number of times an LRU write has occurred as performed by a page cleaner thread. Dec onstat -R
Chunk Writes The number of times a chunk write has occurred as performed during a checkpoint. Dec onstat -m
address The in-memory address of the page cleaner thread. Hex  
flusher A sequential number assigned to each of the page cleaner threads. Dec  
state Describes the current state of the page cleaner thread’s activity using the following coded values:
C Chunk write
E Exiting
I Idle
L LRU queue write
Str  
data Data associated with the state of the page cleaner. If the state is ‘C’, data is the chunk number, if state is ‘L’, data is the number of the LRU queue, otherwise data is 0. Data is displayed in decimal followed by hexadecimal format. Dec Hex  

Notes

    onstat -F doesn’t print out as part of the onstat -a command.

    FG writes may occur periodically from a load and do not necessarily indicate a need for additional page cleaners.

Monitoring and Tuning

    In general, foreground writes (FG) should always be zero. A foreground write is caused when a session needs to have a page from disk placed into the buffer pool, but there are no clean/free buffers available. In this case, the sqlexec thread will pick a page off the least recently used side of an LRU queue, write it out, and mark the page as clean. This process is time consuming for a session and should be avoided.

    Whether buffer flushing occurs as part of an LRU write or a Chunk Write should be based on the nature of the system. LRU writes do not block the system, but are random in nature. Chunk Writes block the system, but are sequential in nature and tend to be more efficient for large writes. It is therefore preferable to have LRU writes occuring in a production system for normal operations and Chunk Writes for loads.

    If you are having long checkpoints, onstat -F may provide some insight. While a checkpoint is occurring, execute onstat -F -r 2. This will show what chunks the page flushers are writing out to disk every two seconds. By monitoring the data column, it is possible to determine which chunks are taking the longest to write. If one or more chunks continually have long chunk writes, relative to the rest of the system, the tables within it might be good candidates for redistribution.

    Under normal circumstances, there will be one flusher assigned to each chunk during a checkpoint.