AWR Vs StatPack

When you face performance issue in database the first thing coming to your mind is Automatic Workload Repository (AWR) or STATPACK reports but what is the difference between them, in this article i will try to mention as much as i can the difference between them.

1-you should be aware that AWR not exists in database 9i so you forced to use statepack, include to that steps to generate AWR much easier than STATPACK.

2-AWR hold all the information and statistics that exists in STATPACK, include to that AWR hold Additional Information.

3- in AWR you will find information called Active Session History ( ASH ) which is not exists in STATPACK.

4- To generate STATPACK you should Run Procedure to enable snapshot, you can use DBMS_JOB or schedule it using crontab.

5- AWR snapshots provide a persistent view of database statistics. A snapshot is a collection of performance statistics that are captured at a specific point in time,AWR snapshots are scheduled every 60 minutes by default.

 6- Statspack snapshot purges must be scheduled manually otherwise AWR snapshots are purged automatically using MMON.

Thank you
Osama Mustafa


AWR : automatic workload repository

The AWR is used to collect performance statistics including:

  • Wait events used to identify performance problems.
  • Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.
  • Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view.
  • Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.
  • Object usage statistics.
  • Resource intensive SQL statements.

I will not get into Details how to generate AWR since i mention it before on my Blog .

 ADDM : automatic database diagnostic monitor

analyzes data in the Automatic Workload Repository (AWR) to identify potential performance bottlenecks.and we use it for the following cases :

  •      CPU bottlenecks
  •      Undersized memory structures
  •      I/O capacity issues
  •      High load SQL statements
  •      RAC specific issues
  •      Database configuration issues
  •      Also provides recommendations on hardware changes, database configuration & schema changes.

Generate ADDM  :

  • Login to SQL
  • @$ORACLE_HOME/rdbms/admin/addmrpt.sql
  •  enter system password when you asked for .
  • Specify a begin_snap from the list and press Enter.
  • Specify the end_snap from the list and press Enter.
  •  Report Name

ASH : Active Session History 

statistics from the in-memory performance monitoring tables also used to track session activity and simplify performance tuning.

ASH reports Give the following information :

  • Top User Events (frequent wait events)
  • Details to the wait events
  • Top Queries
  • Top Sessions
  • Top Blocking Sessions
  • Top DB Object.
  • Activity Over Time

 Generate ASH reports :

The Best way to do that using OEM.  (Enterprise manager).

Thank you
Osama Mustafa

AWR Reports Vs ASH Reports

Sometimes when you face performance issue On your database, Usually first thing you are doing is Generate One of the above reports . But What is the benefits of these reports ? What is the difference ? When Can i use them ?

Let Start

I share in my blog earlier how to generate AWR reports . But Today we are talking About another topic .

Automatic Workload Repository Reports/(AWR):

this report appear in 10g Database, I heard that some people said “AWR used Instead Of Statpack” !! NO AWR is higher Version of statpack , statpack still exists but you have to enable it.

The AWR takes a snap shot of the database in specified intervals (default is one hour) and stores in Sysaux tablespace. and you can change this interval using the following :

retention => 43200, -- Minutes (= 30 Days). Current value retained if NULL.
interval => 30); -- Minutes. Current value retained if NULL.

The snap shots are taken automatically if the statistics_level parameter is set to typical/all. If it set to basic then statistics details are not gathered,The AWR contains the performance statistics and workload information on the database.

and you can take extra snapshot by :


DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (
low_snap_id => 22,
high_snap_id => 32);

As i mention above you can enable it , disable by the following command :

Enable AWR :

alter system set  statistic_level = {typical | all} scope=spfile ;

Disable AWR :

alter system set   set statistic_level = {basic} scope=spfile

 Check Statistic if its been Gathered By Fire :

Select * from V$statistics_level;

The statistics are collected and stored in memory in SGA. The in memory statistics collection area is a circular buffer, where the old data is overwritten after flushing to disk.

