| SYSAUX space - AWR usage [message #362300] |
Mon, 01 December 2008 14:09  |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
