Puede llegar un momento en que las estadísticas de Oracle de AWR empiecen a ocupar demasiado. Las soluciones que ofrece Oracle cuando esto pasa son dos: aumentar el tamaño de SYSAUX o bajar los días de retención.

Sin embargo, en la vida real, al aplicar la segunda solución nos podemos encontrar con un problema, y es que al purgar las estadísiticas antiguas, el proceso se eternice, incluso no acabe nunca.

Este problema es conocido y por eso en la mayoría de los foros recomiendan ir borrando de 1 día en 1 día. Por ejemplo, si hemos pasado de retener 30 días a 15, purgar los datos de la siguiente manera:

exec DBMS_STATS.PURGE_STATS(SYSDATE-29);
exec DBMS_STATS.PURGE_STATS(SYSDATE-28);
exec DBMS_STATS.PURGE_STATS(SYSDATE-27);
(...)

Es una buena recomendación y no tendrás problema en la mayoría de los casos. Aunque sigue teniendo dos pegas: también es lento y el espacio que eliminas no queda disponible para reducir el tablespace, en caso de que lo necesitemos, por lo que tendríamos que reorganizar los datos (Nota 454678.1 en Oracle Support con las indicaciones), añadiendo más tiempo al procedimiento.

La manera rápida y probada1 es crear unas tablas temporales nuevas, insertar los días de retención de datos en ellas, vaciar las originales y volver a introducir los datos de las temporales a las originales.

En este ejemplo vamos a dejar 1 día de histórico en AWR, para posteriormente poder reducir el tamaño de SYSAUX sin problemas, la retención, para el futuro, en 10 días y que tome snaps cada 1 hora.

-- Crear tablas con un día de datos (duración 1 minuto):
create table SYS.WRI$_OPTSTAT_BAK as (select * from sys.wri$_optstat_histhead_history where savtime > SYSDATE - 1);
create table SYS.WRI$_OPTSTAT_TAB_BAK as (select * from sys.wri$_optstat_tab_history where savtime > SYSDATE - 1);
create table SYS.WRI$_OPTSTAT_IND_BAK as (select * from sys.wri$_optstat_ind_history where savtime > SYSDATE - 1);
create table SYS.WRI$_OPTSTAT_HISTGRM_HISTORY_B as (select * from SYS.WRI$_OPTSTAT_HISTGRM_HISTORY where savtime > SYSDATE - 1);

-- Truncar las tablas originales (10 segundos):
truncate table SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;
truncate table SYS.WRI$_OPTSTAT_TAB_HISTORY;
truncate table SYS.WRI$_OPTSTAT_IND_HISTORY;
truncate table SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;

-- Borrar los índices de esas tablas (2 segundos):
alter index I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST unusable;
alter index I_WRI$_OPTSTAT_H_ST unusable;
drop index I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST;
drop index I_WRI$_OPTSTAT_HH_ST;

-- Inserto los datos válidos en las tablas originales:
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);
insert into SYS.WRI$_OPTSTAT_HISTGRM_HISTORY (SELECT * FROM SYS.WRI$_OPTSTAT_HISTGRM_HISTORY_B);

-- Borrar tablas temporales:
drop table SYS.WRI$_OPTSTAT_BAK;
drop table SYS.WRI$_OPTSTAT_TAB_BAK;
drop table SYS.WRI$_OPTSTAT_IND_BAK;
drop table SYS.WRI$_OPTSTAT_HISTGRM_HISTORY_B;

-- Recrear los índices:
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";

alter index "SYS"."I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST" rebuild;
alter index "SYS"."I_WRI$_OPTSTAT_H_ST" rebuild;

-- Asegurarse de que están USABLE
select index_name from dba_indexes where status='UNUSABLE';

-- Analizar el esquema (15 minutos):
ANALYZE TABLE SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY DELETE STATISTICS;
ANALYZE TABLE SYS.WRI$_OPTSTAT_TAB_HISTORY DELETE STATISTICS;
ANALYZE TABLE SYS.WRI$_OPTSTAT_IND_HISTORY DELETE STATISTICS;
ANALYZE TABLE SYS.WRI$_OPTSTAT_HISTGRM_HISTORY DELETE STATISTICS;
exec dbms_stats.gather_schema_stats('SYS',cascade=>TRUE,degree=>20);

-- Ejecutar purgado
exec dbms_stats.purge_stats(SYSDATE-1);

--- Cambio el intervalo a 1 hora y la retención a 10 días
select * from dba_hist_wr_control;
execute dbms_workload_repository.modify_snapshot_settings (interval => 60, retention => 14400);
exec dbms_stats.alter_stats_history_retention(10);
select * from dba_hist_wr_control;

Como véis, de un procedimiento que podría alargarse fuera de nuestra jornada de 8 horas, hemos pasado a pocos minutos.

1Esto es un procedimiento no oficial. Úsalo bajo tu responsabilidad.