A production DBA often faces the problem of a slow database caused by badly written SQL code. Even when tested by developers, SQL code can generate many performance problems. A regular pre-production test made on a manually generated, small dataset, when run on the real, big production dataset, can cause system breakdown. Some complex executions need hours to complete. In that situation the DBA needs a way to collect and identify slow-running SQL statements, and send the most critical statements back to developers for additional change.
This article covers:
- Database and Event Monitor Switches
- Describing a Problem
- Finding a Top Sessions
- Reporting a Bad SQL Code
Database and Event Monitor Switches
There are many different troubleshooting techniques for discovering and diagnosing problems caused by badly written SQL statements. One of them is enabling special monitor switches.
Monitor switches are used to instruct the database manager to collect applications statistics and performance. The Monitoring period is defined by starting and stopping the event monitor switch. Collected information is then written into a dedicated serial file, with full details of each SQL call that was executed. The Event monitor statistic files has to be further processed using the db2trace database utility and the result file is readable ASCI file.
Monitor switches can be controlled at the database manager level (database monitor switches) for the whole database or at the individual session level (event monitor switches).
Database manager monitor switches
Displaying the current status of the database manager monitor switches:
# db2 "get dbm monitor switches" Default database monitor switches Buffer pool (DFT_MON_BUFPOOL) = OFF Lock (DFT_MON_LOCK) = OFF Sort (DFT_MON_SORT) = OFF Statement (DFT_MON_STMT) = OFF Table (DFT_MON_TABLE) = OFF Unit of work (DFT_MON_UOW) = OFF
- DFT_MON_BUFFERPOOL - collecting number of reads and writes to the database
- DFT_MON_LOCK - collecting locks wait statistics and deadlock occurrence
- DFT_MON_SORT - collecting sort statistics
- DFT_MON_STMT - collecting SQL statements information
- DFT_MON_TABLE - collecting table statistics, rows written and rows read
- DFT_MON_UOW - collecting timing information about Unit of Works
DB2 database version 8 has one new monitor switch DFT_MON_TIMESTAMP for collecting timestamps of monitored data. Database monitor switches are by default disabled and database manager will not collect any statistics.
Event monitor switches
On an application level, we can create private monitor switches independent of the database manager and other applications. Applications inherit their monitor switch settings from the database manager when connecting to a database.
Displaying current application event monitor switches:
db2 => get monitor switches Monitor Recording Switches Switch list for node 0 Buffer Pool Activity Information (BUFFERPOOL) = OFF Lock Information (LOCK) = OFF Sorting Information (SORT) = OFF SQL Statement Information (STATEMENT) = OFF Table Activity Information (TABLE) = OFF Unit of Work Information (UOW) = OFF
List of event monitor filters:
- DATABASE - logging database deactivation
- TABLES - logging changes for each accessed tables
- DEADLOCKS - logging deadlock occurance
- TABLESPACES - logging tablespace activity
- BUFFERPOOLS - logging bufferpool activity
- CONNECTIONS - logging connection activity
- STATEMENTS - logging finished SQL statement information
- TRANSACTIONS - logging commit and rollback activity
The syntax for event switches has an optional <<where>> conditional parameter, for further restricting data collection. Possible conditions are:
- APPL_ID - application marker
- AUTH_ID - authorization marker
- APPL_NAME - application program name
- LOCAL - Event monitor reports activity only for the node where it is running
- GLOBAL - Event monitor reports activity from all nodes
Activating SQL statement collecting for a specified user will help us to find potential problems with application SQL code. The typical reason for system slowdown is long-running jobs that contain one or more expensive un-tuned SQL statement.