System performance tools examine the database server, its
configuration, and usage. The most commonly used system performance tool is the
performance monitor. Database performance monitoring and
analysis tools support many types of performance-oriented requests in many
ways. For example, system performance tools can operate:
- In the background mode as a batch job that reports on performance statistics written by the DBMS trace facility
- In the foreground mode as an online monitor that either traps trace information or captures information from the DBMS control blocks as applications execute
- By sampling the database kernel and user address spaces as the program runs and by capturing information about the performance of the job, independent of database traces
- By capturing database trace information and maintaining it in a history file (or table) for producing historical performance reports and for predicting performance trends
- As a capacity planning device that gives statistical information about an application and the environment in which it will operate
- As an after-the-fact analysis tool on a workstation, that analyzes and graphs all aspects of application performance and system-wide performance
Each database performance monitor supports one or more of
these features. The evaluation of database performance monitors is a complex
task. Sometimes more than one performance monitor is used at a single
site—perhaps one for batch reporting and another for online event monitoring.
Maybe an enterprise-wide monitoring solution has been implemented and one
component of that solution is a database module that monitors your DBMS, but it
lacks the details of a more sophisticated DBMS monitor. So, another performance
monitor is purchased for daily DBA usage, while the module of the enterprise-wide
monitoring solution is used for integrated monitoring by system administrators.
Modern database performance tools can set performance
thresholds that, once reached, will alert the DBA, perform another task to
report on, or actually fix the problem. These tools are typically agent-based.
An agent is a piece of independent code that runs on the
database server looking for problems. It interacts with, but does not rely on,
a console running on another machine that is viewed by the DBA. This agent
architecture enables efficient database monitoring because the agent is not
tied to a workstation and can act independently. The agent sends information to
the DBA only when required.
Additionally, some system performance tools are available
that focus on a specific component of the DBMS such as the buffer pools (data cache). Such a
tool can be used to model the memory requirements for database caching, to
capture data cache utilization statistics, and perhaps even to make
recommendations for improving the performance of the buffers.
Another type of performance optimization tool enables
database configuration parameters to be changed without recycling the DBMS
instance, subsystem, or server. These tools are useful when the changes require
the DBMS to be stopped and restarted. Such tools can dramatically improve
availability, especially if configuration parameters need to be changed
frequently and the DBMS does not support dynamic parameter modification.
A few ISVs provide invasive system
performance tools that enhance the performance of databases by adding
functionality directly to the DBMS and interacting with the database kernel.
Typically, these products take advantage of known DBMS shortcomings.
For example, products are available that enhance the
performance of reading a database page or block or that optimize data caching
by providing additional storage and control over buffers and their processing.
Care must be taken when evaluating invasive performance tools. New releases of
the DBMS may negate the need for these tools because functionality has been
added or known shortcomings have been corrected. However, this does not mean
that you should not consider invasive database performance tools. They can pay for themselves after only a short period of time.
Discarding the tool when the DBMS supports its functionality is not a problem
if the tool has already paid for itself in terms of better performance.
One final caution: Because invasive performance tools can
interact very closely with the database kernel, be careful when migrating to a
new DBMS release or a new release of the tool. Extra testing should be
performed with these tools because of their intrusive nature.
