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.