System monitor switches
System monitor switches control how snapshot monitors and some event monitors collect data.
The snapshot monitor and some event monitors report data collected by the system monitor. Collecting system monitor data introduces extra processing for the database manager. For example, in order to calculate the execution time of SQL statements, the database manager must make calls to the operating system to obtain timestamps before and after the execution of every statement. These types of system calls are generally expensive. The system monitor also increases memory consumption. For every monitor element tracked by the system monitor, the database manager uses its memory to store the collected data.
In order to minimize the additional processor time involved in maintaining monitoring information, monitor switches control the collection of potentially expensive data by the database manager. Each switch has only two settings: ON or OFF. If a monitor switch is OFF, the monitor elements under that switch's control do not collect any information. There is a considerable amount of basic monitoring data that is not under switch control, and will always be collected regardless of switch settings.
Each monitoring application has its own logical view of the monitor switches (and the system monitor data). Upon startup each application inherits its monitor switch settings from the dft_monswitches parameters in the database manager configuration file (at the instance level). A monitoring application can alter its monitor switch settings with the UPDATE MONITOR SWITCHES USING MONSWITCH OFF/ON command. The MONSWITCH parameter holds values found in the Monitor Switch column in the Snapshot Monitor Switches table shown in the next section. Changes to the switch settings at the application level only affect the application from where the switch was changed.
Instance-level monitor switches can be changed without stopping
the database management system. To do this use the UPDATE DBM CFG
USING DBMSWITCH OFF/ON command. The
DBMSWITCH parameter holds values from the DBM Parameter column in the Snapshot
Monitor Switches table shown in the next section. This dynamic updating of switches requires
that the application performing the update be explicitly attached
to the instance for the updates to dynamically take effect. Other
existing snapshot applications will not be affected by a dynamic update.
New monitoring applications will inherit the updated instance-level
monitor switch settings. For an existing monitoring application to
inherit the new default monitor switch values, it must terminate and
re-establish its attachment. Updating the switches in the database
manager configuration file will update the switches for all partitions
in a partitioned database.
The database manager keeps track of all the snapshot monitoring applications and their switch settings. If a switch is set to ON in one application's configuration, then the database manager always collects that monitor data. If the same switch is then set to OFF in the application's configuration, then the database manager will still collect data as long as there is at least one application with this switch turned ON.
The collection of time and timestamp elements is controlled by the TIMESTAMP switch. Turning this switch OFF (it is ON by default) instructs the database manager to skip any timestamp operating system calls when determining time or timestamp-related monitor elements. Turning this switch OFF becomes important as CPU utilization approaches 100%. When this occurs, the performance degradation caused by issuing timestamps increases dramatically. For monitor elements that can be controlled by the TIMESTAMP switch and another switch, if either of the switches is turned OFF, data is not collected. Therefore, if the TIMESTAMP switch is turned OFF, the overall cost of data under the control of other monitor switches is greatly reduced.
Event monitors are not affected by monitor switches in the same way as snapshot monitoring applications. When an event monitor is defined, it automatically turns ON the instance level monitor switches required by the specified event types. For example, a deadlock event monitor will automatically turn ON the LOCK monitor switch. The required monitor switches are turned ON when the event monitor is activated. When the event monitor is deactivated, the monitor switches are turned OFF.
The TIMESTAMP monitor switch is not set automatically by event monitors. It is the only monitor switch that controls the collection of any monitor elements belonging to event monitor logical data groupings. If the TIMESTAMP switch is OFF, most of the timestamp and time monitor elements collected by event monitors will not be collected. These elements are still written to the specified table, file, or pipe, but with a value of zero.
|Monitor Switch||DBM Parameter||Information Provided|
|BUFFERPOOL||DFT_MON_BUFPOOL||Number of reads and writes, time taken|
|LOCK||DFT_MON_LOCK||Lock wait times, deadlocks|
|SORT||DFT_MON_SORT||Number of heaps used, sort performance|
|STATEMENT||DFT_MON_STMT||Start/stop time, statement identification|
|TABLE||DFT_MON_TABLE||Measure of activity (rows read/written)|
|UOW||DFT_MON_UOW||Start/end times, completion status|
- Buffer pool activity information
- Lock, lock wait, and time related lock information
- Sorting information
- SQL statement information
- Table activity information
- Times and timestamp information
- Unit of work information
Event monitors are only affected by the time and timestamp information switch. All other switch settings have no effect on the data collected by event monitors.