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.

Before running either script, you need to start Server Manager and connect as INTERNAL. The boldface type in the following example shows what you would type.

$SQL>
  Oracle Server Manager Release 3.1.5.0.0 - Production
  (c) Copyright 1997, Oracle Corporation. All Rights Reserved.
  Oracle8i Release 8.1.5.0.0 - Production
  With the Partitioning and Java options
  PL/SQL Release 8.1.5.0.0 - Production
  SQL> connect internal

  Connected.

Once connected as INTERNAL, you begin the statistics collection process by running utlbstat. you will see quite a bit of output scroll by on the screen as the work tables are dropped, re-created, and then populated with a snapshot of current statistic values.

SQL>@utlbstat
  SQL> Rem ******************************************************************
  SQL> Rem                 First create all the tables
  SQL> Rem ******************************************************************
  SQL> 
  SQL> drop table stats$begin_stats;
  drop table stats$begin_stats
             *

  ORA-00942: table or view does not exist
  SQL> create table stats$begin_stats as select * from v$sysstat where 0 = 1;
  Statement processed.
  SQL> drop table stats$end_stats;
  drop table stats$end_stats
             *
  ORA-00942: table or view does not exist
  SQL> create table stats$end_stats as select * from stats$begin_stats;
  Statement processed.
  ...

To end the statistics process, run utlestat. The output from this script will be even longer, and you will see the entire statistics report scroll by on the screen as it is written out to the report.txt file.

SQL> @utlestat
  SQL> rem 
  SQL> rem $Header: utlestat.sql 17-apr-98.15:26:01 kquinn Exp $ estat.sql 
  SQL> rem 
  SQL> Rem Copyright (c) 1988, 1996, 1998 by Oracle Corporation
  SQL> Rem NAME
  SQL> REM    UTLESTAT.SQL
  SQL> Rem  FUNCTION
  SQL> Rem    This script will generate a report (in "report.txt") which will contain
  SQL> Rem    useful information for performance monitoring.  In particular
  SQL> Rem    information from v$sysstat, v$latch, and v$rollstat.
  SQL> Rem  NOTES
  SQL> Rem    Don't worry about errors during "drop table"s, they are normal.

This should give you a good idea of the mechanics involved in generating statistics. The Server Manager output scrolling by on the screen is not important. It's the final results in the report.txt file that you want to look at.

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

Click the Exercise link below to practice using utlbstat and utlestat to generate statistics for the COIN database.
Collecting Statistics - Exercise
In the next lesson, you will monitor the I/O load across drives.