Oracle 9i et 10g - Critères et méthodes pour la défragmentation des tables et indexes

Introduction

Certaines applications possèdent des procédures de purge mises à la disposition des équipes applicatives et métier. À l’issue de la purge, les défragmentations des tables et indexes purgés sont nécessaires.

Dans cet article sont présentés les critères et méthodes pour défragmenter les tables et indexes impactés par une purge applicative dans une instance Oracle 10g. L’instance Oracle 10g en question dans cet article n’est pas en mode ASSM (Automatic Storage Space Management).

Contexte

L’instance s’appelle UBXP : un tablespace stocke les tables (UBIX_TABLES) et un autre tablespace stocke les indexes (UBIX_INDEX). Ces deux tablespaces sont en mode Locally Managed Tablespace (extent management local). En revanche le mode ASSM (Automatic Storage Space Management) n’est pas activé pour ces deux tablespaces. Pour retrouver ces propriétés :

select tablespace_name, 
        extent_management as LMT,
        segment_space_management as ASSM 
from dba_tablespaces 
  where tablespace_name in ('UBIX_TABLES','UBIX_INDEX');
          
TABLESPACE_NAME                LMT        ASSM
------------------------------ ---------- ------
UBIX_TABLES                    LOCAL      MANUAL
UBIX_INDEX                     LOCAL      MANUAL

À l’issue de la purge applicative, aucun gain d’espace n’est mesuré. Les défragmentations des tables et indexes sont nécessaires.

L’objectif est de déterminer quelles tables et quels indexes doivent être défragmentés.

Détection des tables et indexes fragmentés (dba_tables, index_stats)

Détection des tables fragmentées

Pour détecter les tables fragmentées, la vue dba_tables doit être à jour par rapport à la dernière purge, ce qui revient à lancer le calcul des statistiques après la purge applicative afin que les colonnes num_rows, BLOCKS, EMPTY_BLOCKS , etc. dans la vue dba_tables reflètent la réalité.

Pour vérifier la date de dernière analyse des tables, interroger la colonne LAST_ANALYZED de la vue dba_tables :

select table_name,
     to_char(last_analyzed,'DD/MM/YYYY HH24:MI:SS') as "LAST_ANALYZED"
     from dba_tables
     where owner not in ('SYS','SYSTEM','OUTLN');

TABLE_NAME     LAST_ANALYZED
------------   -----------------------
 ...
EDSSTA         25/09/2010 22:39:10
SPINCD         25/09/2010 23:08:09
ERXSRE         25/09/2010 22:39:14
 ...

Si la date de dernière analyse est antérieure à la purge, lancer le calcul des statistiques. Les informations de la vue dba_tables sont capitales et sont la source des critères de défragmentation des tables.

L’équation pour détecter les tables fragmentées dans cet article va être la suivante :

$$ \begin{align} & R = \frac {N}{U} = \cfrac { \text{num_rows} \times {\text{avg_row_len} + 2 } } { \text{blocks} \times \text{db_block_size} \times \biggl( 1 - \dfrac {\text{PCT_FREE} } {100} \biggr) } \leqslant 0,8 \\ \\ & U \geqslant 256 \text{ Mb} \end{align} $$

num_rows, avg_row_len, blocks sont des colonnes de la vue dba_tables et correspondent respectivement au nombre de lignes, à la taille moyenne d’une ligne et au nombre de blocs pour une table.

db_block_size est la taille du bloc de l’instance, interroger v$parameter pour retrouver sa valeur :

select name, value from v$parameter where name='db_block_size'
          
NAME              VALUE
---------------   --------------
db_block_size     8192

N est l’espace nécessaire réelle pour stocker toutes les lignes avec un surcoût de 2 bytes par ligne (+ 2 dans l’équation) pour l’entête du bloc (block header).
U est l’espace utilisé par la table dans l’instance (délimité par le marqueur de cru ou High Water Mark)

Seules les tables de plus de 256 Mb sont prises en considération dans cet exemple (U supérieur ou égal à 256 Mb) : ce niveau peut être ajusté en fonction de la taille de l’instance et des tables.

Les tables fragmentées sont celles pour lesquelles le ratio R est inférieur à 0,8, ce qui équivaut à dire que moins de 80% de l’espace consommé par la table est nécessaire pour stocker toutes les lignes.

