Suggestions for SYSAUX Tablespace grows rapidly or too large:

Basic Diagnostics

A primary user of the SYSAUX tablespace is the Automatic Workload Repository. For general issues with SYSAUX a very useful report is the "awrinfo" report which  outputs not only general Automatic Workload Repository (AWR) information such as the size, data distribution, etc. in AWR and SYSAUX.  but also information about any object stored in SYSAUX. This report can be found under the Oracle Home directory in the rdbms/admin subdirectory. It can be executed as follows:

Troubleshooting Steps :

To  provide the timestamp until which date to be purged.

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS

---------------- ------------

SUPT             OPEN

SQL> show parameter statistics_level

SQL> show parameter sga_target

SQL> show parameter cluster_database

SQL> select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version from dba_registry order by comp_name;

SQL> select name,user from v$database;

SQL> select bytes/(1024*1024) usedspace,maxi/(1024*1024) total,(maxi-bytes)/(1024*1024)

freespace,(maxi-bytes)*100/maxi pctfre,a.tablespace_name from sm$ts_used a, (select

sum(decode(maxbytes,0,bytes,maxbytes)) maxi,tablespace_name

from dba_data_files group by tablespace_name) b where a.tablespace_name=b.tablespace_name and

a.tablespace_name like 'SYSAUX' ORDER by 4;

 USEDSPACE      TOTAL  FREESPACE     PCTFRE TABLESPACE_NAME

---------- ---------- ---------- ---------- ------------------------------

 54079.125      65792  11712.875 17.8028864 SYSAUX

set lines 113

set pages 10000

col TSname heading 'TSpace|Name|'

col TSname format a6

col FileID heading 'Data|File|ID'

col FileID format 9999

col FileName heading 'Data|File|'

col FileName format a100

SELECT

  tablespace_name as "TSname",

  file_id as "FileID",

  file_name as "FileName"

FROM dba_data_files

WHERE tablespace_name = 'SYSAUX';   

SpaceName  Data File ID   DataFile

------ ----- ------------   ----------------------------------

SYSAUX    76      /u01/oradata/SUPT/sysaux02.dbf

SYSAUX   120     /u02/oradata/SUPT/sysaux01.dbf

SYSAUX   151     /u03/oradata/SUPT/sysaux03.dbf

SQL> @$ORACLE_HOME/rdbms/admin/AWRINFO.sql

è SYSAUX tablespace grows much larger than expected

Automatic Workload Repository (AWR) is taking a significant amount of space (often 2Gb or more) as seen in the following select:

SQL> col OCCUPANT_NAME format a15

SQL> col OCCUPANT_NAME for a10;

SQL> col OCCUPANT_DESC for a55;

SQL> SELECT occupant_name,occupant_desc,space_usage_kbytes FROM v$sysaux_occupants WHERE occupant_name LIKE '%AWR%';

OCCUPANT_N OCCUPANT_DESC                                                    SPACE_USAGE_KBYTES

---------- ----------------------------------------      ------------------

SM/AWR  Server Manageability - Automatic Workload Repository           35384832

++++++++

set linesize 120 

 set pagesize 100 

COLUMN "Item" FORMAT A25 

COLUMN "Space Used (GB)" FORMAT 999.99 

COLUMN "Schema" FORMAT A25 

COLUMN "Move Procedure" FORMAT A40 

SELECT  occupant_name "Item", 

space_usage_kbytes/1048576 "Space Used (GB)", 

schema_name "Schema", 

move_procedure "Move Procedure" 

FROM v$sysaux_occupants 

ORDER BY 1 

 /

 Item     Space Used (GB) Schema      Move Procedure

--------------- ---------------             -------------------------

AO          .04 SYS       DBMS_AW.MOVE_AWMETA

EM          .12 SYSMAN    emd_maintenance.move_em_tblspc

SDO        .01 MDSYS     MDSYS.MOVE_SDO

SM/ADVISOR             .74 SYS

SM/AWR                     33.75 SYS

SM/OPTSTAT             17.95 SYS

SM/OTHER                 .04 SYS

XDB        09 XDB  XDB.DBMS_XDB.MOVEXDB_TABLESPACE

XSAMD     00 OLAPSYS   DBMS_AMD.Move_OLAP_Catalog

XSOQHIST   04 SYS    DBMS_XSOQ.OlapiMoveProc

à Show the new history level

a) Show the current stats retention configuration:

SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION

---------------------------

                         31

b) Show the current history level:

SQL> select dbms_stats.get_stats_history_availability from dual;

GET_STATS_HISTORY_AVAILABILITY

-----------------------------------------------------------

29-JAN-13 11.02.35.197761000 PM +01:00

è Show available stats that have not been purged

Show how big the tables are and rebuild after stats have been purged

SQL> col Mb form 9,999,999 

