SYSAUX tablespace AWR issue.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: SYSAUX tablespace AWR issue.

  1. #1
    Join Date
    Aug 2012
    Posts
    9

    SYSAUX tablespace AWR issue.

    Hi,

    Oracle version 11.1.0.7.0. SYSAUX tablespace is constantly growing. Here are the occupants which are taking up space.

    OCCUPANT_NAME Space (M)
    ---------------------------------------------------------------- -----------
    SM/AWR 4,031.00
    SM/OPTSTAT 1,250.00

    I was able to take care of SM/OPTSTAT but I am having issues with SM/AWR. It grows everyday but I see only 3 snapshots even though retention period for snapshots is 8. I don't understand why the tables are so big when there are few snapshots and they keep growing. How should I go about this problem. I am new to Oracle and come from a DB2 background. what am I missing.

    select SNAP_ID,begin_interval_time,end_interval_time from WRM$_SNAPSHOT;

    SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME
    ------- ------------------------- -------------------------
    23363 31-JUL-12 03.22.44.202 AM 31-JUL-12 03.54.58.684 AM
    23365 01-AUG-12 03.23.33.122 AM 01-AUG-12 03.52.52.998 AM
    23364 31-JUL-12 03.54.58.684 AM 01-AUG-12 03.23.33.122 AM


    But select * from dba_hist_snapshot gives no rows.

    no rows selected

    Retention is 8 days.

    SQL> select * from DBA_HIST_WR_CONTROL;

    DBID SNAP_INTERVAL RETENTION TOPNSQL
    ----------- -------------------- -------------------- ------------------------------
    3246448383 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT

    Here are the sizes of the tables and all these tables have rows with Snapshots dating back to last year but the dba_history_snapshot has no info and returns 0 rows.


    WRH$_SEG_STAT SYSAUX 86
    WRH$_LATCH SYSAUX 92
    WRH$_SQLSTAT SYSAUX 104
    WRH$_PARAMETER SYSAUX 112
    WRH$_SYSSTAT SYSAUX 128
    WRH$_EVENT_HISTOGRAM SYSAUX 312
    WRI$_OPTSTAT_HISTGRM_HISTORY SYSAUX 343
    WRH$_SYSMETRIC_HISTORY SYSAUX 539
    WRH$_ACTIVE_SESSION_HISTORY SYSAUX 660

    Thanks,

    Kanas
    Last edited by kanas; 08-01-2012 at 06:12 PM. Reason: More Information

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Check your snapshot retention period - as a work around, purge undesired awr data.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Aug 2012
    Posts
    9
    Paul,

    Thanks for the reply. The retention period is set to 8 days. But a select on dba_hist_snapshot view returns 0 rows. But it the WRH$ tables I see snap_id going back to last year. Do you want to delete using dbms_workload_repository.drop_snapshot_range procedure. Please let me know.

    Thanks,

    kanas

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Please check if dbms_scheduler jobs are working AND please check if fgr$autopurge_job is enabled.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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