Aprés le post sur « Utilisons les securefile », revenons sur le sujet LOB avec une approche performance, stockage. Nous rencontrons parfois des modèles de données étranges, nous aurions envie en tant que DBA de tout reprendre à zéro pour refaire un modele relationnel en 3eme forme normale (3NF), plus efficace et mieux conçu dans notre « RDBMS » favori, mais la réalité étant ce qu'elle est nous devons faire avec, et au mieux. De plus Oracle n'est pas qu'une base de données relationnelle :-) on l'oublie trop souvent.

Le contexte

J'ai eu l'occasion lors d'un de mes mandats de consultant de voir une application qui utilise la base de données Oracle de manière bien étrange pour l'administrateur que je suis, élevé au biberon du modele relationnel. Le modèle physique de données de cette application est constitué d'un grand nombre de tables dont la particularité est de n'avoir que deux colonnes, la première étant toujours un varchar2 et la deuxieme soit un CLOB soit une colonne de type XMLTYPE.

Lors d'un traitement par lots dit « batch » j'ai observé une forte contention sur un événement rare. Quand je dis rare c'est un euphémisme, je n'avais jamais rencontré autant de wait « enq : HW contention » de ma longue vie de DBA.

Et j'avais devant moi une instance passant 40 % du DB time sur cet événement.

Explication

Rappel - quelques définitions :

Enqueue (Enq) - Les enqueues sont des verrous locaux utilisés pour sérialiser l'accès à diverses ressources.

Wait Enqueue (Wait Enq) - Cet événement indique une attente sur une demande de verrouillage d'un objet. Ce verrou étant déjà détenu par une/des autre(s) session(s) dans un mode incompatible avec le mode demandé. Le plus fréquent est le « enq: TM – contention », signe d'une mauvaise indexation des clefs étrangères.

HW Enqueue (HW Enq) – L'événement HW Enqueue est utilisé pour sérialiser l'allocation d'espace au dessus de la « High Water Mark ». Ce verrou est donc acquis lorsque la marque « High Water Mark » est déplacée. Celle-ci est déplacée quand l'objet n'a plus suffisamment de place pour croître sous cette « High-Water Mark », ce qui se produit typiquement lors d'une grosse opération d'insertion dans un objet tel qu'une table.

Analyse

Après analyse, je découvre que le taux important de wait « enq : HW contention » provient d'un update ! Contradiction avec la cause principale de cette attente qui est l'insertion de données.

Celà dit il ne s'agit pas de n'importe quel update, il s'agit d'un update dans la colonne de type CLOB. Cela dit, la mise à jour d'un LOB est un processus bien différent de la mise à jour d'un attribut classique d'une table.

Lors d'un update d'une colonne de type LOB, le processus de mise à jour est le suivant :

• Un nouvel espace est alloué pour la nouvelle valeur

• Au commit, le pointeur est mis à jour

• L'ancien espace est libéré

Donc, un update pour une colonne de type LOB engendre en premier lieu une insertion dans un nouvel espace. Si l'application est constituée de 'N' processus mettants à jour en parallèle le même objet, nous nous retrouvons avec une situation de sérialisation de l'extension de l'objet, et donc un nombre important de HW enqueue

Résolution du problème

Une recherche sur le site du support oracle (metalink) et la lecture de divers blogs sur internet nous amenent aux solutions suivantes:

• Changer les paramètres RETENTION / PCTVERSION de l'objet.

• Passer le stockage en Secure Files

• Modifier les paramètres de la clause STORAGE

• Partitionner l'objet en mode HASH

Nous avons choisi les propositions Secure Files, partitionnement et en partie STORAGE.

Une ré-éxécution du traitement nous a permis de valider la diminution drastique de la contention sur les extensions de l'objet.