Requete plan execution

Fiche express
Type	Script SQL*Plus de diagnostic
Objet	Plan d'exécution (EXPLAIN PLAN)
Voir aussi	Perf top sql by disk reads · Perf top sql by buffer gets

Requete plan execution capture et affiche le plan d'exécution d'une requête SQL via EXPLAIN PLAN et la table PLAN_TABLE, en le mettant en forme sous forme d'arbre indenté (requête hiérarchique CONNECT BY). Utile pour comprendre pourquoi une requête identifiée comme coûteuse (voir Perf top sql by buffer gets / Perf top sql by disk reads) est lente : full scan au lieu d'un accès par index, mauvais ordre de jointure, etc.

⚠️ Prérequis : la table PLAN_TABLE doit exister dans le schéma courant. Si ce n'est pas le cas, la créer au préalable avec le script fourni par Oracle :

@?/rdbms/admin/utlxplan.sql

(? représente $ORACLE_HOME dans un script SQL*Plus.)

Script

SET ECHO     OFF
SET PAGESIZE 1000
SET FEEDBACK OFF
SET LINESIZE 15000

COLUMN operation    FORMAT A45
COLUMN type_acces   FORMAT A20
COLUMN nom_objet    FORMAT A30
COLUMN ordre        FORMAT A8
COLUMN etat         FORMAT A20

-- Purge un éventuel plan précédent portant le même identifiant
DELETE FROM plan_table WHERE statement_id = 'X';

EXPLAIN PLAN
SET statement_id = 'X'
INTO plan_table
FOR
<requête à analyser>
;

SELECT
    LPAD(' ', 2 * (LEVEL - 1)) || operation                                          "OPERATION"
  , options                                                                          "TYPE_ACCES"
  , DECODE(TO_CHAR(id), '0', 'COST= ' || NVL(TO_CHAR(position), 'Indéfini'), object_name) "NOM_OBJET"
  , id || '-' || NVL(parent_id, 0) || '-' || NVL(position, 0)                        "ORDRE"
  , ' COUT=' || cost || ',' || 'CARD=' || cardinality                                "COUT_OP"
FROM
    plan_table
START WITH
    id = 0
    AND statement_id = 'X'
CONNECT BY PRIOR
    id = parent_id
    AND statement_id = 'X';

Remplacer <requête à analyser> par la requête SQL réelle (sans point-virgule interne), par exemple :

EXPLAIN PLAN
SET statement_id = 'X'
INTO plan_table
FOR
SELECT * FROM employes WHERE departement_id = 10
;

Alternative moderne

Depuis Oracle 9i, le package DBMS_XPLAN simplifie fortement cette manipulation (mise en forme automatique, pas besoin de gérer statement_id à la main) :

EXPLAIN PLAN FOR
SELECT * FROM employes WHERE departement_id = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Ou, pour afficher le plan réellement utilisé par une requête déjà exécutée (à partir du cache, avec les statistiques d'exécution réelles) :

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

Voir aussi

• Perf top sql by buffer gets — identifier les requêtes coûteuses en lectures logiques
• Perf top sql by disk reads — identifier les requêtes coûteuses en lectures physiques
• Profil de performance — diagnostics de performance plus larges (AWR, SQL Profile)