Tag: osama mustafa oracle

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 :

BEGIN
  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
    retention => 43200,        -- Minutes (= 30 Days). Current value retained if NULL.
    interval  => 30);          -- Minutes. Current value retained if NULL.
END;
/

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 :

EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

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

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