Performance - AWR report

Performance - AWR

Display a list of snapshots

Produce a report

To see the snapshot interval and retention period

Change the snapshot interval

Change the retention period

Manually take a snapshot

List all baselines

Create a baseline

Remove a baseline

Enable/Disable automatic snapshots

Time model queries

Produce an Active Session History (ASH) report

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

/

You can see what snapshots are currently in the AWR by using the DBA_HIST_SNAPSHOT view as seen in this example:

SELECT snap_id, to_char(begin_interval_time,'dd/MON/yy hh24:mi') Begin_Interval,

to_char(end_interval_time,'dd/MON/yy hh24:mi') End_Interval

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 (<start snap>, <endsnap>,'<name>')

Remove a baseline

 exec dbms_workload_repository.drop_baseline('<baseline name>')

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