he AWR statistics snap shot are owned by Sys schema. ,The AWR is used to collect performance statistics including:

  • Wait events used to identify performance problems.
  • Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.
  • Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view.
  • Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.
  • Object usage statistics.
  • Resource intensive SQL statements.


     Active Session History (ASH) :

     The Name of this reports describe what is the major difference between it and AWR , The ASH contains recent information on active sessions sampled every second. The AWR are taken every one hour and its one hour old will not help in diagnosing issues that are current on the database. Typically to resolve issues urgenly on the database, details information pertaining o last 5 to 10 mins is critical. Because recording session activity is expensive, ASH samples V$SESSION every second and records the event for which he session are waiting.

    ASH information through V$active_session_history , you can can check this table from Oracle Documentation .

    As appear in the Documentation the View contain :
    It include

    * sql identifier of sql statement.
    * object no., file no., and block no.
    * wait event identifier & parameters.
    * user identifier, Session identifier and Serial number.
    * client identifier and name of the operating system program.

    Conclusion :

    ASH can help you when there’s a sudden performance degradation of the database felt.
    AWR – historic past snapshot intervals.

    AWR, stores the session performance statistics for analysis later.
    ASH – the storage is not persistent and as time progresses, the old entries are removed to accommodate new ones. They can be viewed using V$ACTIVE_SESSION_HISTORY.

    For More Information also about Baseline in AWR.
    Thank You
    Osama Mustafa 

    AWR Reports Types

    list from the home ($ORACLE_HOME/rdbms/admin) with (mostly) the one-line description from the start of file. I’ve put the most useful ones in the first list:

    Basic AWR reports
    Standrad SQL Statement Report
    Period Diff on current Instance
    Workload Repository Report Instance (RAC)
    AWR GLOBAL Report(RAC)
    Script to Output General AWR Information

     For most people the awrrpt.sql and awrsqrpt.sql are likely to be sufficient, but the “difference between two periods” can be very useful – especially if you do things like regularly forcing an extra snapshot at the start and end of the overnight batch so that you can (when necessary) find the most significant differences in behaviour between the batch runs on two different nights.

    If you get into the ‘RAC difference report’ you’ll need a very wide page – and very good eyesight !

    There are also a lot of “infrastructure and support” bits – some of the “input” files give you some nice ideas about how you can write your own code to do little jobs like: “run the most recent AWR report automatically”:


    AWR Baseline Migrate
    AWR LOAD: load awr from dump file
    AWR Extract
    Get inputs for diff report
    Workload Repository Compare Periods Report
    Get inputs for global diff reports
    Workload Repository Global Compare Periods Report
    AWR Global Input
    Workload Repository RAC (Global) Report
    AWR Input Name
    Get inputs for AWR report
    Workload Repository SQL Report Instance

    There are also a couple of deceptively named files that you might miss in 11.2:

    Server Performance AWR RAC report
    AWR IO Intensity Report
    Streams Performance Advisor report


     Thank you
    Osama Mustafa


    Understand AWR Report

    I mention earlier how to generate AWR , But after you did this how can you read it .

    1) The first thing to be checked in AWR report is the following:-

    Snap Id Snap Time Sessions Cursors/Session
    Begin Snap: 112 11-Jun-09 00:00:57 191 6.7
    End Snap: 113 11-Jun-09 01:00:11 173 7.4
    Elapsed: 59.23 (mins)
    DB Time: 710.73 (mins)

    Check the “DB Time” metric. If it is much higher than the elapsed time, then it indicates that the sessions are waiting for something.

    Here in this example, the Elapsed Time is around 60 minutes while the DB Time is around 700 minutes. This means that 700 minutes of time is spent by the sessions on waiting.

    2) Next thing to be looked is the following:-

    Instance Efficiency Percentages (Target 100%)
    Buffer Nowait %: 100.00 Redo NoWait %: 100.00
    Buffer Hit %: 98.67 In-memory Sort %: 100.00
    Library Hit %: 98.60 Soft Parse %: 99.69
    Execute to Parse %: 5.26 Latch Hit %: 99.31
    Parse CPU to Parse Elapsd %: 12.78 %Non-Parse CPU: 99.10 
    As per the thumb rule, Instance Efficieny Percentages should be ideally above 90%.
    3) Then comes the Shared Pool Statistics.
    Shared Pool Statistics
    Begin End
    Memory Usage %: 85.49 80.93
    % SQL with executions>1: 42.46 82.96
    % Memory for SQL w/exec>1: 47.77 81.03 

    The memory usage statistics of shared pool is shown.
    Idealy this should be lesser. If it is very high like beyond 90, this shows the contention in the shared pool.

    4) Next thing to be looked after is the Top 5 Timed Events table.

    This shows the most significant waits contributing to the DB Time.

    Top 5 Timed Events
    Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
    db file sequential read 4,076,086 28,532 7 66.9 User I/O
    CPU time 11,214 26.3
    Backup: sbtbackup 4 4,398 1,099,452 10.3 Administrative
    log file sync 37,365 2,421 65 5.7 Commit
    log file parallel write 37,928 1,371 36 3.2 System I/O 

    Here, the significant wait is the db file sequential read which contributes to 67% of DB Time.

    5) Then , SQL Statistics can be checked.

    SQL Statistics
    SQL ordered by Elapsed Time
    SQL ordered by CPU Time
    SQL ordered by Gets
    SQL ordered by Reads 

    SQL Statistics section would have commonly the above four sections.

    Each section shows the list of SQLs based on the order of the respective metric.
    For example, SQL ordered by Elapsed Time section shows the list of SQLs in the order
    of the Elapsed Time. High resource consuming SQLs can be spotted out and meant for

    Note: All the above four sections of SQL Statistics show the list of SQLs in descending order.
    i.e, For ex: Highest elapsed time is shown as first.

    6) Then comes the IO Stats section.

    This shows the IO Statistics for each tablespaces in the database.

    As the thumb rule, the Av Rd(ms) [Average Reads in milliseconds] should not cross beyond 30, add myself(not greater that 30)
    which is considered to be IO bottleneck.

    Tablespace IO Stats
    ordered by IOs (Reads + Writes) desc 
    Tablespace Reads Av Reads/s Av Rd(ms) Av Blks/Rd Writes Av Writes/s Buffer Waits Av Buf Wt(ms)
    TEMP 3,316,082 933 4.91 1.00 28,840 8 0 0.00
    DAT1 520,120 146 16.06 1.21 185,846 52 902 13.00
    DAT3 93,411 26 42.82 2.98 13,442 4 16 23.13
    DAT2 98,171 28 91.97 7.97 5,333 2 325 34.89 

    In the above example, the Av Rd(ms) is high in all tablespaces indicating the IO contention.

    7) Then , Advisory Statistics can be checked.

    This section shows the following:-

    Buffer Pool Advisory
    PGA Aggr Summary
    PGA Aggr Target Stats
    PGA Aggr Target Histogram
    PGA Memory Advisory
    Shared Pool Advisory
    SGA Target Advisory
    Streams Pool Advisory
    Java Pool Advisory 

    It is very commonly used to check the advisories for the most important SGA structures like shared pool, buffer cache etc and PGA.

    8) Then finally, init.ora Parameters is shown which shows the list of parameters set at instance level.

    init.ora Parameters

    All the above said sections except the DB Time can be checked from Statspack report also.

    The statspack snapshots are not generated automatically as in AWR.It has to be generated during the problem period as follows:-

    Take 2 snapshots between 60 minutes interval during the problem and generate the statspack report

    exec statspack.snap
    wait for 60 minutes
    exec statspack.snap

    Please run $ORACLE_HOME/rdbms/admin/spreport.sql
    and specify BEGIN and END ID’s of the snapshots taken during the problem.

    Some Other Links Will Be Useful :

    1-statspack examples.
    2-Analyaz statspack.
    3-Active Session History.
    4-Statspack Article.
    5-About Statspack.
    6-Using Statspack.
    7-AWR Reports

    Thank you
    Osama mustafa

    Steps to Generate AWR Reports

    Automatic Workload Repository

    The AWR is used to collect performance statistics including:

    • Wait events used to identify performance problems.
    • Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.
    • Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view.
    • Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.
    • Object usage statistics.
    • Resource intensive SQL statements.

    Step 1: Go to $ORACLE_HOME.

    Step 2: Once in $ORACLE_HOME go to cd rdbms and then cd admin.

    Step 3: Run command ‘ls –ltr awr*’.

    Step 4: Go to ‘sqlplus / as sysdba

    Step 5: In sqlplus the following is run ‘SQL> @awrrpt.sql. Select the format for the report as either ‘HTML’ or ‘TEXT’.

    Step 6: Select number of days you want to go back or just hit enter for listing all
    completed snapshots, if you press ENTER it will give you all snapshot .

    Step 7: Then specify Begin and End snapshot Ids.

    Step 8: Here you specify the name of the report or select the default name assigned.

    Exit Sql , And Search for your report_name in the Same Directory .

     Just As Note :

    @?/rdbms/admin/awrrpti.sql – Script can be used for the specified database and instance so the script in addition will prompt for dbid and instance number.

    @?/rdbms/admin/awrddrpt.sql – Script can be used to generate a combine report of 2 different snapshots for comparison of performance in one report so it will prompt for two pairs of snapshot snapshots to compare against.

    @?/rdbms/admin/awrinfo.sql – Script print AWR information like space usage by AWR, subset of AWR snap ids, Automatic Database Diagnostic Monitor (ADDM), Active Session History (ASH) information.

    @?/rdbms/admin/awrrpt.sql – It is an interactive script that can be used to generate HTML or text reports. The script prompts for type of report requested and prompts for number of days of snapshots to choose from so based on that it displays snap ids to choose the start and end snapshot it.

    The Next Post will be How to read AWR Tips .


    Osama Mustafa