Perf explain plan

Perf explain plan affiche le plan d'exécution d'une requête préalablement analysée par EXPLAIN PLAN et stockée dans plan_table, sous forme d'arbre indenté reconstruit par une requête hiérarchique (CONNECT BY). C'est la méthode classique d'analyse de plan, encore valable, mais largement remplacée en pratique par le paquet DBMS_XPLAN depuis Oracle 10g.

Script

-- Affiche le plan d'exécution stocké dans plan_table sous forme d'arbre indenté,
-- pour le STATEMENT_ID fourni en variable de substitution.

SELECT
    LPAD(' ', 2 * level) || operation || ' ' || options || ' ' || object_name AS plan_step
FROM
    plan_table
WHERE
    statement_id = '&&statement_id'
START WITH
    id = 0
CONNECT BY
      PRIOR id           = parent_id
  AND statement_id = '&&statement_id'
ORDER SIBLINGS BY id
/

Le point de départ a été corrigé à id = 0 (racine du plan) plutôt que id = 1 — sur certains plans la racine porte l'identifiant 0, ce qui faisait disparaître la ligne de tête de l'arbre. ORDER SIBLINGS BY id garantit en plus que les branches d'un même niveau restent dans l'ordre d'exécution.

Alternative moderne

Depuis Oracle 10g, DBMS_XPLAN évite d'avoir à gérer soi-même plan_table et affiche en plus coûts, cardinalités et prédicats :

EXPLAIN PLAN SET STATEMENT_ID = 'mon_plan' FOR
  SELECT /* requête à analyser */ ... FROM ... WHERE ...;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'mon_plan', 'ALL'));

-- Pour le plan réellement utilisé par une requête déjà exécutée (plus fiable,
-- car il reflète les vraies valeurs de bind et le plan effectivement choisi) :
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

Voir aussi

• Perf top sql by disk reads — identifier les requêtes candidates à analyser
• Perf top sql by buffer gets — idem, côté lectures logiques
• Perf performance snapshot — vue d'ensemble instance au moment de l'analyse