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
/

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