EXPLAIN PLAN Oracle : analyser les requêtes SQL

EXPLAIN PLAN Oracle : comprendre et optimiser l’exécution de vos requêtes SQL

L’instruction EXPLAIN PLAN est l’un des outils les plus puissants d’Oracle pour analyser les performances de vos requêtes SQL. Elle permet de visualiser le plan d’exécution choisi par l’optimiseur Oracle avant même d’exécuter la requête. Grâce à EXPLAIN PLAN, les développeurs et DBA peuvent identifier les goulets d’étranglement, détecter les full table scans coûteux et prendre des décisions éclairées pour optimiser leurs bases de données en entreprise.

Définition et utilisation d’EXPLAIN PLAN Oracle

L’instruction EXPLAIN PLAN est une commande SQL Oracle qui demande à l’optimiseur de requêtes (CBO — Cost-Based Optimizer) de calculer et d’enregistrer le plan d’exécution d’une instruction SQL dans une table spéciale appelée PLAN_TABLE. Ce plan décrit, étape par étape, la façon dont Oracle compte accéder aux données : quelles tables seront lues, quels index seront utilisés, dans quel ordre les jointures seront effectuées, etc.

Contrairement à une exécution classique, EXPLAIN PLAN n’exécute pas réellement la requête : elle se contente de simuler le raisonnement de l’optimiseur. C’est donc une approche non intrusive, idéale pour analyser des requêtes lourdes sans impacter les utilisateurs en production.

Cas d’usage en entreprise

• Audit de performance : un DBA identifie pourquoi une requête de reporting mensuel prend 45 minutes au lieu de 5.
• Validation avant déploiement : un développeur vérifie qu’une nouvelle requête n’entraîne pas de full table scan sur une table de 50 millions de lignes.
• Comparaison d’index : tester si l’ajout d’un index composite améliore le plan d’exécution avant de le créer en production.
• Tuning de jointures : analyser l’ordre de jointure choisi par Oracle pour des requêtes multi-tables complexes.

Syntaxe complète d’EXPLAIN PLAN

Voici la syntaxe officielle de la commande EXPLAIN PLAN dans Oracle :

EXPLAIN PLAN
  [ SET STATEMENT_ID = 'identifiant' ]
  [ INTO nom_table ]
  FOR instruction_sql;

Explication des paramètres essentiels

• SET STATEMENT_ID = 'identifiant' : paramètre optionnel permettant d’attribuer un identifiant unique au plan généré. Très utile lorsque vous stockez plusieurs plans dans la même PLAN_TABLE et souhaitez les distinguer.
• INTO nom_table : indique dans quelle table stocker le plan. Par défaut, Oracle utilise PLAN_TABLE. Vous pouvez spécifier une table personnalisée si nécessaire.
• FOR instruction_sql : l’instruction SQL à analyser. Il peut s’agir d’un SELECT, INSERT, UPDATE ou DELETE.

Une fois la commande exécutée, le plan est stocké dans PLAN_TABLE. Pour le lire de manière lisible et hiérarchique, Oracle fournit le package DBMS_XPLAN avec la fonction DISPLAY :

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Cette fonction formate automatiquement le plan en arborescence et affiche les colonnes les plus importantes : opération, options, nom de l’objet, coût estimé, cardinalité et nombre d’octets.

Prérequis : La table PLAN_TABLE doit exister dans votre schéma. Depuis Oracle 10g, elle est créée automatiquement comme table globale temporaire publique. Sur des versions plus anciennes, vous devrez l’initialiser via le script $ORACLE_HOME/rdbms/admin/utlxplan.sql.

Exemples pratiques d’EXPLAIN PLAN Oracle

Exemple 1 — Analyser une requête simple sur une table de commandes

Contexte métier : Dans une application e-commerce, les équipes métier signalent que la consultation des commandes d’un client précis est lente. Le développeur veut comprendre comment Oracle exécute cette requête.

-- Étape 1 : générer le plan d'exécution avec un identifiant
EXPLAIN PLAN
  SET STATEMENT_ID = 'analyse_commandes'
  FOR
  SELECT c.client_id,
         c.nom,
         o.numero_commande,
         o.montant_total
  FROM   clients c
  JOIN   commandes o ON c.client_id = o.client_id
  WHERE  c.email = 'marie.dupont@example.com'
  AND    o.statut = 'EN_COURS';

-- Étape 2 : afficher le plan de manière lisible
SELECT * FROM TABLE(
  DBMS_XPLAN.DISPLAY(
    statement_id => 'analyse_commandes'
  )
);

