Name: Oracle 19c Administration
Rating: 4.75 (4 reviews)

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 : l’option partitionnement permet de découper le stockage physique des tables et des index en morceaux plus petits...

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.

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 - 1 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)...

Gestion des index B-tree

1. Vue d’ensemble

Un index est une structure définie sur une ou plusieurs colonnes d’une table ; la (les) colonne(s) constitue(nt) la clé de l’index.

L’index permet un accès rapide aux lignes de la table lors d’une recherche basée sur la clé de l’index. La notion d’index est analogue à celle de l’index d’un livre : pour rechercher un mot dans un livre, il est plus rapide de regarder d’abord dans l’index, ce dernier donnant les numéros des pages qui contiennent le mot. Un index est physiquement et logiquement indépendant de la table. Il peut être créé/supprimé sans affecter la table de base (sauf impact sur les performances lorsque l’index est supprimé). Un index nécessite son propre espace de stockage.

Les index sont automatiquement utilisés et actualisés par Oracle :

utilisés lors des recherches si une clé d’index est mentionnée dans la clause WHERE d’une requête ;
actualisés à chaque mise à jour (INSERT, UPDATE, DELETE).

La présence ou l’absence d’un index est complètement transparente pour l’application ; c’est Oracle qui utilise (ou non) les index automatiquement.

La maintenance des index dégrade les performances des mises à jour.

Un index peut être unique ou non unique :

Unique : une valeur de la clé d’index n’est présente qu’une fois dans la table.
Non unique : une valeur de la clé d’index peut être présente plusieurs fois dans la table.

Oracle préconise de ne pas créer d’index unique explicitement mais de définir des contraintes d’intégrité (PRIMARY KEY ou UNIQUE) pour lesquelles Oracle crée automatiquement des index uniques. Les index non uniques, par contre, doivent être créés explicitement.

Un index peut être composé (concaténé). Dans ce cas, la clé d’index contient plusieurs colonnes de la table ; elles ne sont pas toujours adjacentes dans la table, ni forcément placées dans le même ordre que dans la table.

