Monday, August 4, 2008

Difference or Advantage between AWR and STATSPACK report

1)The AWR is the next evolution of the STATSPACK utility.

2)The AWR repository holds all of the statistics available in STATSPACK as well as some additional statistics which are not.

3)STATSPACK does not store the Active Session History (ASH) statistics which are available in the AWR dba_hist_active_sess_history view.

4)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.

5)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.

6)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.

7)Statspack snapshots must be run by an external scheduler (dbms_jobs, CRON, etc.). AWR snapshots are scheduled every 60 minutes by default. Administrators can manually adjust the snapshot interval if so desired.

8)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.

9)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.

10)AWR snapshots provide a persistent view of database statistics. They are stored in the system-defined schema, which resides in a new tablespace called SYSAUX. A snapshot is a collection of performance statistics that are captured at a specific point in time. The snapshot data points are used to compute the rate of change for the statistic being measured. A unique SNAP_ID snapshot identifier identifies each snapshot.

Related Documents
http://arjudba.blogspot.com/2008/08/how-to-invoke-collect-and-run-awr-and.html

No comments:

Post a Comment