15.1 How do I…Run the UTLBSTAT and UTLESTAT scripts?

Problem

I want to run the UTLBSTAT and UTLESTAT scripts to get a general overview of my database’s performance over a certain period of time. How and when should I do this?

Technique

Run the UTLBSTAT at database startup or, if you’re interested in database activity during a particular time period, run it just prior to the beginning of that period. Run the UTLESTAT script at database shutdown or immediately after the period of database activity of interest. These scripts contain a CONNECT INTERNAL command so users cannot run them in their original form from the SQL*Plus environment; either Server Manager or the Oracle SQL Worksheet utility must be the calling environment for these scripts. This How-To shows how to call the statistics scripts from the SQL Worksheet utility and suggests modifications to these scripts to redirect their DDL activity away from the SYSTEM tablespace, which is the (unfortunate) default.

Steps

1. Run SQL Worksheet, connect as the WAITE user, and open the script called CHP15_1.SQL.

Note-Open scripts in SQL Worksheet by using the Open button on the left side of the window, or by using the Open command on the File drop-down menu.

Run the script by pressing the F5 key. The first action the script takes is to connect to the database as internal. This forces you to supply the password and the database service name again. The script and the last part of its output appear in Figure 15.1.

The first line of the CHP15_1.SQL script alters the user SYS so that SYS’ default tablespace is USERS instead of SYSTEM. The second line calls the UTLBSTAT script and the third line sets SYS’ default tablespace back to SYSTEM.

Note-The CHP15_1.SQL script assumes that your database has a tablespace called USERS as suggested in How-To 1.6. If this is not the case, then change the CHP15_1.SQL script so that it makes a valid change to user SYS’ default tablespace.

2. Wait for the database activity of interest to occur. This can be a particular long-running batch job or it may just be standard business day database activity.

3. Run SQL Worksheet, connect as the WAITE user, and open the script called CHP15_2.SQL. Run the script by pressing the F5 key. The script and the last part of its output appears in Figure 15.2.

How It Works

UTLBSTAT creates tables and views containing cumulative database performance summary information at the time when the script runs. All the objects UTLBSTAT creates contain the word begin.

UTLESTAT creates tables and views containing database objects containing cumulative database performance summary information at the time when its script runs. The names of the tables and views that UTLESTAT creates all contain the word end. UTLESTAT’s most important function is to submit SQL statements summarizing performance information for the time period between UTLBSTAT and UTLBSTAT’s submissions. UTLESTAT spools the results of these SQL statements to a file called REPORT.TXT, which resides in the directory that was current at the time of UTLESTAT’s submission.

Comments

The UTLBSTAT and UTLESTAT scripts were developed by Oracle for Oracle kernel developers. Some of the information that UTLESTAT generates has little meaning even to experienced DBAs, but the report does contain valuable information if you know where to look.

This chapter takes a double-edged approach, and wherever possible, the How-To’s refer to the relevant sections of the UTLESTAT report on the premise that no matter what Oracle installation readers call home, UTLBSTAT and UTLESTAT will be there. In order to provide a customizable approach, in addition, the chapter also contains scripts that query the data dictionary to reveal performance data.

The scripts CHP15_1.SQL and CHP15_2.SQL are nothing more than jackets for the UTLESTAT and UTLBSTAT scripts. Their sole purpose is to redirect the tables created by the statistics scripts to a tablespace other than SYSTEM. This is a worthwhile endeavor, because the statistics scripts create, populate, and drop a series of database objects, which is exactly the kind of activity that should not occur in the SYSTEM tablespace.

An alternative to the jacket scripts is to modify the UTLBSTAT and UTLESTAT scripts to use another tablespace. The only drawback to this strategy is the resulting need to create copies of the statistics scripts with each new release of the Oracle server.