Compare & Differentiate Statspack & AWR
The AWR report is a great tool for monitoring day by day
database activities for DBA. The use of Statspack/AWR report helps the DBA
quickly identify the possible cause or database load. The AWR report mainly
contains the following sections.
·
Database instance details
·
Database Memory statistics.
·
Top 5 wait events.
·
Top SQL’s order by execution time
and elapsed time.
·
SQL’s order by physical reads.
·
SQL’s order by buffer reads and
gets.
·
Tablespace information and hit
ratio on the table spaces.
·
Initialization Parameter’s
The purpose of these two tools is the same but why DBA
prefer to use AWR than Statspack.
1. Statspack needs to be installed manually while AWR is installed
configured and managed by default in a standard manner.
2. The AWR repository holds all the statistics available in STATSPACK as
well as some additional statistics which are not.
3. Statspack analysis is complex and needs a skilled eyes and an adequate
level of experience to detect problems. AWR along with ADDM runs continuously
and generates alerts or perform analysis automatically.
4. Statspack impose a reasonable load during snaps collection where as AWR
collection occurs continuously (offloaded to selected Background process)
allowing for smoother, less perceptible and less disruptive progress.
5. Statspack is not accessible via GUI such as OEM for viewing or
management, where as AWR is accessible both via the OEM as well as SQL &
PL/SQL for viewing or management the report.
6. Statspack gather information from V$SQL for high load SQL based on
certain criteria such as number of logical and physical I/O per SQL stored SQL
statements where as AWR recognize high load SQL as it occurs rather than
collecting high-load SQL from V$SQL (which may not be accurate at this time as it
captured before, may be outside of the snapshot period).
7. Statspack does not store the active session history (ASH) statistics
which are available in AWR dba_hist_active_sess_history view.
8. The Statspack does not store history for new metric statistics introduced
in oracle 10g such as the dba_hist_sysmetric_history and dba_hist_sysmetric_summary.
The AWR also contains views such as dba_hist_service_stat,
dba_hist_service_wait_class dba_hist_service_name views to store
history for cumulative performance statistics.
9. STATSPACK data is stored in the PERFSTAT schema in any designated
tablespace, while AWR data is stored in the SYS schema in the new SYSAUX tablespace
in 10g.
10. Statspack snapshot purges manually where as AWR snapshots are purged
automatically by MMON (default, keeping 7 days snapshots available you can
modify it). 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.
For related post click on the link:
