Diagnostic Utilities

The Oracle database diagnostic and monitoring tools play a big role in effectively examining the database statistics. If you are curious about what is happening inside your Oracle database, you can make use of performance views or any of the GUI tools accessing them to understand the database in a broader spectrum. However, one of the most successful and proven methods for information collection is running a sequence of scripts named UTLBSTAT and UTLESTAT.

These are well-known tools to obtain the Oracle database statistics. But these scripts are phased out from the Oracle 8i series onwards with a similar and much more effective utility called STATSPACK.

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 interpret 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 favored period of time, you run the UTLESTAT to end the static-collecting method and generate the final report.

The generated report after running these diagnostic utilities contains a complete listing of the activities happened inside the database. It includes detailed information regarding the following:

• Database statistics

• Library cache activity

• Events causing waits

• Rollback segment usage and contention statistics

• Latch statistics

• Dictionary cache statistics

• I/O by data files and tablespaces

• initSID.ora parameters

• Database version and timings of the activities

• Period of measurement

It was the historic used method when serious database tuning was in demand. If you are interested in UTLBSTAT or UTLESTAT (known as BSTAT/ESTAT), the easiest way to become itimate 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 extensive.

Consider these guidelines when you are running these utilities. Initially run the report and save the resulting output so that you have the baseline of information. If you are running the report during poor-performing time, it may not be helpful to understand the real changes inside the database. But running the report during a specific period of time will fetch you the accurate results. If the database is crashed or there had been a shutdown between BSTAT and ESTAT, statistics would be invalid and it is better to delete the report.

You need to alter the system to accumulate more refined statistics regarding the time before you run the generated report. You may extract any of the two options such as you can set the parameter TIMED_STATISTICS to TRUE (in the init.ora) or you can set the value using "ALTER SYSTEM". Theoretically speaking, such a change should bring slight performance hit, but it is rarely observed.

You can begin the analysis process from the directory. The generated output report is called "report.txt" and it is wise to rename the report with a unique timestamp for identification and keep it for further archive purposes.

Execution Process in a Broader Spectrum

UTLBSTAT and UTLESTAT are the delivered SQL scripts that are primarily used to begin and end the collection of the statistics. Using these utilities, you can gather performance statistics and figures over a specific period of time say during the end of the month or busiest time of the day and produce report.

In which of the tablespaces should the utilities be executed? Temporary tables are created by the UTLBSTAT to store the statistics. The utilities connect as SYSDBA and the tables are created in the SYS's default TS SYSTEM. You have to create a new TS before executing the script and have to change the default TS of the system to this new one.

When you are finished with running both scripts, you can update the SYS's default TS to SYSTEM. It is often recommended to make this change because when UTLESTAT is executed, these tables are removed and may result in the fragmentation of SYSTEM tablespace. But most of the database administrators know that this low amount of fragmentation is easily negligible.

Steps to Run UTLBSTAT and UTLESTAT

You can execute the scripts from the server manager (SVRMGR) which is connected as SYSDBA. To run from the SVRMGR, the location of scripts in Oracle are as under:

SVRMGR>@oracle_HOME/rdbms/admin/utlbstat.sql

SVRMGR >@oracle_HOME/rdbms/admin/utlestat.sql

Steps to gather and end statistics

Execute the UTLBSTAT script from the above mentioned path. The BEGIN and END tables are created by the script and snapshots of data are taken from the dynamic performance tables (V$xxx) so that initial statistics can be sampled and stored in the BEGIN tables.

The following steps are used to run UTLBSTAT and UTLESTAT:

1.Initially start SQL*Plus and connect the internal.

2.To get in depth statistics, set the TIMED_STATISTICS to TRUE.

SQL> alter system set timed_statistics=true;

System altered.

3.To store the statistics, create temporary tables by running the script utlbstat.sql

SQL> @$ORACLE_HOME/rdbms/admin/utlbstat.sql

4.Let the Oracle database operate normally for a specific time so that the statistics can be created.

5.After the desired monitoring period, run the utlestat.sql to generate the report and drop the temporary tables.

SQL>@$ORACLE_HOME/rdbms/admin/utlestat.sql

6.Now you have to set the TIMED_STATISTICS to FALSE.

SQL> alter system set timed_statistics=false;

System altered.

At this stage, the script generates a elaborate report.txt file by selecting the required data from the DIFFERENCE tables. The default tablespace must be changed back to SYSTEM if you made any changes to run the scripts. Based on the sampled report, you can understand the specific areas of database where the tuning is beneficial.