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.
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 |
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.
- 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.
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.