En langage SQL, l’équation devient :

select table_name, 
       ratio,
       u
from ( select table_name,
         (num_rows * (avg_row_len +2))/(blocks * 8192 * (1 -pct_free/100)) as ratio,
        blocks * 8192 * (1 -pct_free/100) as u
        from dba_tables     
          where owner not in ('SYS','SYSTEM','OUTLN')
          and blocks != 0)
where ratio <=0.8
and  u > 256000000
order by 2

TABLE_NAME                          RATIO          U
------------------------------ ---------- ----------
HISOPT                         ,200976093  727348838
ICOUPT                         ,211985871 1257313075
ISPRSK                         ,220985461 1637513626
ITPCRO                         ,763577321 1121859994

Cette commande SQL simple permet de se concentrer sur les tables qui ont vraiment besoin d’une réorganisation.

Les tables présentant du chaînage et des migrations de lignes sont également dans le champ de recherche des tables fragmentées. La vue DBA_TABLES est une fois de plus la source d’informations pour détecter de la fragmentation générée par le chaînage de lignes : le taux de lignes chaînées (chain_cnt) par rapport au nombre de lignes dans la table (num_rows) ne doit pas excéder 5%.

$$ R = \frac {\text{chain_cnt}} {\text{num_rows}} \geqslant 0,05 $$

Pour plus d’informations sur le chaînage de lignes et son élimination : Oracle - chaînage et migrations de lignes .

Détection des indexes fragmentés (index_stats, analyze index validate structure)

La commande ANALYZE INDEX <owner>.<index_name> VALIDATE STRUCTURE est une commande très utile pour détecter les indexes fragmentés. L’option VALIDATE STRUCTURE renseigne automatiquement la vue index_stats.

analyze index OPS$BRU.ISPRSK1 validate structure;
          
Index analyzed.

select name, 
  lf_rows, 
  del_lf_rows, 
  height, 
  used_space/1024/1024 as "USED_SPACE_MB" 
from index_stats;

NAME              LF_ROWS DEL_LF_ROWS     HEIGHT USED_SPACE_MB
-------------- ---------- ----------- ---------- -------------
ISPRSK1           8843286     6460685          4    459,932841

Pour l’index ci-dessus, le nombre de niveaux de l’index B-TREE ISPRSK1 est à 4, valeur très élevée, et le nombre de lignes supprimées dans les niveaux feuilles (del_lf_rows) est d’environ 6,4 millions de lignes sur 8,8 millions (lf_rows). Cet index a donc été particulièrement affecté par la purge.

Malheureusement, la vue index_stats reporte les statististiques uniquement pour le dernier index analysé avec l’option VALIDATE STRUCTURE. Par conséquent les résultats de chaque commande ANALYZE TABLE ... VALIDATE STRUCTURE doivent être copiés dans une table temporaire pour obtenir un rapport pour tous les indexes.

Le script ci-dessous permet de générer dynamiquement le script analyze_global_indexes.sql afin de remplir automatiquement une table appelée gistats avec les statistiques pour tous les indexes :


`% sqlplus "/ as sysdba"` `set pagesize 9999; set linesize 1000; set heading off; set feedback off; set echo off; spool analyse_global_indexes.sql; select '@create_table_gistats.sql;' from dual; select 'analyze index '\|\|owner\|\|'.'\|\|index_name\|\|' validate structure;' \|\|chr(10)\|\|'@insert_gistats.sql;' from dba_indexes where owner not in ('SYS','SYSTEM','OUTLN'); spool off; set heading on; set feedback on; set echo on;` analyze_global_indexes.sql `@create_table_gistats.sql; analyze index OPS$BRU.RNGSTD2 validate structure; @insert_gistats.sql; analyze index OPS$BRU.RNGSTD1 validate structure; @insert_gistats.sql; ...`	create_table_gistats.sql `create table gistats as select name, most_repeated_key, distinct_keys, del_lf_rows, lf_rows, height, used_space from index_stats where 1=2;` insert_gistats.sql `insert into gistats ( select name, most_repeated_key, distinct_keys, del_lf_rows, lf_rows, height, used_space/1024/1024 from index_stats );`

