Oracle 9i R2 : compression des données

Introduction

La compression des tables introduite avec Oracle 9iR2 peut réduire significativement l’utilisation de l’espace disque et améliorer les performances des requêtes dans certains cas.

Dans cet article sont présentés la configuration des tables compressées, l’impact sur les performances et un cas pratique d’amélioration des performances sur une table partitionnée volumineuse.

Généralités sur la compression

Mode de fonctionnement de la compression

La compression des tables avec Oracle 9i R2 consiste à éliminer les valeurs en doublon trouvées dans les tables d’une base de données. La compression fonctionne au niveau d’un bloc Oracle.

Lorsqu’une table est définie comme compressée, la base de données réserve de l’espace dans chaque bloc pour stocker une copie unique de données qui apparaissent à différents endroits dans ce bloc. Cet espace réservé est appelé « symbol table ». Les données ciblées pour la compression sont stockées seulement dans la table symbole et non dans les lignes mêmes. Comme les données ciblées pour la compression apparaissent dans une ligne unique, une ligne contentant ces données stocke seulement un pointeur sur les données de la table symbole. Les gains d’espace viennent de l’élimination des copies redondantes de valeurs dans la table.

Les effets de la compression des tables sont transparents pour les applications.

Compression des tables

Pour créer une table compressée, utiliser le mot clé COMPRESS dans la commande CREATE TABLE. Le mot clé COMPRESS impose à Oracle de stocker les lignes dans un format compressé dans la mesure du possible.

create table ( ... ) compress;

La commande ALTER TABLE avec le mot clé COMPRESS permet également de changer l’attribut de compression d’une table existante :

alter table <table_name> compress; 

Lorsqu’une table non compressée existe déjà et que l’on désire compresser la table avec les données déjà insérées, l’option MOVE est alors utilisée avec la commande ALTER TABLE en combinaison avec l’option COMPRESS :

alter table <table_name> move compress [ nologging ];

Le mot clé NOCOMPRESS est utilisé pour décompresser une table existante :

alter table <table_name> nocompress [ nologging ]; 

Les vues DBA_TABLES et USER_TABLES du dictionnaire permettent de déterminer si une table est compressée ou non grâce à la colonne COMPRESSION qui présente le statut DISABLED ou ENABLED :

select table_name, compression from dba_tables ;

table_name              compression
-------------           -------------
SALES_HISTORY           DISABLED
SALES_HISTORY_COMP      ENABLED

Définition de la compression au niveau d’un tablespace

L’attribut COMPRESS peut être également défini au niveau du tablespace, à la création du tablespace (CREATE TABLESPACE) ou plus tard (ALTER TABLESPACE). Lorsqu’une table est créée, l’attribut COMPRESS est alors appliqué si le tablespace est défini avec l’attribut COMPRESS.

Pour positionner l’option COMPRESS pour un tablespace à la création ou pour un tablespace existant :

create tablespace <tablespace_name> ... default compress;
alter tablespace <tablespace_name> default compress;

Pour supprimer l’option COMPRESS pour un tablespace existant:

alter tablespace <tablespace_name> default nocompress;

La colonne DEF_TAB_COMPRESSION de la vue DBA_TABLESPACES du dictionnaire permet de déterminer si un tablespace est défini avec l’attribut COMPRESS :

select tablespace_name, def_tab_compression from dba_tablespaces ;

tablespace_name       def_tab_compression
-------------         -------------
USERS                 DISABLED
INDEX                 DISABLED

Indépendamment de l’attribut COMPRESS du tablespace, il est possible de compresser ou décompresser une table dans ce tablespace.

Compression des vues matérialisées

Les vues matérialisées peuvent être compressées de la même manière que les tables avec la commandes CREATE et ALTER :

Pour créer une vue matérialisée compressée :

create materialized view <view_name> compress as select ......; 

Pour compresser une vue matérialisée existante :

alter materialized view <view_name> compress; 

Toutefois avec l’utilisation de cette commande, la compression n’aura lieu qu’à la prochaine matérialisation de la vue.

Lorsqu’une vue matérialisée non compressée existe déjà et que l’on désire compresser la vue matérialisée avec les données déjà insérées, l’option MOVE est alors utilisée avec la commande ALTER MATERIALIZED VIEW en combinaison avec l’option COMPRESS :

alter materialized view <view_name> move compress [nologging]; 

Le mot clé NOCOMPRESS sera utilisé pour décompresser une vue matérialisée existante :

