Write-to-table and file event monitor buffering

For some write-to-table and file event monitors, the event monitor stores output in a buffer before writing it to a file or table.

Table 1 shows which event monitors use such output buffers.

Table 1. Event monitors and output buffers
Event monitor type	Writes output to buffers before writing to disk?
Activities	No
Bufferpools	Yes
Change history	No
Connections	Yes
Database	Yes
Deadlocks (all versions)	Yes
Locking	No
Package cache	No
Statements	Yes
Statistics	Yes
Tablespaces	Yes
Tables	Yes
Transactions	Yes
Unit of work	No

Event monitors that do not use buffers use a newer, faster mechanism for writing output to disk, eliminating the need for buffers.

For those event monitors that use buffers, records are written to disk automatically when a buffer is full. Therefore, you can improve monitoring performance for event monitors with high amounts of throughput by specifying larger buffers to reduce the number of disk accesses. To force an event monitor to flush its buffers, you must either deactivate it or empty the buffers by using the FLUSH EVENT MONITOR statement.

Event monitors that use buffers let you specify whether the event monitor output is to be blocked or non-blocked. A blocked event monitor suspends the database process that is sending monitor data when both of its buffers are full. This is to ensure that no event records are discarded while the blocked event monitor is active. The suspended database process and consequently, any dependent database processes cannot run until a buffer has been written. This can introduce a significant performance consumption, depending on the type of workload and the speed of the I/O device. Event monitors are blocked by default.

A non-blocked event monitor discards monitor data coming from agents when data is coming faster than the event monitor can write the data. This prevents event monitoring from becoming a performance burden on other database activities.

An event monitor that has discarded event records generates an overflow event. It specifies the start and stop time during which the monitor was discarding events and the number of events that were discarded during that period. It is possible for an event monitor to terminate or be deactivated with a pending overflow to report. If this occurs, the following message is written to the admin log:

DIA2503I Event Monitor monitor-name had a pending overflow record
when it was deactivated.

Loss of event monitoring data can also occur for individual event records. If the length of an event record exceeds the event buffer size, the data that does not fit in the buffer is truncated. For example, this situation might occur if you are capturing the stmt_text monitor element and applications attached to the database being monitored issue lengthy SQL statements. If you must capture all of the event record information, specify larger buffers. Keep in mind that larger buffers result in less frequent writes to file or table.