Friday, June 21, 2013

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

No comments:

Post a Comment