RDBMS -- SYSAUX tablespace full, huge sized "WRI$_OPTSTAT_HISTHEAD_HISTORY" , DBMS_STATS.PURGE_ALL demo and shrink tablespace

You may encounter a tablespace problem in SYSAUX tablespace.
Actually, altough you don't have a disk size problem, you may have an unnecessarly big sized SYSAUX tablespace and you may not pay attention to.
In this blog post, I will explain one of the most likely causes of having a big sized SYSAUX tablespace, and also try to explain how to get rid of them, by purging the related content from the Oracle Database.

The things is that , the occupied space in SYSAUX tablespace is mostly caused by optimizer history statistics tables.

Here is an example output produced, when we query and sort the objects residing in the SYSAUX tablespace by their sizes;

TABLESPACE_NAME              SEGMENT_NAME                                     MB

SYSAUX                         WRI$_OPTSTAT_HISTHEAD_HISTORY            47,264
SYSAUX                         I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST              42,150
SYSAUX                         I_WRI$_OPTSTAT_HH_ST                                22,497

As seen, in the first place there is a object statistics history related table and the follower are the related indexes.

The sizes are huge (considering this is an Oracle EBS database sized almost 1 TB and have only few customizations)

Also, altough this size of statistics history can be created because of frequent statistics collection, it is not acceptable.

In fact, MMON process should purge these statistics accordint to the default retention, which is 31 days but it seem it can not. MMON performs these purge activities automatically, but it has a limit of 5 minutes to perform these activities. So if the purging takes more than 5 mins, then the activities are aborted and as a result the stats are not purged.

There are several bugs records about this situation already:

Document 10279045.8 Slow Statistics purging (SYSAUX grows)
Document 8553944.8 SYSAUX tablespace grows
Document 14373728.8 Bug 14373728 - Old statistics not purged from SYSAUX tablespace
Document 11869207.8 Improvements to archived statistics purging / SYSAUX tablespace grows

The similar issue is adressed in Oracle Support document :"SYSAUX Grows Because Optimizer Stats History is Not Purged (Doc ID 1055547.1)"

There are 3 things to do for getting rid of this size, actually to purge the optimizer stats history records.

1) Applying patch 14373728, which superseded 11869207 'i supersede ediyor. The patch 11869207 was faulty anyways. The new patch:14373728, which is an OPTIMIZER overlay patch, can be applied.. This patch is for MMON, the automatic purge.

2) If we can't apply the patch  14373728, we can go ahead and purge the optimizer statistics manually;

begin
for i in reverse 10..100
loop
dbms_stats.purge_stats(sysdate-i);
end loop;
end;
/

This takes time? Then let's see our 3rd solution alternative;

3)

If the patch 1027045 (the related option comes with this patch) is already applied , we can go ahead and use DBMS_STATS.PURGE_ALL to purge all our historical objects statistics. DBMS_STATS.PURGE_ALL just truncates the related table, so all purge activity takes only 5-10 seconds to finish.

The tradeoff of this type of purging can be the inability to make forensic sql performance analysis and corrections accordingly, but in most of the customer environments these operations are done quire rare right? :)

Following is a demo of running DBMS_STATS with PURGE_ALL argument;

Note that, we also change the retention period from the default(31 days) to 15 days.

Initially, the tables are fully loaded. The SYSAUX tablespace is 41 gigs(which is used almost fully) and the WRI$_OPTSTAT_HISTHEAD_HISTORY s almost 14 gigs as seen below;

SQL> set linesize 1000;

SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION

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

                         31

SQL> select dbms_stats.get_stats_history_availability from dual;

GET_STATS_HISTORY_AVAILABILITY

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

23-JUL-16 12.23.12.224357000 PM +03:00

SQL> select * from (select owner, segment_name, bytes/(1024*1024) from dba_segments where owner='SYS' order by bytes desc) where rownum <10;

OWNER                 SEGMENT_NAME                                                                 BYTES/(1024*1024)

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

SYS                      WRI$_OPTSTAT_HISTHEAD_HISTORY                                       14863

SYS                       I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST                                         11179

SYS                       I_WRI$_OPTSTAT_HH_ST                                                          7074

SYS                       SOURCE$                                                                                  5481.64063

SYS                       I_SOURCE1                                                                               4280.03125

SYS                       IDL_UB1$                                                                                   2931.52344

SYS                       I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST                                        2224

SYS                       WRI$_OPTSTAT_HISTGRM_HISTORY                                        1473

SYS                       IDL_UB2$                                                                                   1087.52344

