sysaux usage

Just looking for a gentle prod in the right direction.

The biggest usage in my sysaux (10.2.0.3) tablespace are the following:

SM/OPTSTAT
SM/AWR

Now I've adjusted my awr retention policy and its taken affect (off topic but how often is this done and does MMom do it?).

Now I haven't figured out how to reduce the OPSTAT usage...

Like I said just a gentle prod...

when you have this query, the SM/OPSTAT is the highest consumer?
SELECT occupant_name, schema_name, move_procedure, space_usage_kbytes
FROM v$sysaux_occupants;
--inquiry

Yes, used

SELECT occupant_name AS name,
occupant_desc,
schema_name AS schema,
Decode (move_procedure_desc, '*** MOVE PROCEDURE NOT APPLICABLE ***','N',
'Y') AS Moveable,
space_usage_kbytes/1024 AS space_in_mb
FROM V$SYSAUX_OCCUPANTS
ORDER BY 3

Which gives (note space is in mb):

...
SM/OTHER Server Manageability - Other N 10.625
SM/OPTSTAT Server Manageability - Optimi N 675.5
SM/ADVISOR Server Manageability - Adviso N 95.9375
SM/AWR Server Manageability - Autom N 201.125
...

If I'm not wrong.. the OPTSTAT is the data stats related to the AWR, in which you have already cleaned. And so the statistics data can most probably be deleted with no harm.

try to find segments under WRI$_OPTSTAT* which consume more bytes:
SELECT segment_name,bytes
FROM dba_segments
WHERE segment_name in (
select table_name from dba_tables where tablespace_name = 'SYSAUX'
) order by bytes desc;

there could probably a dbms_? package that could clean the OPTSTAT.

Thanks, if is awr data and the retention policy isn't clearing up then we have a problem. Also the DBMS_WORKLOAD_REPOSITORY package can only drop snapshots (which I've tried and does indeed free space under SM/AWR).

I've opened a SR on this

Quote:

---

Originally Posted by bazza

Now I've adjusted my awr retention policy and its taken affect (off topic but how often is this done and does MMom do it?).

---

By default, MMON process takes snaps every one hour and retains for 7 days. You can see your current settings with,

select snap_interval, retention
from dba_hist_wr_control;

About snapshots not getting cleared, check if you have baseline defined on them. MMON does not purge baseline snapshots even after retention period. You then have to purge such snapshots using DBMS_WORKLOAD_REPOSITORY package.