Print Article
BookMark Article
10 newest articles RSS
Oracle Database Diagnostic Utilities - UTLBSTAT And UTLESTAT
Author : Larry Maligan
Submitted : 2011-10-04 20:38:16 Word Count : 1001 Popularity: 51
Tags: UTLBSTAT, UTLESTAT
Diagnostic Utilities
The Oracle database diagnostic and monitoring tools play a important role in effectively examining the system statistics. If you are curious about what is happening inside Oracle, you can make use of data dictionary views or any of the GUI tools accessing them to understand the database in a broader spectrum. However, one of the most best methods for information collection is running a sequence of scripts named UTLBSTAT and UTLESTAT.
These are solid 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 evaluate 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 period of time, you run the UTLESTAT to end the static-collecting method and generate the ultimate report.
The generated report after running these diagnostic utilities contains a wide-range 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 ancient 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 close 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 substantial.
Consider these guidelines when you are executing 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 ignore the report.
You need to alter the system to cumulate more refined statistics regarding the time before you run the generated report. You may select 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 mentioned.
You can start the analysis process from the directory. The generated output report is called “report.txt” and it is advised 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 predefined SQL scripts that are primarily used to begin and end the gathering 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 hold 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 starting 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 change 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 think that this slight 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 collected 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 detailed 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 generated.
5.After the favored 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 extensive 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 created report, you can understand the specific areas of database where the tuning is beneficial.
Author's Resource Box
Additional information about UTLESTAT and other Oracle Diagnosis Tools like TRCANLZR is just a click away!
Article Source:
Articles Alive