Database Reference
In-Depth Information
Chapter 5
Postmortem Analysis
of Irreproducible Problems
This chapter describes how to analyze a performance problem that you can neither reproduce nor observe while
it's happening. In other words, this chapter explains what you can do when you are trying to analyze a problem
that happened in the past and, as a result, you can't take advantage of the information provided by SQL Trace and
dynamic performance views. In such a situation, the only way you can make a quantitative analysis is by using a
repository containing performance statistics covering the period of time you want to analyze.
Repositories
Oracle Database provides two repositories that store information that you can use to analyze a performance problem
that happened in the past:
•
Automatic Workload Repository (AWR)
•
Statspack
Since AWR is an evolution of Statspack, it is based on the same three basic concepts(as are the utilities provided
with them):
•
At regular intervals (for example, 30 minutes), the content of a bunch of dynamic performance
views (for example, the views discussed in Chapter 4) is dumped into a set of tables. The
resulting data is called a
snapshot
and can be identified by an integer value called a
snapshot
ID
. For some dynamic performance views, all the data they provide is dumped. For others,
only part of the data is dumped. For example, information about SQL statements is dumped
only for the top consumers.
•
Through a script provided by Oracle or, for AWR, with a tool (for example, Enterprise Manager
or SQL Developer), you can find out how much the statistics stored in the repository changed
over a period of time delimited by two snapshots.
•
In general, snapshots aren't preserved indefinitely. Instead, they are purged after a specific
retention period has elapsed. Snapshots of specific time periods can be marked as
baselines
and, as a result, be excluded from the purging process. Baselines are useful for comparative
purposes. For example, if you preserve a baseline for a period of time in which the system
performs as expected, you can compare that period with a baseline when a performance
problem occurs.