% sqlplus "/ as sysdba"

set pagesize 9999;
set linesize 1000;
set heading off;
set feedback off;
set echo off;

spool analyse_global_indexes.sql;

select '@create_table_gistats.sql;' from dual;

select 'analyze index '||owner||'.'||index_name||' validate structure;'
||chr(10)||'@insert_gistats.sql;'
from dba_indexes
where owner not in ('SYS','SYSTEM','OUTLN');

spool off;

set heading on;
set feedback on;
set echo on;

analyze_global_indexes.sql

@create_table_gistats.sql;

analyze index OPS$BRU.RNGSTD2 validate structure;
@insert_gistats.sql;

analyze index OPS$BRU.RNGSTD1 validate structure;
@insert_gistats.sql;
...

create_table_gistats.sql

create table gistats
as
select name,
   most_repeated_key,
   distinct_keys,
   del_lf_rows,
   lf_rows,
   height,
   used_space
from index_stats
where 1=2;

insert_gistats.sql

insert into gistats
(
select name,
   most_repeated_key,
   distinct_keys,
   del_lf_rows,
   lf_rows,
   height,
   used_space/1024/1024
from index_stats
);

Après exécution du script analyze_global_indexes.sql, l’équation pour détecter les indexes fragmentés dans cet article va être la suivante :

$$ \begin{align} & R=\frac{D}{L}=\frac {\text{del_lf_rows}} {\text{lf_rows}} \geqslant 0,1 \\ \\ & \text{height} \geqslant 4 \end{align} $$

Seules les indexes consommant plus de 20 Mb sont considérés ici (used_space supérieur ou égal à 20 Mb dans la table gistats) : ce niveau peut être ajusté en fonction de la taille de l’instance et des indexes.

Les indexes fragmentés sont ceux qui présentent dans les niveaux feuilles un taux de lignes supprimées (del_lf_rows) par rapport au nombre de lignes (lf_rows) supérieur ou égal à 10% (0.1).

Les indexes B-TREE dont la hauteur (height) est supérieure ou égale à 4 niveaux sont également incorporés dans le périmètre des indexes éventuellement fragmentés.

En langage SQL :

Pour le ratio ##\displaystyle { \frac {\text{del_lf_rows}}{\text{lf_rows}} }## :

select name,
  ratio,
  used_space
from ( select round(del_lf_rows/lf_rows * 100,2) as ratio,
             name,
             used_space
       from gistats
       where lf_rows != 0
       and  del_lf_rows != 0
     )
where ratio >= 10
and used_space > 20
order by 2

NAME                                RATIO USED_SPACE
------------------------------ ---------- ----------
HISPOT1                             18,14 30,0900288
HISDPO1                             19,89 58,4686499
HISTDP1                             26,77  49,414629
ICOUPT2                             50,38 193,531354
ISPRSK2                             53,67  118,38968
ISPRSK5                             71,32 245,996452
HISOPT1                             71,93 519,580663
ICOUPT1                             72,11 780,430938
ISPRSK1                             73,06 459,932841
ISPRSK4                             73,62 129,395497

Pour les indexes dont la hauteur de l’index B-TREE est supérieure ou égale à 4 :

select name,
  ratio,
  used_space,
  height
from ( select round(del_lf_rows/lf_rows * 100,2) as ratio,
             name,
             used_space,
             height
       from gistats
       where lf_rows != 0
       and  del_lf_rows != 0
     )
where height >=4
order by 4

NAME                                RATIO USED_SPACE     HEIGHT
------------------------------ ---------- ---------- ----------
HISOPT1                             71,93 519,580663          4
ICOUPT1                             72,11 780,430938          4
ISPRSK1                             73,06 459,932841          4

Dans le cadre de cet article, les indexes ayant une hauteur supérieure à 3 sont également ceux qui présentent un ratio supérieur à 10%.

Défragmentation des tables et indexes

Pour toutes les opérations de défragmentation évoquées ci-dessous, l’espace libre dans les tablespaces doit être suffisant.

Défragmentation des tables

2 méthodes possibles au choix pour défragmenter une table :

Export / import (méthode la plus risquée)
Déplacement de la table sur un nouveau segment - ALTER TABLE MOVE

