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. Event monitors that do not use buffers are always blocked. A blocked event monitor suspends the database process that is sending monitor data when both of the buffers are full or when there are no available event monitor records. 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 significant performance delays, depending on the type of workload and the speed of the I/O device. Event monitors are blocked by default.

Note: Blocked write-to-table event monitors must be explicitly disabled before issuing the SET WRITE SUSPEND command and re-enabled when write operations are resumed. If a blocked event monitor remains active while write operations are suspended, delays can occur because the event monitor cannot write records to disk. For example, a new connection attempt is delayed if a blocked write-to-table connection event monitor is left active under the following conditions:
  • While write operations are suspended.
  • While the volume of incoming connections is enough to fill all available space in the event monitor buffers.

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.