Oracle: To Clean up WRI$_OPTSTAT_HISTHEAD_HISTORY

Three  options:

(1):

 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;

(2) 

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.

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;

set linesize 1000;

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

col owner format a10

set linesize 200

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

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

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

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

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

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

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

 from ( select sum(bytes)/1024 Kbytes_free,

               max(bytes)/1024 largest,

              tablespace_name

       from   dba_free_space

       where  tablespace_name='SYSAUX'

     group by tablespace_name ) df,

     ( select sum(bytes)/1024 Kbytes_alloc,

              tablespace_name

      from   dba_data_files

       where  tablespace_name='SYSAUX'

     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

(3)

 1. Take inventory prior to purge

 — number of partitions

select count(1) from dba_tab_partitions

where table_name='WRI$_OPTSTAT_HISTGRM_HISTORY'; 2

select count(1) from dba_tab_partitions

where table_name='WRI$_OPTSTAT_HISTHEAD_HISTORY'; 2

— size of segments

select trunc(sum(bytes)/1024/1024) size_mb

from dba_segments

where segment_name='WRI$_OPTSTAT_HISTGRM_HISTORY'; 25MB

select trunc(sum(bytes)/1024/1024) size_mb

from dba_segments

where segment_name='WRI$_OPTSTAT_HISTHEAD_HISTORY'; 34MB

— SYSAUX tablespace free space

select trunc(sum(bytes)/1024/1024) total_size_mb

from  dba_data_files

where tablespace_name='SYSAUX';                   15024MB

select trunc(sum(bytes)/1024/1024) free_size_mb

from  dba_free_space

where tablespace_name='SYSAUX';             438MB

2. Purge stats and collect them

exec dbms_application_info.set_client_info ('PURGE_STATS');

— purge all stats

exec dbms_stats.purge_stats(dbms_stats.purge_all);

— drop all but 15 days partitions

exec dbms_stats.purge_stats(trunc(sysdate-15));

exec dbms_stats.purge_stats(trunc(sysdate-1));

— gather OS stats as we purged all stats

exec dbms_stats.gather_system_stats;

— gather dict stats

exec dbms_stats.gather_dictionary_stats;

3. Take inventory after purge

— number of partitions

select count(1) from dba_tab_partitions

where table_name=’WRI$_OPTSTAT_HISTGRM_HISTORY’;

select count(1) from dba_tab_partitions

where table_name=’WRI$_OPTSTAT_HISTHEAD_HISTORY’;

— size of segments

select trunc(sum(bytes)/1024/1024) size_mb

from dba_segments

where segment_name=’WRI$_OPTSTAT_HISTGRM_HISTORY’;

select trunc(sum(bytes)/1024/1024) size_mb

from dba_segments

where segment_name=’WRI$_OPTSTAT_HISTHEAD_HISTORY’;

— SYSAUX tablespace free space

select trunc(sum(bytes)/1024/1024) total_size_mb

from  dba_data_files

where tablespace_name=’SYSAUX’;

select trunc(sum(bytes)/1024/1024) free_size_mb

from  dba_free_space

where tablespace_name=’SYSAUX’;

Get which segment is the biggest:

set linesize 200;

select segment_name, segment_type, bytes from dba_segments where tablespace_name='SYSAUX' order by bytes asc;

[...]

SEGMENT_NAME                                                                                                                     SEGMENT_TYPE            BYTES

WRH$_EVENT_HISTOGRAM_PK                                                                                                          INDEX PARTITION     595591168

I_WRI$_OPTSTAT_H_ST                                                                                                              INDEX               601882624

WRH$_SYSSTAT_PK                                                                                                                  INDEX PARTITION     634388480

WRI$_ADV_OBJECTS                                                                                                                 TABLE               743440384

I_WRI$_OPTSTAT_HH_ST                                                                                                             INDEX               815792128

I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST                                                                                                   INDEX              1219493888

SEGMENT_NAME                                                                                                                     SEGMENT_TYPE            BYTES

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

I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST                                                                                                    INDEX              1735393280