col SEGMENT_NAME form a40 

 col SEGMENT_TYPE form a6 

set lines 120 

select sum(bytes/1024/1024) Mb, segment_name,segment_type from dba_segments 

where  tablespace_name = 'SYSAUX' 

 and segment_name like 'WRI$_OPTSTAT%' 

 and segment_type='TABLE' 

group by segment_name,segment_type order by 1 asc

/

        MB SEGMENT_NAME               SEGMEN

---------- ----------------------------------       ------ ------

         0 WRI$_OPTSTAT_AUX_HISTORY              TABLE

         0 WRI$_OPTSTAT_SYNOPSIS_PARTGRP   TABLE

         0 WRI$_OPTSTAT_SYNOPSIS_HEAD$        TABLE

       104 WRI$_OPTSTAT_HISTGRM_HISTORY    TABLE

       289 WRI$_OPTSTAT_IND_HISTORY              TABLE

       393 WRI$_OPTSTAT_TAB_HISTORY              TABLE

     1,095 WRI$_OPTSTAT_OPR                               TABLE

     5,926 WRI$_OPTSTAT_HISTHEAD_HISTORY   TABLE

8 rows selected.

è Show how big the indexes are ready for a rebuild after stats have been purged

col Mb form 9,999,999 

col SEGMENT_NAME form a40 

col SEGMENT_TYPE form a6 

set lines 120 

select sum(bytes/1024/1024) Mb, segment_name,segment_type from dba_segments 

where  tablespace_name = 'SYSAUX' 

and segment_name like '%OPT%'  

and segment_type='INDEX' 

group by segment_name,segment_type order by 1 asc 

/

       MB SEGMENT_NAME                             SEGMEN

---------- ---------------------------------------- ------

         80 I_WRI$_OPTSTAT_H_ST                      INDEX

       168 I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST           INDEX

       224 I_WRI$_OPTSTAT_IND_ST                    INDEX

       240 I_WRI$_OPTSTAT_IND_OBJ#_ST               INDEX

       369 I_WRI$_OPTSTAT_TAB_ST                    INDEX

       461 I_WRI$_OPTSTAT_TAB_OBJ#_ST               INDEX

       675 I_WRI$_OPTSTAT_OPR_STIME                 INDEX

     3,464 I_WRI$_OPTSTAT_HH_ST                     INDEX

     4,890 I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST      INDEX

SQL> SELECT retention FROM dba_hist_wr_control;

RETENTION

----------------------------------------------------------

+00008 00:00:00.0

+00008 00:00:00.0
SQL> SELECT MIN(snap_id) FROM dba_hist_snapshot;


SQL> SELECT snap_id
FROM dba_hist_snapshot
WHERE begin_interval_time BETWEEN (sysdate - 14) -1/24 AND (sysdate - 14);

è Determine the largest object in the SYSAUX tablespace

SQL> Select * from v$sysaux_occupants;

-------------------------------------------------------

SM/AWR  Server Manageability - Automatic Workload Repository SYS

*** MOVE PROCEDURE NOT APPLICABLE ***                                      35384832

SM/OPTSTAT Server Manageability - Optimizer Statistics History SYS

*** MOVE PROCEDURE NOT APPLICABLE ***                                      18821760

è 1. Check how many partitions do exist for the offending table (1292724.1)

SQL> select table_name,partition_name from dba_tab_partitions where table_name = 'WRH$_ACTIVE_SESSION_HISTORY';

TABLE_NAME                     PARTITION_NAME

------------------------------ ----------------------

WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_356234088_0

WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_361538929_7944

WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_SES_MXDB_MXSN

  

è Check the V$SYSAUX_OCCUPANTS view to see if anything can be moved out of the SYSAUX tablespace

SQL> SELECT occupant_name,

  occupant_desc,

  move_procedure

FROM v$sysaux_occupants

WHERE move_procedure IS NOT NULL;

OCCUPANT_N OCCUPANT_DESC   MOVE_PROCEDURE

---------- ----------------------------   ----------------------

LOGMNR     LogMiner     SYS.DBMS_LOGMNR_D.SET_TABLESPACE

LOGSTDBY   Logical Standby  SYS.DBMS_LOGSTDBY.SET_TABLESPACE

AO    AnalyticalWorkspaceObject Table   DBMS_AW.MOVE_AWMET

SQL> select snap_id, begin_interval_time, end_interval_time

FROM

SYS.WRM$_SNAPSHOT

WHERE

snap_id = ( SELECT MIN (snap_id) FROM SYS.WRM$_SNAPSHOT)

UNION

SELECT

snap_id, begin_interval_time, end_interval_time

FROM

SYS.WRM$_SNAPSHOT

WHERE

