Difference between Statspack, UTLBSTAT/UTLESTAT and AWR

October 15, 2009 at 12:03 pm (Oracle) (Difference between Statspack and AWR, statspack UTLBSTAT UTLESTAT, statspack UTLBSTAT UTLESTAT awr)

Statspack differs from the existing UTLBSTAT/UTLESTAT performance scripts in the following ways:

* Statspack collects more data, including high-resource SQL.
* Statspack precalculates many ratios useful when performance tuning, such as cache hit ratios, rates, and transaction statistics. Many of these ratios must be calculated manually when using BSTAT/ESTAT.
* Statspack uses permanent tables owned by the PERFSTAT user to store performance statistics. Rather than creating and dropping tables each time, data is inserted into the existing tables, making it easier to compare historical data.
* Statspack separates data collection from report generation. Data is collected when a snapshot is taken. The performance engineer then runs the performance report and views the data collected.
* Statspack makes data collection easy to automate using either DBMS_JOB or an operating system utility to schedule collection tasks.
* Statspack considers a transaction to finish either with a COMMIT or a ROLLBACK, and so calculates the number of transactions as user commits + user rollbacks. BSTAT/ESTAT considers a transaction to complete with a COMMIT only, and so assumes that transactions = user commits. For this reason, comparing statistics for each transaction between Statspack and BSTAT/ESTAT can result in significantly different ratios.

Excerpt from Chris Foot on the difference between Statspack and AWR snapshots are:

* Statspack snapshots must be run by an external scheduler (dbms_jobs, CRON, etc.). AWR snapshots are scheduled every 60 minutes by default. A new background server process called MMON is responsible for initiating the snapshot mechanism. Administrators can manually adjust the snapshot interval if so desired.
* ADDM captures a much greater depth and breadth of statistics than Statspack does. During snapshot processing, MMON transfers an in-memory version of the statistics to the permanent statistics tables.
* Statspack snapshot purges must be scheduled manually. When the Statspack tablespace runs out of space, Statspack quits working. AWR snapshots are purged automatically by MMON every night. MMON, by default, tries to keep one week’s worth of AWR snapshots available. If AWR detects that the SYSAUX tablespace is in danger of running out of space, it will free space in SYSAUX by automatically deleting the oldest set of snapshots. If this occurs, AWR will initiate a server-generated alert to notify administrators of the out-of-space error condition. Administrators can manually adjust the amount of information retained by invoking the MODIFY_SNAPSHOT_SETTINGS PL/SQL stored procedure and specifying the RETENTION parameter input variable.

Excerpt from Donald Burleson’s STATSPACK and AWR Statistics Comparison:

The AWR repository holds all of the statistics available in STATSPACK as well as some additional statistics which are not. The following information on statistics is specific to those stored in the AWR that are not part of the STATSPACK.

STATSPACK does not store the Active Session History (ASH) statistics available in the AWR dba_hist_active_sess_history view. The ASH allows DBAs to perform time-series analyses of wait events for a particular session history.

An important difference between STATSPACK and the AWR is that STATSPACK does not store history for new metric statistics introduced in Oracle10g. The key AWR views, dba_hist_sysmetric_history and dba_hist_sysmetric_summary , help the DBA build time-series reports for important database performance metrics such as Total Time Waited or Response Time Per Txn.

The AWR also contains views such as dba_hist_service_stat , dba_hist_service_wait_class and dba_hist_service_name , which store history for performance cumulative statistics tracked for specific services.

The latest version of STATSPACK included with Oracle10g contains a set of specific tables, which track history of statistics that reflect the performance of the Oracle Streams feature. These tables are stats$streams_capture , stats$streams_apply_sum , stats$buffered_subscribers , stats$rule_set , stats$propagation_sender , stats$propagation_receiver and stats$buffered_queues . The AWR does not contain the specific tables that reflect Oracle Streams activity; therefore, if a DBA relies heavily on the Oracle Streams feature, it would be useful to monitor its performance using STATSPACK utility.