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
|
