@?/rdbms/admin/awrrpt.sql
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
/
Change the snapshot interval
col
snap_interval format a30
col
retention format a30
select
snap_interval
,
retention
from
dba_hist_wr_control
/
changes it to 10 minutes
exec
dbms_workload_repository.modify_snapshot_settings (interval => 10)
Change the retention period
changes it to 10 days
exec
dbms_workload_repository.modify_snapshot_settings (retention => 10*24*60)
AWR
BASELINE
Creating
a baseline allowed a specified range of snapshots to be retained, regardless of
the AWR retention policy
The
main purpose of a baseline is to preserve typical runtime statistics in the AWR
repository, allowing you to run the AWR snapshot reports on the preserved
baseline snapshots at any time and compare them to recent snapshots contained
in the AWR. This allows you to compare current performance to established
baseline performance, which can assist in determining database performance
problems.
Create a baseline
exec dbms_workload_repository.create_baseline (, ,'')
Remove a baseline
exec dbms_workload_repository.drop_baseline(''')
11G Enhancement
Time-expired baselines - start and end times.
Procedures
and functions allowing baselines to be created using start and end times, which
are used to estimate the relevant snapshot IDs. The functions have the same
parameter lists as the procedures, but return the baseline ID. By default
baselines are kept forever, but the new expiration parameter allows them to be
automatically expired after specified number of days
The Moving Window Baseline
Uses
the system_moving_window to incorporate adaptive thresholds, whereby
the bases changes as does the typical workload.
The system_moving_window corresponds to the retention tine window for
the existing baselines, and the total time span is controlled by the dbms_workload_repository.modify_baseline_window_size procedure
that allows you to create adaptive threshold baselines for longer periods (like
monthly workload cycles).
