AWR in Oracle

Display a list of snapshots

set lines 100 pages 999
select snap_id
, snap_level
, to_char(begin_interval_time, 'dd/mm/yy hh24:mi:ss') begin
from dba_hist_snapshot
order by 1
/

Produce a report

@?/rdbms/admin/awrrpt.sql

To see the snapshot interval and retention period

col snap_interval format a30
col retention format a30
select snap_interval
, retention
from dba_hist_wr_control
/

Change the snapshot interval

Note. This example changes it to 30 minutes
exec dbms_workload_repository.modify_snapshot_settings (interval => 30)

Change the retention period

Note. This example changes it to two weeks (14 days)
exec dbms_workload_repository.modify_snapshot_settings (retention => 14*24*60)

Manually take a snapshot

exec dbms_workload_repository.create_snapshot

List all baselines

set lines 100
col baseline_name format a40
select baseline_id
, baseline_name
, start_snap_id
, end_snap_id
from dba_hist_baseline
order by 1
/

Create a baseline

exec dbms_workload_repository.create_baseline (, ,'')

Remove a baseline

exec dbms_workload_repository.drop_baseline('')

Enable/Disable automatic snapshots

Note. This job is enabled by default
exec dbms_scheduler.enable('GATHER_STATS_JOB')

and to disable...
exec dbms_scheduler.disable('GATHER_STATS_JOB')

Time model queries

System time model
set lines 100 pages 999
select stat_name
, value
from v$sys_time_model
order by value desc
/

Session time model
set lines 100 pages 999
select stat_name
, value
from v$sess_time_model
where sid = '&sid'
order by value desc
/

Produce an Active Session History (ASH) report

@?/rdbms/admin/ashrpt.sql

2. Generate AWR reports for any instance in the cluster:
The script will ask you for the instance number in output format.
$ cd $ORACLE_HOME/rdbms/admin/
$ sqlplus sys as sysdba
SQL> @awrrpti.sql

From 11gr2 RAC We hav following options available:-
1.Generate AWR Cluster aggregated statistics from all the instances:
This report shows the aggregated statistics from the whole cluster database also, described as a global AWR cluster report
$ cd $ORACLE_HOME/rdbms/admin/
$ sqlplus sys as sysdba
SQL> @awrgrpt.sql

2. Generate AWR Global Cluster Difference Report:
Ths report compares the statistic results of differences between two different snapshot intervals, for the whole cluster database
$ cd $ORACLE_HOME/rdbms/admin/
$ sqlplus sys as sysdba
SQL> @awrgdrpt.sql