Overblog Suivre ce blog
Editer l'article Administration Créer mon blog

LAMI DBA

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 !

Published by Micka - - Oracle Statistiques

commentaires

LAO 09/09/2015 17:51

Super BatMan (le surnom de Micka ;) Et voila c'est parti....

Articles autour des SGBD Oracle, SQL Server & PostgreSQL

News

Il était temps de refaire peau neuve en relookant notre Blog !

Au programme dans les prochains jours, la reprise des articles de Mickael@DBAFAQ qui rejoint LAMI-DBA, pour plus de simplicité de gestion, et bien sur de nouveaux articles !

A propos de LAMI-DBA

Le Blog LAMI-DBA est la fusion de deux blogs existants, celui de LAurent (laodba) et celui de MIckael (dbafaq), deux DBA passionnés des sgbd, et particulièrement d'Oracle.

 

Laurent, 45 ans, Expert Oracle & MS SQL Server, Team Leader, dit "Le Taz", Certifié Expert RAC 11G, Exadata Implementation Specialist, OCA 11G, 

Profil Linkdin               Blog de Laurent

 

 

 

 

Mickael, 35 ans, DBA Oracle, dit "Batman", Certifié Expert RAC 11G, OCP 11G, OCP 10G,

Profil Linkdin               Blog de Mickael

 

Hébergé par Overblog