Example: Investigating an increase in lock escalations using the change history event monitor
You can use the change history event monitor to detect what changes might have led to a database performance degradation.
ScenarioIn this example, users are reporting a decline in database performance. The database administrator (DBA) notices an abnormally high number of lock escalations occurred in the last 24 hours. The DBA also notices a corresponding increase in application lock wait times over the same period.
The DBA has been monitoring configuration changes, index changes, and LOAD operations with a change history event monitor. The event monitor was created with the following statement:
The event monitor was activated with the following statement:
CREATE EVENT MONITOR CFGHIST FOR CHANGE HISTORY WHERE EVENT IN (DBCFG, DBMCFG, DBCFGVALUES, DBMCFGVALUES,REGVAR,REGVARVALUES, DDLDATA, LOAD) WRITE TO TABLE
SET EVENT MONITOR CFGHIST STATE=1
The following table shows some sample event monitor data that the CFGHIST change history event monitor might write to the CHANGESUMMARY_CFGHIST table. All change history event monitors write to the CHANGESUMMARY logical data group. As described in
APPL_ID APPL_NAME .... EVENT_ID EVENT_TIMESTAMP ---------------------------- --------- .... -------- ------------------- *LOCAL.tripathy.111028110756 db2bp .... 1 28/10/2011 07:12:02 EVENT_TYPE MEMBER .... ---------- ------ .... EVMONSTART 0 ....
Since performance was not previously a problem, the DBA suspects that some recent change might be causing the problem and performs the following steps:
- Check the CHANGESUMMARY logical data group for any changes made
in the last 24 hours. For this example, assume that the current time
is 31/10/2011 06:00:00.
The query returns the following result:
SELECT EVENT_TYPE FROM CHANGESUMMARY_CFGHIST WHERE EVENT_TIMESTAMP > CURRENT TIMESTAMP - 24 HOURS
The output indicates that there were two database configuration updates in the last 24 hours.
EVENT_TYPE ---------- DBCFG DBCFG
- Query the DBDBMCFG logical data group to get the details of the configuration changes.
The query returns the following result:
SELECT EVENT_TIMESTAMP, CFG_NAME, CFG_VALUE, CFG_OLD_VALUE, DB_DEFERRED FROM DBDBMCFG_CHGHIST
The output indicates that locking changes were made during the time period when performance declined.
EVENT_TIMESTAMP CFG_NAME CFG_VALUE CFG_OLD_VALUE DB_DEFERRED ------------------- ----------- --------- ------------- ----------- 30/10/2011 08:41:39 LOCKLIST 1024 2048 N 30/10/2011 08:42:35 LOCKTIMEOUT 0 -1 Y
The DBA notices that the LOCKTIMEOUT change was deferred and issues a query to check whether the database was activated after this configuration change. This check determines whether the configuration change was picked up by the database. If the change was not picked up, then it is unlikely that it is causing the performance problem. The database activation time is recorded in the EVMONSTART logical data group. All change history event monitors write to the EVMONSTART logical data group by default.
The query returns a nonzero value.
SELECT COUNT (*)as POST_CFG_ACTIVATIONS FROM EVMONSTART_CHGHIST WHERE DB_CONN_TIME > TIMESTAMP(2011-10-30-08:42:35)
This nonzero value confirms that the database was activated after the LOCKTIMEOUT configuration parameter was changed, meaning that the new value is in effect. The DBA now understands what changed on the system and can try adjusting the lock-related configuration parameters back to their original values to see whether this resolves the issue.
POST_CFG_ACTIVATIONS -------------------- 1
Note: If the change history event monitor was inactive when the configuration parameters were changed, then the event monitor would not capture the DBCFG events. Instead the change history event monitor would capture a DBCFGVALUES event when the event monitor was started. In the DBDBMCFG logical data group each row represents a configuration parameter that was updated as part of a DBCFG or DBMCFG event, or captured at event monitor startup as part of a DBCFGVALUES or DBMCFGVALUES event. The CFG_COLLECTION_TYPE monitor element identifies whether the record describes a configuration parameter update, or an initial value recorded at event monitor startup. The DBA would need to compare the values captured at the start of the current change history event monitor with the values of the previous capture to look for changed values that might be causing the problem. Examining the diagnostic log would also be helpful.