AWR in Oracle

Automatic Workload Repository (AWR)(10g)

Before 8i utlbstat/utlestat
from 8i statspack
from 10g awr
from 11g ash

• performance gathering and reporting tools.
• The UTLBSTAT/UTLESTAT scripts were used to monitor performance metrics.
• 0racle 8i introduced the Statspack functionality which Oracle 9i extended.
• Oracle 10g statspack has evolved into the Automatic Workload Repository (AWR).

AWR Features:-

• Wait events used to identify performance problems.
• 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.
• top sql's
• db overload's

Snapshots.(DBA_HIST_WR_CONTROL)

statistics_level=typical/all/Basic

 AWR ill taken snapshot from every one hour and retention for 7 days.

 we can set like this:-
 begin
  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(retention => 43200,interval  => 30);
  end;
  /
we create a snapshot maually:-
EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

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

How to get AWR:-
step 1:-
@?/rdbms/admin/awrrpt.sql;
@$ORACLE_HOME/rdbms/admin/awrrpti.sql



Format
 the report format (html or text),

awrinfo.sql -get information about awr.
awrddrpt.sql -compare report
awrrpti.sql  -multi instance reports



Workload Repository Views
The following workload repository views are available:

V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled every second.
V$METRIC - Displays metric information.
V$METRICNAME - Displays the metrics associated with each metric group.
V$METRIC_HISTORY - Displays historical metrics.
V$METRICGROUP - Displays all metrics groups.
DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history.
DBA_HIST_BASELINE - Displays baseline information.
DBA_HIST_DATABASE_INSTANCE - Displays database environment information.
DBA_HIST_SNAPSHOT - Displays snapshot information.
DBA_HIST_SQL_PLAN - Displays SQL execution plans.
DBA_HIST_WR_CONTROL - Displays AWR settings.