Export/import

La méthode export/import est la plus risquée techniquement car elle nécessite par exemple la suppression et recréation des triggers mais elle demeure la seule solution lorsque il n’y a pas assez de place dans les tablespaces pour faire des opérations de déplacements de segments.

1. La table est exportée vers un fichier plat avec le binaire d’export Oracle exp.

% exp "'/ as sysdba'" FILE=ISPRSK.dmp LOG=ISPRSK.log TABLES=OPS\$BRU.ISPRK

Lorsque les propriétaires ou noms d’objets ont un caractère spécial comme $, il doit être échappé avec \.

2. La table est tronquée ce qui garantit la suppression de tous les blocs et extents associés à la table

truncate table OPS$BRU.ISPRSK;

Supprimer tous les indexes, triggers, contraintes, etc. attachés à la table. La suppression des triggers est cruciale car l’import avec l’option IGNORE=Y déclenche les triggers. La suppression des indexes est souhaitable également pour éviter des dépassements de capacités dans les tablespaces temporaires et d’UNDO lors de l’import des données.

3. Les données sont réimportées avec le binaire d’import Oracle imp en utilisant les options ROWS=Y et IGNORE=Y, IGNORE=Y pour ignorer la création de la table en échec à l’import puisqu’elle existe déjà.

% exp "'/ as sysdba'" FILE=ISPRSK.dmp LOG=ISPRSK.log FROMUSER=OPS\$BRU TOUSER=OPS\$BRU IGNORE=Y ROWS=Y

4. Les triggers, indexes, etc. sont recréés

Déplacement de la table sur un nouveau segment - ALTER TABLE MOVE

Une solution plus simple et moins risquée pour défragmenter : déplacer la table d’un tablespace vers un autre ou dans le même tablespace grâce à l’option MOVE de la commande ALTER TABLE, sous réserve d’espace libre suffisant dans le tablespace :

alter table OPS$BRU.ISPRSK move tablespace UBIX_TABLES nologging;

L’option NOLOGGING génère une journalisation minimum.

Cette méthode modifie les rowids des lignes, ce qui met les indexes de la table au statut inutilisable (UNUSABLE) et impose une reconstruction de ces indexes. Le paragraphe concernant les opérations post défragmentation évoque la gestion de ce cas.

Défragmentation des indexes - ALTER INDEX REBUILD

Il est possible d’éviter de reconstruire un index (DROP INDEX / CREATE INDEX) grâce à l’option REBUILD de la commande ALTER INDEX. Cette commande permet de déplacer un index sur un nouveau segment, opération qui supprime la fragmentation au passage.

select sum(bytes) from dba_segments where segment_name='ICOUPT1';

SUM(BYTES)
----------
1372651520

alter index OPS$BRU.ICOUPT1 rebuild tablespace ubix_index nologging;

Index altered.

select sum(bytes) from dba_segments where segment_name='ICOUPT1';

SUM(BYTES)
----------
 260849664

Opérations post défragmentation (indexes au statut UNUSABLE, calcul des statistiques)

Une précaution très importante à prendre : vérifier qu’il n’existe aucun index au statut UNUSABLE à la suite d’éventuelles opérations de déplacement de tables. Pour retrouver les indexes au statut UNUSABLE :

select owner, index_name 
     from dba_indexes 
     where status='UNUSABLE' 
     and owner not in ('SYS','SYSTEM','OUTLN');

Pour générer dynamiquement le script de reconstruction des indexes au statut UNUSABLE :

% sqlplus "/ as sysdba"

set echo off;
set feedback off;
set heading off;
set linesize 1000;
set pagesize 50000;

spool rebuild_unusable_indexes.sql;

select 'alter index '||owner||'.'||index_name||' rebuild nologging;'
from dba_indexes 
where owner not in ('SYS','SYSTEM','OUTLN')
and status = 'UNUSABLE';

spool off;
exit;

% sqlplus "/ as sysdba"

@rebuild_unusable_indexes.sql;

À l’issue de la défragmentation, relancer le calcul des statistiques.

L’évolution de l’espace consommé diminue drastiquement à l’issue de la défragmentation :

Oracle 9i et 10g - Défragmentation des tables et indexes (critères et méthodes)