alter materialized view <view_name> move nocompress [nologging]; 

Compression des tables partitionnées

Pour les tables partitionnées, la compression peut être appliquée au niveau de la table ou individuellement au niveau des partitions. Lorsque la compression est appliquée au niveau de la table, les partitions sont en mode compression par défaut. L’utilisateur a un degré de liberté sur la compression des partitions en compressant certaines partitions et pas d’autres : ceci peut être très pratique pour compresser les partitions qui contiennent uniquement de l’historique et qui ne sont quasiment accédées qu’en lecture par les utilisateurs.

Pour positionner l’option COMPRESS sur une partition d’une table :

CREATE TABLE HISTORIQUE_GLOBAL (
     SICOVAM            NUMBER(10) NOT NULL,
     JOUR               DATE NOT NULL,
     ...
)
PARTITION BY RANGE (JOUR) (
    PARTITION P_1_2003
        VALUES LESS THAN (TO_DATE('01-APR-2003','DD-MON-YYYY')) COMPRESS,
    PARTITION P_2_2003
        VALUES LESS THAN (TO_DATE('01-JUL-2003','DD-MON-YYYY')) COMPRESS,
    PARTITION P_3_2003
        VALUES LESS THAN (TO_DATE('01-OCT-2003','DD-MON-YYYY')),
    PARTITION P_4_2003
        VALUES LESS THAN (TO_DATE('01-JAN-2004','DD-MON-YYYY'))
);

Lorsqu’une partition d’une table non compressée existe déjà et que l’on désire compresser la partition avec les données déjà insérées, l’option MOVE est alors utilisée avec la commande ALTER TABLE ... MOVE PARTITION en combinaison avec l’option COMPRESS :

alter table <table_name> move partition <partition_name>
        compress [ nologging ]; 

Le mot clé NOCOMPRESS sera utilisé pour décompresser une partition d’une table existante :

alter table <table_name> move partition <partition_name>
        nocompress [ nologging ]; 

Les vues du dictionnaire DBA_TAB_PARTITIONS et USER_TAB_PARTITIONS avec la colonne COMPRESSION (ENABLED ou DISABLED) permettent de savoir quelles partitions d’une table sont définies avec l’option de compression :

select table_name, partition_name, compression from dba_tab_partitions;

table_name               partition_name         compression
-------------            ---------------        -------------
HISTORIQUE_GLOBAL        P_2_2004               ENABLED
HISTORIQUE_GLOBAL        P_3_2004               ENABLED

Chargement des données dans une table compressée

Seules 4 méthodes bien précises de chargement de données génèrent la compression des données lors du chargement dans une table compressée :

• Direct Path SQL*Loader
• INSERT en série avec le hint APPEND
• INSERT en parallèle
• CREATE TABLE … AS SELECT

Dans les exemples ci-dessous, la table SALES_HISTORY_COMP est définie en mode COMPRESS :

sqlldr
<user>/<password>@instance control=sales_history_comp.ctl
direct=true

INSERT /*+ APPEND */
 INTO SALES_HISTORY_COMP
 SELECT * FROM SALES_HISTORY;

ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ PARALLEL(SALES_HISTORY_COMP,4) */
 INTO SALES_HISTORY_COMP
 SELECT * FROM SALES_HISTORY;

CREATE TABLE SALES_HISTORY_COMP COMPRESS
 AS SELECT * FROM SALES_HISTORY;

Si l’une de ces 4 méthodes n’est pas utilisée (par exemple : utilisation de la commande INSERT classique, de SQL*Loader en mode conventionnel…), les données demeurent non compressées même si la table est définie avec l’option COMPRESS.

Quand utiliser la compression des données (avantages et inconvénients) ?

Systèmes OLTP et systèmes datawarehouse

Dans les systèmes OLTP (Online transaction processing), les données sont généralement insérées par des commandes INSERT classiques, aussi on ne peut bénéficier de la compression dans ces systèmes.

La compression des données est par conséquent adaptée pour les tables de type lecture seule qui sont chargées une fois et lues seulement. Dans les applications de type datawarehouse, les tables sont généralement candidates à de la compression.

Influence des commandes update

La mise à jour des données dans une table compressée nécessite la décompression des lignes, ce qui nuit aux objectifs de la compression. Aussi, les tables qui sont régulièrement mises à jour ne sont pas candidates aux bénéfices de la compression.

Influence des commandes insert/delete

