RelationalDBDesign RelationalDBDesign

Sorting Operations   «Prev 

utlbstat-utlestat output

You can also use the utlbstat-utlestat report to monitor disk sorting. With this utility you can see the number of sorts for any pre-defined elapsed time period.

What exactly are UTLBSTAT and UTLESTAT?

These are scripts that produce snapshots and report statistics for a specific period of time across an entire Oracle database.
So you can analyze how the Oracle database performs from the snapshot produced from the time you start UTLBSTAT (begin) until you run the UTLESTAT (end).
Basically, you run the UTLBSTAT when you start the database and after the desired period of time, you run the UTLESTAT to end the static-collecting method and generate the hard copy report.
The generated report after running these diagnostic utilities contains a detailed and wide-range listing of the activities happened inside the database. It includes detailed information regarding the following:
  1. Database statistics
  2. Library cache activity
  3. Events causing waits
  4. Rollback segment usage and contention statistics
  5. Latch statistics
  6. Dictionary cache statistics
  7. I/O by data files and tablespaces
  8. initSID.ora parameters
  9. Database version and timings of the activities
  10. Period of measurement

It was historically the first method used when serious database tuning begun. If you are interested in UTLBSTAT or UTLESTAT (sometimes named BSTAT or ESTAT), the easiest way to become familiar with these utilities is to run it and interpret the generated results.
But it may be overwhelming to deal with the generated report which is lengthy.
Consider these guidelines when you are running these utilities:
  1. Initially run the report and save the resulting output so that you have the baseline of information.
  2. If you are running the report during poor-performing time, it may not be helpful to understand the real changes inside the database.
  3. Running the report during a specific period of time will fetch you the accurate results.
  4. If the database has crashed or there was a shutdown between the execution of the BSTAT and ESTAT scripts, statistics are invalid and is better to disregard the report.

You need to alter the system to gather more refined statistics regarding the time before you run the generated report. You may choose any of the two options such as
  1. you can set the parameter TIMED_STATISTICS to TRUE (in the init.ora) or
  2. you can set the value using "ALTER SYSTEM".
Theoretically speaking, such a change should bring slight performance hit, but it is rarely reported.
You can start the analysis process from any directory. The generated output report is called "report.txt" and it is advised to rename the report with a unique timestamp for identification and save it for further archive purposes.