SQL> select nvl(df.tablespace_name, nvl(fs.tablespace_name,'UNKOWN')) name,

  2         to_char(kbytes_alloc,'999,999,990') kbytes,

  3         to_char(kbytes_alloc-nvl(kbytes_free,0),'999,999,990') used,

  4         to_char(nvl(kbytes_free,0),'999,999,990') free,

  5         to_char(((kbytes_alloc-nvl(kbytes_free,0))/kbytes_alloc)*100,'990.9999') pct_used,

  6         to_char(nvl(largest,0),'999,999,990') largest

  7  from ( select sum(bytes)/1024 Kbytes_free,

  8                max(bytes)/1024 largest,

  9                tablespace_name

 10         from   dba_free_space

 11         where  tablespace_name='SYSAUX'

 12         group by tablespace_name ) df,

 13       ( select sum(bytes)/1024 Kbytes_alloc,

 14                tablespace_name

 15         from   dba_data_files

       where  tablespace_name='SYSAUX'

 16   17         group by tablespace_name ) fs;

NAME                           KBYTES       USED            FREE         PCT_USED  LARGEST

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

SYSAUX                           41,943,040   41,878,016       65,024   99.8450          960

NOW WE PURGE!!!

SQL> exec DBMS_STATS.PURGE_STATS(DBMS_STATS.PURGE_ALL);

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION

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

                         31

SQL> select dbms_stats.get_stats_history_availability from dual;

GET_STATS_HISTORY_AVAILABILITY

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

24-AUG-16 11.16.26.195234000 AM +03:00

SQL> select * from (select owner, segment_name, bytes/(1024*1024) from dba_segments where owner='SYS' order by bytes desc) where rownum <10;

OWNER                          SEGMENT_NAME                                                          BYTES/(1024*1024)

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

SYS                            SOURCE$                                                                                 5481.64063

SYS                            I_SOURCE1                                                                              4280.03125

SYS                            IDL_UB1$                                                                                 2931.52344

SYS                            IDL_UB2$                                                                                 1087.52344

SYS                            ARGUMENT$                                                                            1011.33594

SYS                            _SYSSMU9_2885769297$                                                          849.125

SYS                            _SYSSMU2_735814084$                                                            839.125

SYS                            _SYSSMU4_179149818$                                                            839.125

SYS                            _SYSSMU8_751394697$                                                            836.125

9 rows selected.

SQL> select nvl(df.tablespace_name, nvl(fs.tablespace_name,'UNKOWN')) name,

  2         to_char(kbytes_alloc,'999,999,990') kbytes,

  3         to_char(kbytes_alloc-nvl(kbytes_free,0),'999,999,990') used,

  4         to_char(nvl(kbytes_free,0),'999,999,990') free,

  5         to_char(((kbytes_alloc-nvl(kbytes_free,0))/kbytes_alloc)*100,'990.9999') pct_used,

  6         to_char(nvl(largest,0),'999,999,990') largest

  7  from ( select sum(bytes)/1024 Kbytes_free,

  8                max(bytes)/1024 largest,

  9                tablespace_name

       from   dba_free_space

 10   11         where  tablespace_name='SYSAUX'

 12         group by tablespace_name ) df,

 13       ( select sum(bytes)/1024 Kbytes_alloc,

 14                tablespace_name

 15         from   dba_data_files

 16         where  tablespace_name='SYSAUX'

 17         group by tablespace_name ) fs;

NAME                           KBYTES       USED         FREE         PCT_USED  LARGEST

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

SYSAUX                           41,943,040    1,944,960   39,998,080    4.6371    1,434,624

"After the purge we  have 39G free ..."

Now, we change the retention-->

SQL> exec dbms_stats.alter_stats_history_retention(15);

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION

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

                         15

"After the purge we  have 3.5 MB for optimizer statistics history related objects..."

SQL> select occupant_desc, space_usage_kbytes from v$sysaux_occupants where OCCUPANT_DESC like '%Statistics%';

OCCUPANT_DESC                                                    SPACE_USAGE_KBYTES

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

Server Manageability - Optimizer Statistics History                            3584

So, with this demo, we have seen how to empty the SYSAUX tablespace by purging the optimizer statistics history table, so what 's next; I say you what... Now we need to resize our SYSAUX tablespace by giving back the newly emerged free space.

In order to do this; we need to shrink or move the tables and makes sure the continous empty space is configured to be in the end of the datafiles of the SYSAUX tablespace..

So in order to accomplish this, we can follow use the Tanel Poder ' s script. This script will check the current situation of the datafiles and generate the recuired alter operation statements for resizing the database ..

Ref: http://blog.tanelpoder.com/files/scripts/trim_database.sql

with query as ( select /*+ NO_MERGE MATERIALIZE */ file_id, tablespace_name, max(block_id + blocks) highblock from dba_extents group by file_id, tablespace_name ) select 'alter database datafile '|| q.file_id || ' resize ' || ceil ((q.highblock * t.block_size + t.block_size)/1024) || 'K;' cmd from query q, dba_tablespaces t where q.tablespace_name = t.tablespace_name;

But if the free space is not in the end of the datafiles, this script is useless . So in that case, we need to reorganize the objects residing the SYSAUX tablespace to make the free space to be located in the end of the SYSAUX datafiles. In order to do this, we can follow the article published Jonathan Levis ->  Ref: https://jonathanlewis.wordpress.com/2010/02/06/shrink-tablespace

That is it for this topic. I hope it will help you.