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.
||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.
||The number of times an LRU write has occurred as performed by a page cleaner thread.
||The number of times a chunk write has occurred as performed during a checkpoint.
||The in-memory address of the page cleaner thread.
||A sequential number assigned to each of the page cleaner threads.
||Describes the current state of the page cleaner thread’s activity using the following coded values:
C Chunk write
L LRU queue write
||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.
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.