Lors de la suppression d’une ligne dans une table compressée, la base de données libère l’espace occupé par la ligne dans le bloc. Cet espace nouvellement libéré est réutilisé pour des insertions futures. Comme l’insertion de données dans un mode conventionnel (commande INSERT classique) n’est pas compressée, il n’est pas certain que l’espace libéré par la ligne supprimée dans le bloc puisse accueillir les nouvelles données, ce qui engendre de la fragmentation et de l’espace inutilisé dans les blocs.

Aussi il faut être vigilant quant à la fragmentation si la table compressée est sujette à des opérations delete/insert massives.

Cas pratique sur une table partitionnée

Dans le cas pratique, les partitions de la table HISTORIQUE_GLOBAL du schéma RISK essentiellement accédée en lecture seule sont compressées et une influence très notable et bénéfique est constatée sur les performances pour les commandes SELECT sur cette table. Outre des performances améliorées, un gain d’espace disque est également vérifié.

Les partitions de la table HISTORIQUE_GLOBAL sont articulées autour de la colonne JOUR de type date, une partition correspondant à un trimestre d’une année civile.

Gains d’espace disque

Dans le cas pratique, seules les grosses partitions de la table HISTORIQUE_GLOBAL sont analysées en volumétrie, volumétrie connue grâce à la colonne bytes de la vue dba_segments :

select sum(bytes) from dba_segments
where owner='RISK'
      and segment_name='HISTORIQUE_GLOBAL'
      and partition_name is not null
group by segment_name, bytes;


sum(bytes) (avant compression)  sum(bytes) (après compression)   gain d’espace disque
------------------------------  ------------------------------   --------------------
995450880                       68503961                         310 Mb

Amélioration des performances

La requête témoin des gains de performances est la suivante :

select /*+ FIRST_ROWS */ sicovam, p, h, b, d,bid,ask,
       jour-to_date('01/01/1904','DD/MM/YYYY') "jour"
from RISK.Historique_global
where (jour,sicovam) in (select MAX(JOUR), sicovam from Historique_global
                          where JOUR <= 37020+to_date('01/01/1904','DD/MM/YYYY')
                          and D is not null group by sicovam)
order by sicovam

Rows     Row Source Operation
-------  ---------------------------------------------------
  30719  SORT ORDER BY
  30719   NESTED LOOPS
  30719    VIEW
  30719     SORT GROUP BY
6321089      PARTITION RANGE ITERATOR PARTITION: 1 42
6321089       INDEX FULL SCAN PK_HISTORIQUE PARTITION: 1 42 (object id 26136)
  30719    PARTITION RANGE ITERATOR PARTITION: KEY KEY
  30719     INDEX UNIQUE SCAN PK_HISTORIQUE PARTITION: KEY KEY (object id 26136)

Pour cette requête, 30719 lignes sont retournées après un scan de 6 321 089 lignes réparties sur les 42 partitions de la table HISTORIQUE_GLOBAL.

Le tableau ci-dessous donne les performances obtenues avec tkprof avant compression des partitions :

Call        cpu        elapsed
----        ---        -------
Total       50.81      162.88
Total       50.23      185.39
Total       48.53      148.55
Total       50.88      158.33
Moyenne     50.11 s    163.79 s

Le tableau ci-dessous donne les performances obtenues avec tkprof après compression des partitions (le plan d’exécution étant resté le même):

Call        cpu        elapsed
----        ---        -------
Total       36.81      38.01
Total       36.99      38.26
Total       34.90      34.42
Total       35.23      34.69
Moyenne     35.98 s    36.34 s

Le temps CPU gagné est de 14 secondes (50.11 s => 35.98 s), soit un gain de 140%.

Le temps total gagné est de 127.45 secondes (163.79 s => 36.34 s), environ 2 minutes, soit un gain de 450%.

Le gain de performances est considérable, toutefois il faut être vigilant sur les pertes de performances liées à la compression sur les commandes DML (insert | update | delete).

Conclusion

Une bonne analyse du comportement d’une table ou d’une partition d’une table doit être réalisée avant d’envisager "sauvagement" le mode COMPRESS, il faut déterminer :

• son usage (transactionnel ou lecture seule)
• son mode de chargement

Il faut garder à l’esprit, l’aspect néfaste des commandes DML sur les tables ou les partitions compressées (fragmentation avec les opérations delete/insert, décompression lors des mises à jour…) et les raisons qui font que les données peuvent ne pas être compressées lors d’un chargement.