Bstat and Estat
David Clement
September 2004
Oracle versions 7-8
The tools with "stat" in their names are based on the dynamic performance views in the data dictionary, often called the v$ views. These provide a synchronic assessment of the Oracle instance: that is, they report the performance of the whole instance as a system for a short period. The Bstat and Estat tools were available in Oracle for several releases; Statspack, a better tool providing the same functionality and more, made them obsolete beginning with version 8.1.6, although the scripts for Bstat and Estat could still be found on the installation CDs before 9i.
The Bstat and Estat tools consist of two scripts, called utlbstat.sql and utlestat.sql in most releases. The first of these is a utility for beginning statistics collection and the second is a utility for ending statistics collection, hence their names. The second one also produces a report of the differences observed between them. Typically, the scripts are executed 10 minutes to an hour apart, and then the report that the second one generates is read in search of tuning and optimization data. The scripts produce complete data and good output only in Server Manager (svrmgrl). To run the first one, connect at the internal or sysdba level and start the script. For example, issue the following commands at the Server Manager prompt (the example assumes a Microsoft environment).
connect / as sysdba
@D:/orant/rdbms80/admin/utlbstat
connect / as sysdba
@D:/orant/rdbms80/admin/utlestat
report.txt will be generated in the
current directory.
The report is long and complex, and its format differs in different releases. The most useful information is in the statistics section, which is usually the third one, which gives performance measurements for CPU usage for the reporting period (misleadingly called "CPU used by this session") and parse time CPU. After that, the wait events should be compared to the performance measurements. High percentages of time spent on certain wait events usually indicate underlying problems.
The relationship between a wait event and its systemic cause is sometimes obvious but sometimes indirect. An example where the relationship is obvious is the
log file switch completion
event. This means that the system had to wait until a nearly full
redo log file was saved and another redo log file became available.
This is a normal task that would not take much system time unless
something was wrong with the redo logs, such as their being too small
to support the load on this particular system. For a more subtle case,
a great deal of time spent waiting for the db file sequential
read event may indicate contention for index segments. Because
index accesses are of small amounts of data, they dominate the
db file sequential read metric, just as full table scans
dominate the db file scattered read metric. However,
there are other causes of db file sequential read events,
such as reading datafile headers, so it is not a foregone conclusion
that index contention is behind the wait event.
The way to deal with these reports is to ignore the possible causes of wait events unless and until a wait event seems problematic, and then to research the possible causes of that particular wait event. The Oracle Metalink system notes are probably the best place to begin the research.
|
|
|