BASIC - for manual snapshots
dba_hist_snapshot - for existing snapshots
dba_hist_baseline - for viewing baseline settings
awrrpt.sql - for Statspack style report (either text or html format)
awrrpti.sql - for single instance in RAC
EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;
BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (
low_snap_id => 22,
high_snap_id => 32);
END;
BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
retention => 43200, -- Minutes (= 30 Days). Current value retained if NULL.
interval => 30); -- Minutes. Current value retained if NULL.
END;
Setting baselines:
BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_baseline (
baseline_name => 'batch baseline',
cascade => FALSE); -- Deletes associated snapshots if TRUE.
END;
This example creates a baseline (named 'batch baseline') between snapshots 105 and 107 for the local database:
EXECUTE DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (start_snap_id => 105,
end_snap_id => 107,
baseline_name => 'batch baseline');
Following views gets affected by AWR snapshots
v$active_session_history - Displays the active session history (ASH) sampled every second.
v$metric - Displays metric information.
v$metricname - Displays the metrics associated with each metric group.
v$metric_history - Displays historical metrics.
v$metricgroup - Displays all metrics groups.
dba_hist_active_sess_history - Displays the history contents of the active session history.
dba_hist_baseline - Displays baseline information.
dba_hist_database_instance - Displays database environment information.
dba_hist_snapshot - Displays snapshot information.
dba_hist_sql_plan - Displays SQL execution plans.
dba_hist_wr_control - Displays AWR settings.
Another interesting article on AWR, Time Model, Active Session History, Baseline, Snapshots is in http://www.rampant-books.com/art_nanda_awr.htm
No comments:
Post a Comment