sysaux usage
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: sysaux usage

  1. #1
    Join Date
    Jul 2002
    Posts
    335

    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...

  2. #2
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    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
    ---------------

  3. #3
    Join Date
    Jul 2002
    Posts
    335
    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
    ...

  4. #4
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    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;
    ---------------

  5. #5
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    there could probably a dbms_? package that could clean the OPTSTAT.
    ---------------

  6. #6
    Join Date
    Jul 2002
    Posts
    335
    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

  7. #7
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    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.
    -- Dilip

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width