Purging Optimizer Stats

Space consumption continuously increases in the following tables:

• wri$_optstat_tab_history
• wri$_optstat_ind_history
• wri$_optstat_histhead_history

As you know, by default the MMON performs the automatic purge that removes all history older than:

     current time - statistics history retention

Default for Statistics History Retention is 31 days, which is too much and causes excessive tablespace growth due to retained statistics.

SQL> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;

GET_STATS_HISTORY_RETENTION

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

            31

Fun Fact: MMON performs the purge of the optimizer stats history automatically. However it has an internal limit of 5 minutes to perform this job. If the operation takes more than 5 minutes, then it is aborted and stats not purged. No trace or alert message is reported

There are two ways to purge statistics:

1. This is the one recommended by oracle, but it takes too much time to purge

• Size of the table

SQL> select sum(bytes/1024/1024) MB,segment_name from dba_segments where segment_name='WRI$_OPTSTAT_HISTHEAD_HISTORY' group by segment_name;

        MB SEGMENT_NAME

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

     5,512 WRI$_OPTSTAT_HISTHEAD_HISTORY

•      Show the current history level

 SQL> select dbms_stats.get_stats_history_availability from dual;

GET_STATS_HISTORY_AVAILABILITY

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

19-MAY-14 02.31.58.726187000 AM -05:00

•   Retention period for keeping Stats

 SQL> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;

GET_STATS_HISTORY_RETENTION

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

                         31

•   Changed the retention period from 31 to 15

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

SQL> select count(1) from WRI$_OPTSTAT_HISTHEAD_HISTORY;

  COUNT(1)

----------

  29092385

• Running purge stats older than 15 days

 SQL> exec DBMS_STATS.PURGE_STATS(SYSDATE-15);

•  Size of the table

 SQL> select sum(bytes/1024/1024) MB,segment_name from dba_segments where segment_name='WRI$_OPTSTAT_HISTHEAD_HISTORY' group by segment_name;

        MB SEGMENT_NAME

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

    16.125 WRI$_OPTSTAT_HISTHEAD_HISTORY

SQL> select count(1) from WRI$_OPTSTAT_HISTHEAD_HISTORY;

  COUNT(1)

----------

      1944

2. This the traditional way, and its much faster than the previous one.

• Let's backup these tables to store stats older than 14 days

create table SYS.WRI$_OPTSTAT_BAK as (select * from sys.wri$_optstat_histhead_history
where savtime > SYSDATE - 14);

create table SYS.WRI$_OPTSTAT_TAB_BAK as (select * from sys.wri$_optstat_tab_history
where savtime > SYSDATE - 14);

create table SYS.WRI$_OPTSTAT_IND_BAK as (select * from sys.wri$_optstat_ind_history
where savtime > SYSDATE - 14

•  Truncate the table with drop storage option

truncate table SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY drop storage;
truncate table SYS.WRI$_OPTSTAT_TAB_HISTORY drop storage;
truncate table SYS.WRI$_OPTSTAT_IND_HISTORY drop storage

• Insert the records back into the table which you dropped

insert into SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY (SELECT * FROM SYS.WRI$_OPTSTAT_BAK);

Issue: SQL> insert into SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY (SELECT * FROM SYS.WRI$_OPTSTAT_BAK); insert into SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY (SELECT * FROM SYS.WRI$_OPTSTAT_BAK)                 * ERROR at line 1: ORA-54013: INSERT operation disallowed on virtual columns Cause: Re-issue the statment without providing values for a virtual column Action: Attempted to insert values into a virtual column Try to run the first query like this: INSERT     INTO         SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY         (             OBJ#,             INTCOL#,             SAVTIME,             FLAGS,             NULL_CNT,             MINIMUM,             MAXIMUM,             DISTCNT,             DENSITY,             LOWVAL,             HIVAL,             AVGCLN,             SAMPLE_DISTCNT,             SAMPLE_SIZE,             TIMESTAMP#,             EXPRESSION,             COLNAME,             SPARE1,             SPARE2,             SPARE3,             SPARE4,             SPARE5,             SPARE6         )     (SELECT          OBJ#,          INTCOL#,          SAVTIME,          FLAGS,          NULL_CNT,          MINIMUM,          MAXIMUM,          DISTCNT,          DENSITY,          LOWVAL,          HIVAL,          AVGCLN,          SAMPLE_DISTCNT,          SAMPLE_SIZE,          TIMESTAMP#,          EXPRESSION,          COLNAME,          SPARE1,          SPARE2,          SPARE3,          SPARE4,          SPARE5,          SPARE6 FROM SYS.WRI$_OPTSTAT_BAK)

insert into SYS.WRI$_OPTSTAT_TAB_HISTORY (SELECT * FROM SYS.WRI$_OPTSTAT_TAB_BAK);
insert into SYS.WRI$_OPTSTAT_IND_HISTORY (SELECT * FROM SYS.WRI$_OPTSTAT_IND_BAK);

•   Drop the temporary backup tables.

drop table SYS.WRI$_OPTSTAT_BAK;
drop table SYS.WRI$_OPTSTAT_TAB_BAK;
drop table SYS.WRI$_OPTSTAT_IND_BAK;

SQL> select sum(bytes/1024/1024) MB,segment_name from dba_segments where segment_name='WRI$_OPTSTAT_HISTHEAD_HISTORY' group by segment_name;

        MB SEGMENT_NAME

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

    16.125 WRI$_OPTSTAT_HISTHEAD_HISTORY

SQL> select count(1) from WRI$_OPTSTAT_HISTHEAD_HISTORY;

  COUNT(1)

----------

      1944

HTH

Thanks for reading..!!