Oracle Database Discussions

Hi Folks,

Question regarding the dbms_stats.purge_stats utility. My tablespace has grown to about 20GB with 18GB of that being data from SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY. I believe we are encountering the bug where the automatic purging does not work because the MMON process that submits it times out since it is so large. I have tried manually submitting the dbms_stats.purge_stats query and it takes forever to run. I have devised a work around and would like to know if anyone sees any issues with it. I would also like to note that this database is part of an Oracle E-Business Suite environment.

First, I create backup tables using the below queries. (This will preserve 14 days worth of data)

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);

Then I truncate the original tables.

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

Then I insert the 14 days worth of data back into the original tables.

insert into SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY (SELECT * 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;

Drop the related indexes on those tables:

SQL> drop index I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST;

SQL> drop index I_WRI$_OPTSTAT_HH_ST;

Recreate the indexes:

CREATE UNIQUE INDEX "SYS"."I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST" ON "SYS"."WRI$_OPTSTAT_HISTHEAD_HISTORY" ("OBJ#", "INTCOL#", SYS_EXTRACT_UTC("SAVTIME"), "COLNAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSAUX";

CREATE INDEX "SYS"."I_WRI$_OPTSTAT_HH_ST" ON "SYS"."WRI$_OPTSTAT_HISTHEAD_HISTORY" (SYS_EXTRACT_UTC("SAVTIME"))
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSAUX";

Make sure indexes are in usable state:

select index_name from dba_indexes where status='UNUSABLE';

Then I am able to run the below statement in a matter of minutes.

SQL> exec dbms_stats.purge_stats(SYSDATE-14);

Run Gather Schema Statistics


I would like to note that I have had an SR open with Oracle for 8 months and they have identified this as a bug, but have not been able to provide me with a solution. My tablespace can't afford to wait any longer, so this is why I have decided to use a workaround. I have implemented this workaround in many test environments and have not had any issues. I am just a bit gun shy to pull the trigger in Production.


Any opinions/comments are welcome.


Thanks.