Les valeurs NULL ne sont pas stockées dans les index B-tree (sauf dans...

Les statistiques et l’optimiseur Oracle

L’optimiseur Oracle est chargé de déterminer le plan d’exécution des requêtes, c’est-à-dire la manière dont Oracle va exécuter la requête.

Depuis maintenant plusieurs versions, Oracle recommande de faire fonctionner l’optimiseur dans le mode CBO (Cost Based Optimizer - Optimiseur basé sur les coûts). Depuis la version 10, seul le mode CBO est supporté ; le mode RBO (Rule Based Optimizer - Optimiseur basé sur les règles) n’est plus supporté.

Pour fonctionner, l’optimiseur dans le mode CBO a besoin de statistiques sur les tables, les colonnes et les index. Ces statistiques sont calculées avec le package DBMS_STATS.

Dans les anciennes versions, il était de la responsabilité du DBA de programmer une tâche périodique de collecte des statistiques, afin que l’optimiseur ne travaille pas avec des données obsolètes.

Depuis la version 10, les statistiques sont automatiquement collectées par Oracle. Depuis la version 11, cette collecte s’effectue par l’intermédiaire d’une tâche de maintenance automatisée.

Par défaut, cette tâche de maintenance collecte les statistiques sur les objets de la base de données qui n’ont pas de statistiques ou qui ont des statistiques jugées...

Le conseiller sur les segments

Parmi les tâches de maintenance automatisées, Oracle exécute un conseiller sur les segments (Segment Advisor). Ce conseiller identifie les segments qui ont de l’espace récupérable ou qui présentent un problème de migration. Si besoin, le conseiller sur les segments peut être exécuté manuellement à l’aide du package DBMS_ADVISOR (voir la documentation Oracle).

Pour consulter les résultats du conseiller sur les segments, vous pouvez interroger différentes vues du dictionnaire de données (DBA_ADVISOR_RECOMMENDATIONS, DBA_ADVISOR_FINDINGS, DBA_ADVISOR_ACTIONS et DBA_ADVISOR_ OBJECTS) mais il est plus simple d’appeler la fonction table ASA_RECOMMENDATIONS du package DBMS_SPACE qui effectue les jointures nécessaires entre les différentes vues et retourne le résultat sous forme lisible.

La spécification de cette fonction est la suivante :

DBMS_SPACE.ASA_RECOMMENDATIONS  
   ( 
   all_runs        IN    VARCHAR2 DEFAULT := TRUE, 
   show_manual     IN    VARCHAR2 DEFAULT := TRUE, 
   show_findings   IN    VARCHAR2 DEFAULT := FALSE 
   ) 
RETURN ASA_RECO_ROW_TB PIPELINED;

Avec :

all_runs	Si ce paramètre est à TRUE, la fonction retourne les résultats de toutes les exécutions automatiques du conseiller. S’il est égal à FALSE, seul le résultat de la dernière exécution automatique est retourné. Ce paramètre est sans objet pour les exécutions manuelles du conseiller.
show_manual	Si ce paramètre est à TRUE, la fonction retourne les résultats des exécutions manuelles et automatiques du conseiller. S’il est égal à FALSE, les résultats des exécutions manuelles ne sont pas retournées.
show_findings	Si ce paramètre est à TRUE, la fonction retourne uniquement les constats (findings) effectués par le conseiller mais pas les recommandations. S’il est égal à FALSE, seules les recommandations sont retournées.

Les « constats » (findings) correspondent à ce que le conseiller a pu observer dans les segments analysés (espace...

Utiliser Oracle SQL Developer

EM Express ne propose aucune page pour gérer les tables et les index. Pour cela, il est par contre possible d’utiliser Oracle SQL Developer.

1. Les tables

Dans Oracle SQL Developer, l’onglet affiché lors de la sélection d’une table dans le panneau Connexions présente les différentes caractéristiques de la table dans plusieurs onglets :

Un clic droit sur le dossier Tables dans le panneau Connexions affiche un menu contextuel qui permet notamment de créer une nouvelle table (menu Nouvelle table…) :

Lorsque vous sélectionnez ce menu, la fenêtre de dialogue suivante s’affiche :

Cette fenêtre de dialogue simplifiée permet de définir une nouvelle table avec des caractéristiques minimales. Pour avoir accès à plus d’options, vous pouvez cliquer sur la case à cocher Avancé :

Une fenêtre de dialogue similaire est proposée pour modifier une table lorsque vous cliquez sur l’icône images/13i01.png

dans l’onglet qui affiche la structure de la table :

Par ailleurs, lorsque vous effectuez un clic droit sur une table dans le panneau Connexions, un menu contextuel permettant d’effectuer diverses actions sur la table s’affiche :

Le menu Actions... proposé dans l’onglet qui affiche la description d’une table offre à...

Problèmes courants et solutions

ORA-01653: impossible d'étendre la table X. de N dans le tablespace Y

ORA-01654: impossible d'étendre l'index X. de N dans le tablespace Y

Explication

Un segment (table ou index) n’arrive pas à s’étendre.

Cause(s)

Le segment (table ou index) n’arrive pas à s’étendre car le tablespace dans lequel il est stocké n’a pas suffisamment d’espace disponible et ne peut pas s’étendre lui-même.

Action(s)

Il faut augmenter l’espace disponible dans le tablespace :

- soit en lui allouant un nouveau fichier de données (ALTER TABLESPACE ... ADD DATAFILE ...) ;

- soit en augmentant la taille d’un fichier de données du tablespace (ALTER DATABASE DATAFILE ... RESIZE ...) ;

- soit en autorisant un fichier de données du tablespace à s’étendre automatiquement (ALTER DATABASE DATAFILE ... AUTOEXTEND ON ...).

ORA-01631: nbre max. d'ensembles de blocs contigus (N) atteint dans table X.

ORA-01632: nbre max. d'ensembles de blocs contigus (N) atteint dans index X.