Lecture du résultat : Si Oracle affiche TABLE ACCESS FULL sur la table CLIENTS, cela signifie qu’aucun index n’est utilisé sur la colonne email. La solution consiste à créer un index : CREATE INDEX idx_clients_email ON clients(email);. Après recréation du plan, vous devriez voir apparaître INDEX RANGE SCAN, signe d’une utilisation efficace de l’index.

Exemple 2 — Comparer deux approches de requête sur une table de ventes

Contexte métier : Un analyste financier souhaite calculer le chiffre d’affaires mensuel par région. Il hésite entre deux formulations SQL et veut identifier la plus efficace avant de l’intégrer dans un rapport automatisé.

-- Plan pour la version avec sous-requête
EXPLAIN PLAN
  SET STATEMENT_ID = 'ca_sous_requete'
  FOR
  SELECT region,
         SUM(montant) AS chiffre_affaires
  FROM   ventes
  WHERE  date_vente IN (
           SELECT date_vente
           FROM   ventes
           WHERE  EXTRACT(YEAR FROM date_vente) = 2024
         )
  GROUP BY region;

-- Affichage du premier plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(statement_id => 'ca_sous_requete'));

-- Plan pour la version directe (plus optimale)
EXPLAIN PLAN
  SET STATEMENT_ID = 'ca_direct'
  FOR
  SELECT region,
         SUM(montant) AS chiffre_affaires
  FROM   ventes
  WHERE  EXTRACT(YEAR FROM date_vente) = 2024
  GROUP BY region;

-- Affichage du second plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(statement_id => 'ca_direct'));

Analyse : En comparant les deux plans, vous constaterez souvent que la version directe génère un plan plus simple avec un coût (colonne Cost) inférieur, car Oracle évite une double lecture de la table ventes. Le STATEMENT_ID distinct permet de conserver les deux plans dans PLAN_TABLE et de les comparer côte à côte.

Erreurs courantes avec EXPLAIN PLAN Oracle

Erreur : ORA-00942 – Table ou vue inexistante (PLAN_TABLE)

Symptôme : Lors de l’exécution d’EXPLAIN PLAN, Oracle retourne l’erreur suivante :

ORA-00942: table or view does not exist

Cause : La table PLAN_TABLE n’existe pas dans le schéma courant. Cela se produit principalement sur des environnements Oracle anciens (avant 10g) ou sur des schémas utilisateurs isolés sans accès à la table publique globale temporaire.

Solution : Créer manuellement la table en exécutant le script Oracle fourni dans l’installation :

-- Connexion en tant que DBA, puis exécution du script
@$ORACLE_HOME/rdbms/admin/utlxplan.sql

-- Ou créer la vue synonyme pour l'utilisateur courant
CREATE SYNONYM plan_table FOR sys.plan_table$;

Sur Oracle 10g et supérieur, vérifiez simplement que votre utilisateur dispose du privilège SELECT sur la vue publique PLAN_TABLE.

Résumé — Points clés d’EXPLAIN PLAN Oracle

2 bonnes pratiques Oracle

1. Toujours utiliser DBMS_XPLAN.DISPLAY plutôt que d’interroger directement PLAN_TABLE avec un SELECT *. La fonction formate l’arborescence hiérarchique et calcule les indentations qui rendent le plan lisible. Une lecture directe de la table brute produit un résultat difficile à interpréter.
2. Utiliser systématiquement un STATEMENT_ID explicite pour chaque analyse. Cela vous permet de conserver un historique des plans dans PLAN_TABLE, de comparer plusieurs variantes d’une même requête et d’éviter les confusions lorsque plusieurs développeurs utilisent la même table simultanément.

Aller plus loin sur l’optimisation SQL Oracle

Vous maîtrisez maintenant l’essentiel d’EXPLAIN PLAN. Pour approfondir vos compétences en optimisation SQL Oracle, découvrez ces sujets complémentaires :

• DBMS_XPLAN Oracle : exploiter toutes les options d’affichage des plans d’exécution — apprenez à utiliser DISPLAY_CURSOR et DISPLAY_AWR pour analyser les plans des requêtes déjà exécutées en production.
• Les hints Oracle : forcer l’optimiseur à utiliser un index ou un plan spécifique — découvrez comment guider manuellement l’optimiseur avec des directives comme /*+ INDEX */, /*+ FULL */ ou /*+ USE_NL */.
• DBMS_STATS Oracle : collecter et gérer les statistiques de l’optimiseur — comprenez pourquoi des statistiques obsolètes génèrent de mauvais plans d’exécution et comment les maintenir à jour efficacement.