Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Tuesday, 4 June 2013

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:


Post a Comment