snap_id = ( SELECT MAX (snap_id) FROM SYS.WRM$_SNAPSHOT);

 SNAP_ID BEGIN_INTERVAL_TIME  END_INTERVAL_TIME

---------- --------------------------------------   ----------------

 1            06-MAR-13 11.27.51.000 AM       06-MAR-13 12.00.40.670 PM

22544     02-MAR-13 09.00.08.943 PM      02-MAR-13 10.00.18.963 PM

++++++++++++++++++++++++++++++++++++

a) Disable automatic purge ( -1 = statistics history never purged by autopurge):

    à Set retention of old stats to 10 days

exec dbms_stats.alter_stats_history_retention(-1);

exec dbms_stats.alter_stats_history_retention(10);

b) Purge stats older than 10 days (best to do this in stages if there is a lot of data (sysdate-30,sydate-25 etc)

     à Purge by hand running successively:

exec dbms_stats.purge_stats(sysdate-&days);

 Action 1

exec DBMS_STATS.PURGE_STATS(SYSDATE-10);

Action 2

To do this in stages "the oldest snapshot date is "29-JAN-13"

exec DBMS_STATS.PURGE_STATS(SYSDATE-70);

exec DBMS_STATS.PURGE_STATS(SYSDATE-60);

exec DBMS_STATS.PURGE_STATS(SYSDATE-50);

exec DBMS_STATS.PURGE_STATS(SYSDATE-40);

exec DBMS_STATS.PURGE_STATS(SYSDATE-31);

Note :-  That you cannot enable row movement and shrink the tables as the indexes are function based

è Check the retention period is appropriate

Set retention period to a shorter duration

SQL> exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention=>5760);

Since the retention period parameter value is defined in minutes, 4 days is 5760 minutes (4 x 24 x 60 = 5760). The minimum retention periof is 1 day.

è Check for orphaned ASH rows using the following statement:

SQL> SELECT COUNT(1) Orphaned_ASH_Rows

FROM wrh$_active_session_history a

WHERE NOT EXISTS

  (SELECT 1

  FROM wrm$_snapshot

  WHERE snap_id       = a.snap_id

  AND dbid            = a.dbid

  AND instance_number = a.instance_number);

ORPHANED_ASH_ROWS

-----------------

         67874329

Note :- If the value is larger than zero then you should delete the orphan rows.

Delete 'orphaned' Active Session History (ASH)

SQL> DELETE

FROM wrh$_active_session_history a

WHERE NOT EXISTS

  (SELECT 1

  FROM wrm$_snapshot

  WHERE snap_id       = a.snap_id

  AND dbid            = a.dbid

  AND instance_number = a.instance_number);

è Run the rebuild table commands – note that this does cause any gather_stats jobs to fail

SQL> select 'alter table '||segment_name||'  move tablespace SYSAUX;' from dba_segments where tablespace_name = 'SYSAUX' and segment_name like '%OPT%' and segment_type='TABLE' ;

  

è Script to generate rebuild statements

SQL> select 'alter index '||segment_name||'  rebuild online parallel (degree 14);' from dba_segments where tablespace_name = 'SYSAUX' and segment_name like '%OPT%' and segment_type='INDEX' ;

SQL> select 'alter index '||index_name||' rebuild online parallel (degree 14);' from

dba_indexes where tablespace_name = 'SYSAUX' and table_name in

('WRI$_OPTSTAT_HISTHEAD_HISTORY','WRH$_SQL_PLAN','WRH$_SYSMETRIC_HISTORY','WRI$_OPTSTAT_HISTGRM_HIST

ORY','WRI$_OPTSTAT_IND_HISTORY','WRI$_OPTSTAT_OPR','WRI$_ADV_SQLT_PLANS','WRI$_OPTSTAT_TAB_HISTORY',

'WRH$_SYSMETRIC_SUMMARY');

  

è Once completed it is best to check that the indexes (indices) are usable

SQL> select  di.index_name,di.index_type,di.status  from  dba_indexes di , dba_tables dt 

where  di.tablespace_name = 'SYSAUX' 

and dt.table_name = di.table_name 

and di.table_name like '%OPT%' 

order by 1 asc 

/

 Finally lets see what space has been saved with a retention date of 1 day and a gather schema stats for the SYSASDM schema

SQL> exec dbms_stats.alter_stats_history_retention(1); 

 +++++++

è 1. Check how many partitions do exist for the offending table (1292724.1)

SQL> select table_name,partition_name from dba_tab_partitions where table_name = 'WRH$_ACTIVE_SESSION_HISTORY';

è 2. Try to force the execution of the purge operations :

SQL> alter session set "_swrf_test_action" = 72;

è 3. Drop snapshots manually with small range.

SQL> DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(

low_snap_id IN NUMBER,

high_snap_id IN NUMBER

dbid IN NUMBER DEFAULT NULL);

 - or -

