AWR

Oracle Database Performance

AWR

Metalink Note:276103.1

STATISTICS_LEVEL initialization parameter must be set to the

TYPICAL or ALL to enable the Automatic Workload Repository


 

To Take a snapshot of the instance

EXECUTE dbms_workload_repository.create_snapshot();

Generate AWR Report

Option 1 : @$ORACLE_HOME/rdbms/admin/awrrpt.sql

Option 2:  @$ORACLE_HOME/rdbms/admin/awrrpti.sql  # useful in RAC enviornment

Drop snapshot from instance

exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 22,high_snap_id =>32, dbid =>3310949047);

Modify automatic snapshot setting

exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 43200,interval =>30, dbid =>3310949047); #retention is in mins

Take Baseline of database

BEGIN

DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (start_snap_id =>270,

end_snap_id =>280, baseline_name =>'peak baseline', dbid =>3310949047);

END;

/

View name : DBA_HIST_BASELINE

Drop Baseline snapshot

BEGIN

DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (baseline_name =>'peak baseline',cascade =>FALSE, dbid =>3310949047);

END;

/

Note : cascade : FALSE (don't drop snapshot)

Hope this help. Regards Rupam