Home » RDBMS Server » Enterprise Manager » SYSAUX space - AWR usage (RAC 10.2.0.3, Red-Hat ES4)
SYSAUX space - AWR usage [message #362300] Mon, 01 December 2008 14:09 Go to next message
DriveShaft
Messages: 20
Registered: August 2007
Junior Member
RAC 10.2.0.3 running on Red-Hat ES4.

SYSAUX tablespace has been slowly growing over the past few months. When it hit 90% capacity, I began to look into what was causing this growth. AWR is set to run every hour and keep 7 days worth of stats. Assuming that this was the majority of what was held in the AWR, I decided to try and delete a majority of the snapshots to see if the space would decrease. I deleted all but 2 days worth of snapshots and the space decreased by no more than 5%. So, that has me questioning what is taking up almost 6 Gigs worth of space in the AWR?

There seems to be LOB's that are consuming almost 4 Gigs of the space.

SQL> select owner||'.'||table_name "TABLE", column_name, segment_name from all_lobs where segment_name='SYS_LOB0000008938C00038$$';

TABLE             COLUMN_NAME        SEGMENT_NAME
--------------------------------------------------------
SYS.WRH$_SQL_PLAN  OTHER_XML   SYS_LOB0000008938C00038$$

SQL> select owner||'.'||table_name "TABLE", column_name, segment_name from all_l obs where segment_name='SYS_LOB0000008932C00004$$';

TABLE            COLUMN_NAME       SEGMENT_NAME
-----------------------------------------------------
SYS.WRH$_SQLTEXT  SQL_TEXT  SYS_LOB0000008932C00004$$ 


The SQL_PLAN table has 7,853,984 Rows. I can see the large CLOB's in the OTHER_XML column. Looking at the first 2000 Rows, I can see that the SNAP_ID's that most of the data is for, are old and purged out snapshots? It seems as if this table is not being purged with the 7 day retention policy?

The same thing goes for the SQL_TEXT table. I can see the large CLOB's in the SQL_TEXT column. I also see SNAP_ID's that are for old snapshots. One thing that sticks out here is that I see a few SNAP_ID's for snapshots that do not exist yet. My highest snapshot at this moment is 13,755 and I see SNAP_ID's of 13,805 in the SNAP_ID column?

So, I am curious as to why this data is being kept for well over 1 year, and why it has SNAP_ID's that do not exist yet?

DriveShaft
Re: SYSAUX space - AWR usage [message #365333 is a reply to message #362300] Thu, 11 December 2008 09:29 Go to previous message
DriveShaft
Messages: 20
Registered: August 2007
Junior Member
Would anyone know possibly how to purge this history or what retention it uses? SQL from May 2007 seems pretty old to be keeping around.

DS
Previous Topic: Alert log errors notification
Next Topic: Oracle Manager
Goto Forum:
  


Current Time: Thu Mar 28 11:42:20 CDT 2024