DBTime-Graph and ADDM Methodology

Identifying the component contributing the most database time is equivalent to finding the single database component that provides the greatest benefit when tuned. ADDM uses database time to identify database components that require investigation and also to quantify performance bottlenecks. 

The first step in automatic performance tuning is to correctly identify the causes of performance problems. Only when the root cause of the performance problem is correctly identified, is it possible to explore effective tuning recommendations to solve or alleviate the issue.

ADDM looks at the database time spent in two independent dimensions:

• The first dimension looks at the database time spent in various phases of processing user requests. This dimension includes categories such as “connecting to the database,” “optimizing SQL statements,” and “executing SQL statements.”

• The second dimension looks at the database time spent using or waiting for various database resources used in processing user requests. The database resources considered in this dimension include both hardware resources, such as CPU and I/O devices, and software resources, such as database locks and application locks.

To perform automatic diagnosis, ADDM looks at the database time spent in each category under both these dimensions and drills down to the categories that had consumed significant database time. This two-dimensional drilldown process can be represented using the DBTime-graph.

Performance problems often distribute database time across many categories in one dimension but not in the other. For example, a database with insufficient CPU capacity slows down all phases involved in processing user requests, which is in the first dimension of the ADDM analysis. 

However, it would be evident from the second dimension that the top performance problem affecting the database is insufficient CPU capacity. This two-dimensional view of determining where the database time is consumed gives ADDM very good judgment in zooming in to the more significant performance issues.