Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

Support de Cours ORACLE 11g : Administration, par Mawunya Koffi GBENOU Chapitre

Support de Cours ORACLE 11g : Administration, par Mawunya Koffi GBENOU Chapitre 12 : Gestion des tables et des index • Vue d'ensemble • Gestion des tables • Gestion des index B-tree • Les statistiques et l'optimiseur Oracle • Utiliser le Database Control Vue d’ensemble Parmi les principaux types d’objets d’un schéma, seuls les tables et les index occupent de l’espace de stockage en dehors de leur définition dans le dictionnaire. Cet espace de stockage doit être planifié correctement pour éviter les erreurs liées au manque d’espace ou les problèmes de performance. Les tables et les index sont des segments ; le stockage est donc organisé en extensions, piloté par la clause STORAGE et par les caractéristiques du tablespace. Par ailleurs, l’organisation du stockage dans les blocs a de l’importance. Il existe d’autres types d’objets qui occupent de l’espace de stockage, mais ces derniers sortent du périmètre de cet ouvrage : • Vues matérialisées : structure analogue à une table et dont le contenu est périodiquement mis à jour à partir d’une requête SELECT. • IOT (Index Organised Table - table organisée en index) : table dont le stockage est organisé dans l’index de la clé primaire de la table. • Clusters : structures qui permettent de stocker physiquement ensemble des tables fréquemment interrogées par jointure. • Tables et index partitionnées : depuis la version 8, l’option partitionnement permet de découper le stockage physique des tables et des index en morceaux plus petits appelés partitions. De même, il existe plusieurs types d’index : • Index B-tree : index classique qui sera étudié dans cet ouvrage. • Index Bitmap : index dont le stockage est organisé différemment des index B-tree et qui est plutôt destiné à l’indexation des colonnes à faible cardinalité dans un environnement décisionnel (l’index bitmap est très coûteux en mise à jour). • Index à clé inversée : index B-tree qui indexe non pas la valeur de la colonne mais une valeur résultant de l’inversion des octets de la colonne (intéressant pour l’indexation de colonnes qui sont insérées en ordre croissant et interrogées par égalité). • Index basé sur des fonctions : index B-tree qui indexe non pas la valeur de la colonne mais le résultat de l’application d’une fonction SQL (UPPER, LOWER, etc.) à la valeur de la colonne. Il est intéressant lorsque la colonne n’est pas interrogée directement (colonne opérateur valeur) mais avec la fonction (fonction(colonne) opérateur valeur). 1 | P a g e Support de Cours ORACLE 11g : Administration, par Mawunya Koffi GBENOU Gestion des tables 1. Organisation du stockage dans les blocs a. Principes Structure du bloc L’en-tête du bloc contient l’adresse du bloc, le type de segment, un répertoire des tables, un répertoire des lignes et des entrées pour les transactions. La taille de l’en-tête du bloc est variable, de l’ordre de 100 octets à 200 octets. Le reste du bloc contient les données (une à plusieurs lignes de la table) et de l’espace libre. L’en-tête est stocké dans la partie haute du bloc et les données sont insérées à partir du bas. L’en-tête est susceptible de grossir (vers le bas) en fonction de l’activité dans le bloc ; il ne rétrécit jamais. Par exemple, si 100 lignes sont insérées dans le bloc, le répertoire des lignes situé dans l’en-tête grossit ; si les lignes sont ensuite supprimées, le répertoire des lignes ne rétrécit pas (l’espace est conservé et pourra être réutilisé si des lignes sont de nouveaux insérées dans le bloc). Structure d’une ligne L’en-tête d’une ligne contient quelques informations sur la ligne (nombre de colonnes, chaînage éventuel, verrou). La taille de l’en-tête de lignes est variable (3 octets minimum). Ensuite, chaque colonne est stockée avec un en-tête de colonne (qui donne la longueur de la colonne sur 1 à 3 octets) suivi de la valeur de la colonne. 2 | P a g e Support de Cours ORACLE 11g : Administration, par Mawunya Koffi GBENOU La longueur totale d’une ligne dépend du nombre de colonnes et de la valeur stockée dans chaque colonne, la longueur de la colonne dépendant du type de données. Exemple : Type Longueur du stockage CHAR(n) Longueur fixe (n octets), quelle que soit la valeur stockée dans la colonne. VARCHAR2(n) Longueur variable (0 à n octets), dépendant de la valeur stockée dans la colonne. NUMBER(x,y) Longueur variable (entre 1 et 21 octets) dépendant de la valeur stockée dans la colonne. DATE Longueur fixe (8 octets). CLOB, BLOB Longueur variable, jusqu’à 2^32 blocs Oracle. Une valeur NULL occupe un octet en milieu de ligne et aucun en fin de ligne. Les fonctions SQL VSIZE et DUMP appliquées à une valeur (colonne, résultat d’une expression) permettent de connaître respectivement la taille en octets du stockage interne de la valeur et la représentation interne de la valeur. Ce qu’il faut retenir, c’est que le bloc ne contient pas que des données utiles ; il y a des données de contrôle, de surcharge, utilisées en interne par Oracle. À titre d’exemple, une ligne comprenant 3 colonnes stockant 30 octets de données utiles emploiera en moyenne 35 octets d’espace dans le bloc et une ligne comprenant 15 colonnes stockant 145 octets de données utiles emploiera en moyenne 160 octets d’espace dans le bloc. b. Gestion de l’espace dans les blocs L’espace libre à l’intérieur du segment peut être géré automatiquement ou manuellement. Dans le cas de la gestion "manuelle", pour chaque segment, Oracle gère une liste de blocs disponibles pour l’insertion de lignes (freelist). La disponibilité ou la non-disponibilité d’un bloc pour l’insertion est contrôlée par deux paramètres de la définition de la table : PCTFREE et PCTUSED. Dans le cas de la gestion "automatique", pour chaque segment, Oracle utilise une bitmap afin de connaître le taux de remplissage de chaque bloc alloué au segment et en déduire ceux dans lesquels il peut insérer des données. Dans ce cas, le paramètre PCTUSED est sans objet. La gestion automatique est apparue en version 9. La gestion automatique de l’espace dans les segments (Automatic Segment-Space Management - ASSM) présente de nombreux avantages : facilité d’utilisation (pas de paramètre PCTUSED à spécifier), meilleure utilisation de l’espace, meilleure concurrence d’accès pour les insertions simultanées. La gestion automatique de l’espace dans les segments est disponible uniquement dans les tablespaces gérés localement et spécifiée au niveau du tablespace (pas du segment individuel) par la clause SEGMENT SPACE MANAGEMENT AUTO (voir le chapitre Gestion des tablespaces et des fichiers de données) ; elle est activée par défaut. 3 | P a g e Support de Cours ORACLE 11g : Administration, par Mawunya Koffi GBENOU PCTFREE Dans la définition d’une table, le paramètre PCTFREE spécifie le pourcentage de l’espace du bloc laissé libre pour les modifications des lignes stockées dans le bloc : La clause PCTFREE permet de ne pas remplir les blocs à 100 % et de conserver de l’espace disponible à l’intérieur du bloc, pour les futures mises à jour des lignes stockées dans le bloc. En effet, lorsqu’une ligne est modifiée, Oracle cherche à réaliser la modification en conservant la ligne à l’intérieur du bloc où elle est stockée : cela ne pose pas de problème si la longueur globale de la ligne diminue (remplacement de PIERRE par PAUL dans une colonne) mais peut en poser si la ligne grossit (remplacement de PAUL par PIERRE dans une colonne). Dans ce dernier cas, s’il n’y a pas suffisamment d’espace disponible à l’intérieur du bloc, Oracle va déplacer la ligne dans un autre bloc avec des impacts négatifs sur les performances que nous verrons dans la suite de cet ouvrage. Gestion manuelle : PCTUSED Dans la définition d’une table, en gestion manuelle uniquement, le paramètre PCTUSED spécifie le pourcentage d’occupation auquel le bloc doit redescendre avant de redevenir candidat à l’insertion : Lorsque le bloc atteint un taux de remplissage correspondant à la clause PCTFREE, aucune insertion n’est possible avant que de l’espace dans le bloc soit libéré, par suppression de ligne ou diminution de la taille d’une ligne lors d’une modification. Le paramètre PCTUSED permet de contrôler le moment où le bloc redeviendra candidat à l’insertion, suite à la libération d’espace. Ce paramètre permet d’éviter que le bloc ne redevienne immédiatement candidat à l’insertion dès que le moindre octet se libère, d’une part, car l’espace libéré n’est peut-être pas suffisant pour réellement insérer une ligne et d’autre part, car le bloc risque de redevenir non disponible dès la première insertion ; or, cette gestion interne du bloc (disponible ou non pour l’insertion) est coûteuse en temps de traitement. PCTUSED va donc permettre d’attendre que suffisamment d’espace ait été libéré dans le bloc avant d’autoriser de nouvelles insertions dans le bloc. Gestion automatique 4 | P a g e Support de Cours ORACLE 11g : Administration, par Mawunya Koffi GBENOU En gestion automatique, Oracle utilise une bitmap pour connaître le taux de remplissage de chaque bloc alloué au segment : 0 % d’espace libre (plein), entre 0 et 25 % d’espace libre, entre 25 et 50 % d’espace uploads/Management/ chapitre-12-gestion-des-tables-et-des-index.pdf