SQL> exec dbms_stats.purge_stats(sysdate-&days);

4. If the drop snapshot did not finish or consume for UNDO space. Terminate the session and wait for a day or two the default auto purge job should automatically drop the old snapshots.

== --- === ---

Once the rows have been deleted, you can shrink the WRH$_ACTIVE_SESSION_HISTORY table to reclaim the freed space (since row movement is enabled on WRH$_ACTIVE_SESSION_HISTORY by default):

alter table WRH$_ACTIVE_SESSION_HISTORY shrink space;

You can then re-check to see if the space used by AWR is reduced as follows:

column OCCUPANT_NAME format a15

SQL> SELECT occupant_name,

  occupant_desc,

  space_usage_kbytes

FROM v$sysaux_occupants

WHERE occupant_name LIKE '%AWR%';

+++++++++++++++++++++
   want to delete dba_hist_snapshot to see which snap id range :
SQL>EXEC DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(12626, 16699);

+++++++++++++++++++++

Run the following SQL to list which Segments are using the more space in the SYSAUX TableSpace

SQL> set lines 130

set pages 10000

col SgmntSize heading 'Sgmnt|Size|Mb'

col SgmntSize format 99999

col TSname heading 'TSpace|Name|'

col TSname format a25

col SgmntOwner heading 'Sgmnt|Owner|'

col SgmntOwner format a15

col SgmntName heading 'Sgmnt|Name|'

col SgmntName format a35

col SgmntType heading 'Sgmnt|Type|'

col SgmntType format a5

SELECT

  ROUND(SUM(ds.bytes)/1024/1024,0) as "SgmntSize",

  ds.TableSpace_name as "TSname",

  ds.owner as "SgmntOwner",

  ds.segment_name as "SgmntName",

  ds.segment_type as "SgmntType"

FROM dba_segments ds

WHERE ds.segment_type IN ('TABLE','INDEX')

AND TableSpace_name = 'SYSAUX'

GROUP BY

  ds.TableSpace_name,

  ds.owner,

  ds.segment_name,

  ds.segment_type

ORDER BY "SgmntSize" DESC;

Sgmnt  TSpace                    Sgmnt           Sgmnt                               Sgmnt

SizeMb Name                      Owner           Name                                Type

   ------ ------------------------- --------------- ---------------------- -----

  5926 SYSAUX   SYS  WRI$_OPTSTAT_HISD_HISTORY  TABLE

  4890 SYSAUX   SYS  I_WRI$_OPTST_HH_OBJ_ICOL_ST    INDEX

  3464 SYSAUX   SYS  I_WRI$_OPTSTAT_HH_ST   INDEX

  1095 SYSAUX   SYS  WRI$_OPTSTAT_OPR          TABLE

  1093 SYSAUX   SYS  WRH$_SYSMETRIC_HISTORY    TABLE

   911 SYSAUX    SYS  WRH$_SQL_PLAN               TABLE

++++++++++++++++

è Check the indexes are in usable state

SQL> select index_name,index_type,status from dba_indexes where tablespace_name = 'SYSAUX' and

table_name in

('WRI$_OPTSTAT_HISTHEAD_HISTORY','WRH$_SQL_PLAN','WRH$_SYSMETRIC_HISTORY','WRI$_OPTSTAT_HISTGRM_HIST

ORY','WRI$_OPTSTAT_IND_HISTORY','WRI$_OPTSTAT_OPR','WRI$_ADV_SQLT_PLANS','WRI$_OPTSTAT_TAB_HISTORY',

'WRH$_SYSMETRIC_SUMMARY');

è Check for and rebuild any unusable index on the table after the LOB move:

SQL> select index_name from dba_indexes where table_name='WRH$_SQLTEXT' and status='UNUSABLE';

SQL> alter index <Index_name>  rebuild;

 Note: Verify as above all steps completed include rebuild, then check the size of  sysaux tablespace  ....

SQL> select * from
(select owner,segment_name||'~'||partition_name segment_name,bytes/(1024*1024) size_m
 from dba_segments
 where tablespace_name = 'SYSAUX'
 ORDER BY BLOCKS desc)
where rownum < 40;

SQL> select bytes/(1024*1024) usedspace,maxi/(1024*1024) total,(maxi-bytes)/(1024*1024)

freespace,(maxi-bytes)*100/maxi pctfre,a.tablespace_name from sm$ts_used a, (select

sum(decode(maxbytes,0,bytes,maxbytes)) maxi,tablespace_name

from dba_data_files group by tablespace_name) b where a.tablespace_name=b.tablespace_name and

a.tablespace_name like 'SYSAUX' ORDER by 4;

+++++++++ Manjunatha ++++++++++++++++

1399365.1, 552880.1,1292724.1,287679.1,387914.1

+++++++++++++++++++++++++++++++++