Thinking Out Loud

November 16, 2010

How to modify AWR snapshot

Filed under: 11g,oracle — mdinh @ 4:39 am

Since I am using a third party application to monitor and to gather metrics for the database, it does not make sense to have AWR running as well.

It’s possible to modify the interval and retention for AWR.

AWR defaults:

SQL> @pr ‘select snap_interval,retention from dba_hist_wr_control’;
==============================
SNAP_INTERVAL : +00000 01:00:00.0
RETENTION : +00008 00:00:00.0
==============================

PL/SQL procedure successfully completed.

Modify AWR interval:

SQL> exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval => 0);

PL/SQL procedure successfully completed.

SQL> @pr ‘select snap_interval,retention from dba_hist_wr_control’;
==============================
SNAP_INTERVAL : +40150 00:00:00.0
RETENTION : +00008 00:00:00.0
==============================

PL/SQL procedure successfully completed.

Modify AWR retention:

SQL> exec dbms_workload_repository.modify_baseline_window_size(1);

PL/SQL procedure successfully completed.

SQL> exec dbms_workload_repository.modify_snapshot_settings(retention=>1440);

PL/SQL procedure successfully completed.

SQL> @pr ‘select snap_interval,retention from dba_hist_wr_control’;
==============================
SNAP_INTERVAL : +40150 00:00:00.0
RETENTION : +00001 00:00:00.0
==============================

PL/SQL procedure successfully completed.

The above information shows the retention period of 1 day and interval between snaphots is 40,150 days.

Reference:
>>Managing the Automatic Workload Repository

>> opens in new tab.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: