Oracle Analysis - UTLESTAT

Diagnostic Utilities The Oracle database diagnostic and monitoring tools play a prominent role in effectively examining the system statistics. If you are curious about what is happening inside Oracle, 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 traditional methods for information collection is running a sequence of scripts named UTLBSTAT and UTLESTAT. These are solid and time-tested 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 appraise 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 planned period of time, you run the UTLESTAT to end the static-collecting method and generate the definite 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: - 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 former 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 versant 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 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 delete the report. You need to alter the system to gather 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 activate the analysis process from the directory. The generated output report is called “report.txt” and it is usefull to rename the report with a unique timestamp for identification and save it for further archive purposes. Execution Process in a Broader Spectrum UTLBSTAT and UTLESTAT are the existing 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 predefined period of time say during the end of the month or busiest time of the day and produce report. In which tablespace 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 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 change the SYS’s default TS to SYSTEM. It is often recommended to make this change because when UTLESTAT is executed, these tables are dropped 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 run the scripts from the server manager (SVRMGR) which is connected as SYSDBA. To execute 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 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 run normally for a specific time so that the statistics can be created. 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 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 needed.

Additional information about UTLBSTAT and UTLESTAT and other Oracle Diagnosis Tools like TRCANLZR is just a click away!