AUTOMATIC WORKLOAD REPOSITORY – AWR

 

AUTOMATIC WORKLOAD REPOSITORY – AWR

A list of the 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

/

REPORT QUERY

$ORACLE_HOME/rdbms/admin/awrrpt.sql

The retention and interval time settings

col snap_interval format a30

col retention format a30

select snap_interval

, retention

from dba_hist_wr_control;

Changing the interval settings

exec dbms_workload_repository.modify_snapshot_settings

 (interval => 30) -- Gets a snapshot every 30 mins

Changing the retention periods

exec dbms_workload_repository.modify_snapshot_settings (retention => 10*24*60) -- Set to 10 days

Taking a snapshot manually

exec dbms_workload_repository.create_snapshot

Listing all the 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

/

Creating a baseline

exec dbms_workload_repository.create_baseline (<start snap>, <endsnap>,'<name>')

Deleting a baseline

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

Activating the default snapshot (active by default)

exec dbms_scheduler.enable('GATHER_STATS_JOB')

Deactivating the default snapshot (active by default)

exec dbms_scheduler.disable('GATHER_STATS_JOB')

The system time model

set lines 100 pages 999

select stat_name

, value

from v$sys_time_model

order by value desc

/

The session time model

set set lines 100 pages 999

select stat_name

, value

from v$sess_time_model

where sid = '&sid'

order by value desc

/

Active Session History (ASH) report, identifying active sessions and information about them

@$ORACLE_HOME/rdbms/admin/ashrpt.sql