Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Wednesday, May 4, 2011

SHRINK SYSAUX AWR SPACE

SELECT occupant_name, schema_name, space_usage_kbytes/1024/1024 FROM v$sysaux_occupants;

OCCUPANT_NAME SCHEMA SIZE_IN_GB


SM/ADVISOR SYS 0.821045
SM/OPTSTAT SYS 1.971924
SM/AWR SYS 46.2876


Here clearly it shows AWR is occupying more than 46g. How can I reclaim this space back?

SQL> select * from DBA_HIST_WR_CONTROL;

DBID snap_interval retention topnsql
3861913925 +00 00:15:00.000000 +14 00:00:00.000000 DEFAULT

 shows retention as 14 days @ 15 mins time interval . Looks normal here so check further

If you ever want to change the retention then use below:

SQL> execute DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 5760);

Here 4320 is 4*24*60 ( Convert the days to minutes). For 3 days, it 5760 minutes.



SQL> select min(snap_id),MAX(snap_id) from dba_hist_snapshot;

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
4907 83193  Huge snapshot difference that means we have long history of snapshots which needs to be purged.

SQL> show parameter statistics

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL  should be typical.
timed_os_statistics integer 0
timed_statistics boolean TRUE


Thus at this time we can call this as the huge history of snapshots are stored in the database:

Use the below to purge the old snapshots and reclaim the space:

SQL> exec dbms_workload_repository.drop_snapshot_range(low_snap_id,high_snap_id);


To my surprise the drop snapshot is not dropping the snapshots:


SQL> exec dbms_workload_repository.drop_snapshot_range( 4907 , 9000);

PL/SQL procedure successfully completed.

SQL> select min(snap_id),MAX(snap_id) from dba_hist_snapshot;

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
4907 83197


SQL> select instance_number from v$instance;

INSTANCE_NUMBER
---------------
3

SQL> exec dbms_workload_repository.drop_snapshot_range( 4907 , 9000);


PL/SQL procedure successfully completed.


SQL> select min(snap_id),MAX(snap_id) from dba_hist_snapshot;

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
4907 83193


SQL> select min(snap_id),MAX(snap_id) from dba_hist_snapshot;

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
4907 83195

SQL> select instance_number from v$instance;

INSTANCE_NUMBER
---------------
2


SQL> exec dbms_workload_repository.drop_snapshot_range( 4907 , 9000);

PL/SQL procedure successfully completed.

SQL> select instance_number from v$instance;

INSTANCE_NUMBER
---------------
1


After running the drop snapshot I still see the low snap as the same:


select * from dba_hist_snapshot order by 1<-- indeed show 4907 was from 2008.
4907 3861913925 1 9/16/2008 2:40:41.000 AM 2/1/2009 10:00:49.693 PM 2/1/2009 11:00:02.760 PM +00 00:00:10.200000 1 0
4908 3861913925 1 9/16/2008 2:40:41.000 AM 2/1/2009 11:00:02.760 PM 2/2/2009 12:00:16.084 AM +00 00:00:09.900000 1 0
4909 3861913925 1 9/16/2008 2:40:41.000 AM 2/2/2009 12:00:16.084 AM 2/2/2009 1:00:28.861 AM +00 00:00:10.100000 1 0
4910 3861913925 1 9/16/2008 2:40:41.000 AM 2/2/2009 1:00:28.861 AM 2/2/2009 2:00:42.319 AM +00 00:00:09.700000 1 0

So started doing some research and found the below:



DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE Is Not Removing All Snapshots Data [ID 1218543.1]
Cause
This is not a bug but expected behavior, as oracle does not drop snapshots data associated with the baselines .

To overcome this:

Drop the baseline to clear the data for these snapshots.


SQL> select BASELINE_ID,START_SNAP_ID,END_SNAP_ID from WRM$_BASELINE
2 ;

BASELINE_ID START_SNAP_ID END_SNAP_ID
----------- ------------- -----------
2 4907 5192
3 4932 4956
11 6524 6548
12 6620 6645
1 4908 4932
4 4956 4980
5 4980 5004
6 5004 5028
7 5028 5052
8 5052 5076
9 5076 5139
10 5139 5215
14 5924 6867


SQL> EXECUTE DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE ( baseline_name => 'AWR_1234290129964',cascade=>true,dbid=>3861913925);

PL/SQL procedure successfully completed.


WOW,Now the min snapshot has been changed.

SQL> select min(snap_id),MAX(snap_id) from dba_hist_snapshot;

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
4908 83215


So I went ahead and deleted all the old baselines to purge the old AWR historic info and reclaimed all the space.

Good luck to you all:

1 comment:

Tahir Hussain said...

nice document! and i also appreciate that you put hadith :)

regards,
Tahir