Database Monitoring   «Prev  Next»
Lesson 14Collecting Statistics
ObjectiveGenerate statistics for your database.

Generate statistics for your database

Oracle supplies two scripts that you can use to generate a report with detailed statistics about your database. These two scripts work together to gather statistics over a period of time, and are named utlbstat.sql and utlestat.sql. These scripts are designed to be run from Server Manager while connected as INTERNAL. They may not work otherwise. Both of these files are located in the $ORACLE_HOME/rdbms/admin directory. For Windows NT users, the $ORACLE_HOME directory is usually. 

C:\ORANT
or something similar. Follow this process to generate statistics for a database:
  1. Decide on the time period that you want the statistics to cover.
  2. Run utlbstat.sql at the beginning of that period. This script gathers a snapshot of a large number of statistics and stores that in a set of work tables.
  3. Do not shut down the database between the time that you run utlbstat and the time that you run utlestat. Doing so will result in a report containing invalid data.
  4. Run utlestat.sql at the end of the period. This script gathers another snapshot, computes the differences between the beginning and ending of the period, and generates a report showing the results.

The report generated by utlestat.sql will be in a file named report.txt. This file will be created in whichever directory is current when you run the script. You can view it with any text editor or send it to a printer if you prefer to read hard copy. The report is quite extensive and contains several suggestions of things to watch out for. Among other things, it contains all the information needed to compute the hit ratios described earlier in this module. If you want to see what it's like to run these scripts from Server Manager, see this utlbstat utlestat Scripts.
If you want to see a complete report, you can download one from the course Resources page.
In the next lesson, you will monitor the I/O load across drives.

Buffer Cache Hit Ratio - Quiz

Click the Quiz link below to answer a few questions about hit ratios.
Buffer Cache Hit Ratio - Quiz

Collecting Statistics - Exercise

Click the Exercise link below to practice using utlbstat and utlestat to generate statistics for the COIN database.
Collecting Statistics - Exercise