Purging statistics from the SYSAUX tablespace

Whenever statistics in the dictionary are modified, old versions of statistics are saved automatically for future restoring. The old statistics are purged automatically at regular intervals based on the statistics history retention setting and the time of recent statistics gathering performed in the system. Retention is configurable using the ALTER_STATS_HISTORY_RETENTION procedure. The default value is 31 days.
That is normally not too big an issue but in our Peoplesoft environments we run a lot of gather_stats jobs and if the retention period is not managed then the SYSAUX tablespaces can grow very large. In one of our systems the SYSAUX tablespace was 37Gb with over 32Gb consisting of the stats tables and assoc iated indexes. This blog entry will provide the scripts to diagnose and correct excessive tablespace growth due to retained statistics

01 set linesize 120
02 set pagesize 100
03
04 COLUMN "Item" FORMAT A25
05 COLUMN "Space Used (GB)" FORMAT 999.99
06 COLUMN "Schema" FORMAT A25
07 COLUMN "Move Procedure" FORMAT A40
08
09 SELECT  occupant_name "Item",
10     space_usage_kbytes/1048576 "Space Used (GB)",
11     schema_name "Schema",
12     move_procedure "Move Procedure"
13 FROM v$sysaux_occupants
14 ORDER BY 1
15 /
Item                      Space Used (GB) Schema                    Move Procedure
------------------------- --------------- ------------------------- ----------------------------------------
AO                                    .00 SYS                       DBMS_AW.MOVE_AWMETA
EM                                    .08 SYSMAN                    emd_maintenance.move_em_tblspc
EM_MONITORING_USER                    .00 DBSNMP
EXPRESSION_FILTER                     .00 EXFSYS
JOB_SCHEDULER                         .00 SYS
LOGMNR                                .01 SYSTEM                    SYS.DBMS_LOGMNR_D.SET_TABLESPACE
LOGSTDBY                              .00 SYSTEM                    SYS.DBMS_LOGSTDBY.SET_TABLESPACE
ODM                                   .00 DMSYS                     MOVE_ODM
ORDIM                                 .00 ORDSYS
ORDIM/PLUGINS                         .00 ORDPLUGINS
ORDIM/SQLMM                           .00 SI_INFORMTN_SCHEMA
SDO                                   .00 MDSYS                     MDSYS.MOVE_SDO
SM/ADVISOR                            .02 SYS
SM/AWR                                .15 SYS
SM/OPTSTAT 11.44 SYS
SM/OTHER                              .02 SYS
STATSPACK                             .00 PERFSTAT
STREAMS                               .00 SYS
TEXT                                  .00 CTXSYS                    DRI_MOVE_CTXSYS
TSM                                   .00 TSMSYS
ULTRASEARCH                           .00 WKSYS                     MOVE_WK
ULTRASEARCH_DEMO_USER                 .00 WK_TEST                   MOVE_WK
WM                                    .00 WMSYS                     DBMS_WM.move_proc
XDB                                   .00 XDB                       XDB.DBMS_XDB.MOVEXDB_TABLESPACE
XSAMD                                 .00 OLAPSYS                   DBMS_AMD.Move_OLAP_Catalog
XSOQHIST                              .00 SYS                       DBMS_XSOQ.OlapiMoveProc

How long old stats are kept

1 select dbms_stats.get_stats_history_retention from dual;

Set retention of old stats to 10 days

1 exec dbms_stats.alter_stats_history_retention(10);

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

1 exec DBMS_STATS.PURGE_STATS(SYSDATE-10);

Show available stats that have not been purged

1 select dbms_stats.get_stats_history_availability from dual;

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

1 col Mb form 9,999,999
2 col SEGMENT_NAME form a40
3 col SEGMENT_TYPE form a6
4 set lines 120
5 select sum(bytes/1024/1024) Mb, segment_name,segment_type from dba_segments
6 where  tablespace_name = 'SYSAUX'
7 and segment_name like 'WRI$_OPTSTAT%'
8 and segment_type='TABLE'
9 group by segment_name,segment_type order by 1 asc
        MB SEGMENT_NAME                             SEGMEN
---------- ---------------------------------------- ------
         0 WRI$_OPTSTAT_OPR                         TABLE
         0 WRI$_OPTSTAT_AUX_HISTORY                 TABLE
        88 WRI$_OPTSTAT_TAB_HISTORY                 TABLE
       126 WRI$_OPTSTAT_IND_HISTORY                 TABLE
       158 WRI$_OPTSTAT_HISTGRM_HISTORY             TABLE
     4,482 WRI$_OPTSTAT_HISTHEAD_HISTORY            TABLE

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

01 col Mb form 9,999,999
02 col SEGMENT_NAME form a40
03 col SEGMENT_TYPE form a6
04 set lines 120
05 select sum(bytes/1024/1024) Mb, segment_name,segment_type from dba_segments
06 where  tablespace_name = 'SYSAUX'
07 and segment_name like '%OPT%'
08 and segment_type='INDEX'
09 group by segment_name,segment_type order by 1 asc
10 /
        MB SEGMENT_NAME                             SEGMEN
