|Lesson 14||Collecting Statistics |
|Objective||Generate 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
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.
or something similar. Follow this process to generate statistics for a database:
- Decide on the time period that you want the statistics to cover.
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.
- 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.
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
Collecting Statistics - Exercise