7.1 Introduction

Monitoring your DBMS's performance indicators is one of the best ways to diagnose how well or badly the DBMS is performing. These indicators can be thought of as a DBMS's performance "vital signs" and can be calculated using counters, gauges, or details of the DBMs's internal activities. Monitoring performance indicators can ultimately help you determine the areas to which your tuning efforts should be directed.

To give you a better idea of which indicators and which internal activities we are talking about, consider the path a query follows inside a DBMS from its submission to the point when a result set or a return code is produced. Immediately after a query is entered into the system, it is transformed into an access plan by the optimizer. The access plan is one of the performance indicators that tell you how well the optimizer is contributing to the overall performance of your system. This is the first place to look whenever a query presents performance problems, but the query's story does not stop here.

An access plan causes other internal subsystems to run on its behalf, which will answer the query the plan represents. A query requires the cooperation of all components: the query execution subsystem may be computing a part of the plan (e.g., a join) while the disk subsystem may be fetching the next pages necessary for that operation; the cache manager may be trying to make room for those pages; the locking...