---------- ---------------------------------------- ------
         0 WRH$_OPTIMIZER_ENV_PK                    INDEX
         0 I_WRI$_OPTSTAT_OPR_STIME                 INDEX
         0 I_WRI$_OPTSTAT_AUX_ST                    INDEX
        88 I_WRI$_OPTSTAT_TAB_ST                    INDEX
       105 I_WRI$_OPTSTAT_IND_ST                    INDEX
       105 I_WRI$_OPTSTAT_H_ST                      INDEX
       195 I_WRI$_OPTSTAT_TAB_OBJ#_ST               INDEX
       213 I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST           INDEX
       214 I_WRI$_OPTSTAT_IND_OBJ#_ST               INDEX
     2,055 I_WRI$_OPTSTAT_HH_ST                     INDEX
     3,883 I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST            INDEX

Note that you cannot enable row movement and shrink the tables as the indexes are function based

1 alter table WRI$_OPTSTAT_IND_HISTORY enable row movement;
2 alter table WRI$_OPTSTAT_IND_HISTORY shrink space;
3 *
4 ERROR at line 1:
5 ORA-10631: SHRINK clause should not be specified for this object

 

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

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

alter table WRI$_OPTSTAT_TAB_HISTORY  move tablespace sysaux;
alter table WRI$_OPTSTAT_IND_HISTORY  move tablespace sysaux;
alter table WRI$_OPTSTAT_HISTHEAD_HISTORY  move tablespace sysaux;
alter table WRI$_OPTSTAT_HISTGRM_HISTORY  move tablespace sysaux;
alter table WRI$_OPTSTAT_AUX_HISTORY  move tablespace sysaux;
alter table WRI$_OPTSTAT_OPR  move tablespace sysaux;
alter table WRH$_OPTIMIZER_ENV  move tablespace sysaux;

Script to generate rebuild statements

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

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

1 select  di.index_name,di.index_type,di.status  from  dba_indexes di , dba_tables dt
2 where  di.tablespace_name = 'SYSAUX'
3 and dt.table_name = di.table_name
4 and di.table_name like '%OPT%'
5 order by 1 asc
6 /
SQL>
INDEX_NAME                     INDEX_TYPE                  STATUS
------------------------------ --------------------------- --------
I_WRI$_OPTSTAT_AUX_ST          FUNCTION-BASED NORMAL       VALID
I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST  FUNCTION-BASED NORMAL       VALID
I_WRI$_OPTSTAT_HH_ST           FUNCTION-BASED NORMAL       VALID
I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST FUNCTION-BASED NORMAL       VALID
I_WRI$_OPTSTAT_H_ST            FUNCTION-BASED NORMAL       VALID
I_WRI$_OPTSTAT_IND_OBJ#_ST     FUNCTION-BASED NORMAL       VALID
I_WRI$_OPTSTAT_IND_ST          FUNCTION-BASED NORMAL       VALID
I_WRI$_OPTSTAT_OPR_STIME       FUNCTION-BASED NORMAL       VALID
I_WRI$_OPTSTAT_TAB_OBJ#_ST     FUNCTION-BASED NORMAL       VALID
I_WRI$_OPTSTAT_TAB_ST          FUNCTION-BASED NORMAL       VALID
WRH$_OPTIMIZER_ENV_PK          NORMAL                      VALID

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

1 exec dbms_stats.alter_stats_history_retention(1);
2 select dbms_stats.get_stats_history_retention from dual;
        MB SEGMENT_NAME                             SEGMEN
---------- ---------------------------------------- ------
         0 WRI$_OPTSTAT_OPR                         TABLE
         0 WRI$_OPTSTAT_AUX_HISTORY                 TABLE
         3 WRI$_OPTSTAT_TAB_HISTORY                 TABLE
         4 WRI$_OPTSTAT_IND_HISTORY                 TABLE
         8 WRI$_OPTSTAT_HISTGRM_HISTORY             TABLE
       104 WRI$_OPTSTAT_HISTHEAD_HISTORY            TABLE

        MB SEGMENT_NAME                             SEGMEN
---------- ---------------------------------------- ------
         0 WRH$_OPTIMIZER_ENV_PK                    INDEX
         0 I_WRI$_OPTSTAT_OPR_STIME                 INDEX
         0 I_WRI$_OPTSTAT_AUX_ST                    INDEX
         2 I_WRI$_OPTSTAT_IND_ST                    INDEX
         2 I_WRI$_OPTSTAT_TAB_ST                    INDEX
         3 I_WRI$_OPTSTAT_TAB_OBJ#_ST               INDEX
         4 I_WRI$_OPTSTAT_IND_OBJ#_ST               INDEX
         5 I_WRI$_OPTSTAT_H_ST                      INDEX
         9 I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST           INDEX
        41 I_WRI$_OPTSTAT_HH_ST                     INDEX
        96 I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST            INDEX

I have also added another post on a similar theme. It may be useful
(Posted by John Hallas on http://jhdba.wordpress.com/)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s