Oracle 11g : How to PURGE Optimizer stats - Tablespace SYSAUX fills up !!!

Qui n'a jamais rencontré un tablespace SYSAUX qui n'arrête pas de grossir et de grossir indéfiniment et ce malgré utiliser la procédure Oracle officielle à savoir l'utilisation de DBMS_STATS (purge / alter stats..)

La raison principale serait (et je dis bien serait) liée à un bug de la version 11g, dont l'utilisation de dbms_stats.purge_stats ne permetterait pas de vider correctement les tables de statistiques liées à l'optimizer, la commande purge_stats ne rendant pas la main.

Du coup, après avoir étudié la choses et bien évidemment parcouru notre ami google, voici une procédure non officielle, qui permet de retrouver une situation normale et un tablespace à une taille acceptable, car dans mon cas ce dernier était à plusde 20Go, sur une base de données de volumétrie moyenne, voir petite (120Go).

Première étape : la taille actuelle du tbs, pour faire un état avant et un état après :

select sum(d.bytes/1024/1024) "tbs allocated space Mo" from dba_data_files d where d.tablespace_name='SYSAUX';

tbs allocated space Mo
----------------------
20480

select sum(f.bytes/1024/1024) "Free space Mo" from dba_free_space f where f.tablespace_name='SYSAUX';

Free space Mo
-------------
3136,6875

Seconde étape : Backup des tables avec comme critère la rétention des statistiques souhaitée. Dans mon cas, mes stats sont conservées 7j, je positionne donc le critère à 7j :

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

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

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

Troisième étape : Un joli truncate des tables :

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;

Quatrième étape : On insert ce que l'on a au préalable sauvegardé dans les tables qui ont été précédemment tronquées, puis on supprime les tables de backup :

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

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

Au tour des indexes, qui doivent etre supprimés à leur tour, pour être recrées (un rebuild n'est pas suffisant)

drop index I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST;
drop index I_WRI$_OPTSTAT_HH_ST;

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

Il faut par contre vérifier que les indexes soient bien en statut USABLE, sinon cela va poser problème ..

select index_name from dba_indexes where status='UNUSABLE' and owner='SYS';

no rows selected.

=> Good !

On peut maintenant repasser le dbms_stats.purge_stats pour que tout soit au même niveau de rétention. La commande doit maintenant passer correctement, et rendre la main, ce qui n'était pas le cas auparavant (dans mon cas je précise)

exec dbms_stats.purge_stats(SYSDATE-7);

Pour terminer, un petit coup de stats sur le schéma SYS pour rendre tout propre :

exec dbms_stats.gather_schema_stats('SYS',cascade=>true,estimate_percent=>100);

Et on fait un état après de l'allocation du tablespace afin de voir si nos efforts ont été récompensés ou non :

select sum(d.bytes/1024/1024) "tbs allocated space Mo" from dba_data_files d where d.tablespace_name='SYSAUX';

tbs allocated space Mo
----------------------
20480

select sum(f.bytes/1024/1024) "Free space Mo" from dba_free_space f where f.tablespace_name='SYSAUX';

Free space Mo
-------------
12946,6875

Nous avons récupéré quasiment 10Go d'espace, soit la moitié de la taille allouée au tablespace ==> mission accomplie !

Enjoy ! ;-)

Micka !