Optimisation SQL Oracle : guide pratique

1. Identifier d'abord les requêtes coûteuses

Si vous disposez du Diagnostics Pack, un rapport AWR (ou une requête ASH ciblée) est le moyen le plus rapide d'identifier ce qui coûte réellement cher sur l'instance, classé par temps écoulé total, CPU ou buffer gets plutôt que sur une seule exécution lente :

SELECT sql_id, executions, elapsed_time/1e6 AS elapsed_sec,
       buffer_gets, elapsed_time/NULLIF(executions,0)/1e6 AS avg_sec
FROM v$sqlarea
ORDER BY elapsed_time DESC
FETCH FIRST 20 ROWS ONLY;

Pas de licence Diagnostics Pack ? Statspack (gratuit, fourni avec la base) offre le même type de rapports à partir de snapshots, sans contrainte de licence, avec simplement une interface moins fine.

Triez par temps écoulé total, pas par moyenne — un sql_id exécuté 200 000 fois par jour à 15 ms pèse plus lourd qu'un autre exécuté 10 fois à 2 secondes. Une fois un candidat identifié, récupérez son plan directement à partir de son sql_id plutôt que de le deviner à la lecture du code source.

2. Lire le plan d'exécution

EXPLAIN PLAN affiche l'estimation de l'optimiseur sans rien exécuter :

EXPLAIN PLAN FOR
SELECT * FROM orders WHERE customer_id = 42 AND status = 'PENDING';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Pour une requête déjà exécutée, DBMS_XPLAN.DISPLAY_CURSOR récupère dans le cache des curseurs le plan réellement utilisé et, avec STATISTICS_LEVEL=ALL (ou le hint /*+ gather_plan_statistics */), affiche les nombres de lignes réels à côté des estimations :

SELECT * FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(sql_id => '&sql_id', format => 'ALLSTATS LAST')
);

La colonne à surveiller est E-Rows vs A-Rows — lignes estimées contre lignes réelles à chaque étape. Un écart important et récurrent est le signe le plus fiable que l'optimiseur travaille sur des statistiques erronées et choisit, en conséquence, un ordre de jointure ou un chemin d'accès sous-optimal. Vérifiez aussi le Cost (une valeur relative, pas une unité de temps) et repérez toute étape en TABLE ACCESS FULL là où vous attendiez un index range scan.

3. Indexer à bon escient

• B-tree — le type par défaut, adapté aux colonnes à forte cardinalité utilisées dans des prédicats d'égalité et d'intervalle.
• Index bitmap — efficaces sur les colonnes à faible cardinalité (un indicateur de statut, un code région) pour des charges de reporting ou d'entrepôt de données majoritairement en lecture. À éviter sur les tables soumises à de fréquents DML concurrents : une entrée d'index bitmap couvre une plage de lignes, si bien que la mise à jour d'une seule ligne peut verrouiller une portion de l'index bien plus large qu'avec un B-tree.
• Index fonctionnels (function-based) — si vous filtrez sur UPPER(last_name), indexez directement UPPER(last_name) ; un index simple sur last_name ne sera pas utilisé.
• Index invisibles — créez et testez un index sans que l'optimiseur l'utilise par défaut (CREATE INDEX ... INVISIBLE), vérifiez son apport avec OPTIMIZER_USE_INVISIBLE_INDEXES au niveau de la session, puis rendez-le visible. Pratique pour tester un changement sur des données de production sans risquer de régression de plan.

Comme toute base relationnelle, Oracle n'indexe pas automatiquement les colonnes de clés étrangères. L'absence d'index sur les FK est une cause fréquente de jointures lentes et de verrous inutiles sur toute la table lors des suppressions de lignes parentes.

4. Les écritures de requêtes qui neutralisent les index

• Conversion implicite. Comparer une colonne VARCHAR2 à un littéral NUMBER (ou à une variable de liaison mal typée côté applicatif) désactive silencieusement l'index sur cette colonne.
• Fonctions sans index fonctionnel correspondant. WHERE TRUNC(created_at) = TRUNC(SYSDATE) ne peut pas utiliser un index simple sur created_at ; réécrivez la condition sous forme d'intervalle ou ajoutez un index fonctionnel.
• Jokers en début de motif. LIKE '%foo' ne peut pas exploiter un index B-tree standard. Pour une vraie recherche plein texte, utilisez Oracle Text.
• Pagination profonde de type OFFSET. Une pagination profonde avec OFFSET (ou l'ancien motif ROWNUM) oblige Oracle à calculer puis écarter les lignes sautées. Préférez une pagination par clé (keyset) sur une colonne de tri indexée et unique.

5. Statistiques et bind variable peeking

Deux problèmes sont suffisamment spécifiques à Oracle pour mériter d'être traités à part.

Statistiques obsolètes ou absentes

L'optimiseur basé sur les coûts repose entièrement sur les statistiques des objets. Après des modifications importantes des données, rafraîchissez-les explicitement plutôt que d'attendre la fenêtre de maintenance nocturne par défaut :

EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'APP', tabname => 'ORDERS', cascade => TRUE
);

Pour les colonnes à distribution asymétrique (une colonne de statut à 95 % 'COMPLETE'), assurez-vous que des histogrammes sont collectés (METHOD_OPT) ; sinon l'optimiseur suppose une distribution uniforme et se trompe lourdement sur la sélectivité des valeurs rares.

Bind variable peeking

Oracle « jette un œil » à la valeur d'une variable de liaison lors du premier hard parse d'une requête et construit un plan optimisé pour cette valeur précise. Si la colonne est asymétrique, le plan efficace pour une valeur fréquente peut être réutilisé — depuis le curseur en cache — pour une valeur rare à laquelle il est très mal adapté, ou inversement. L'adaptive cursor sharing (11g+) atténue ce problème pour les requêtes qu'il détecte comme sensibles à la valeur de liaison, mais ne couvre pas automatiquement tous les cas. Si un même sql_id affiche des performances très variables d'une exécution à l'autre, c'est la première piste à vérifier via DBMS_XPLAN.DISPLAY_CURSOR avec plusieurs curseurs enfants.

6. Surveiller et itérer

Conservez les snapshots AWR (ou Statspack) comme référence permanente, pas seulement comme outil de diagnostic ponctuel. Le SQL Tuning Advisor (DBMS_SQLTUNE) peut détecter automatiquement les régressions et suggérer des profils ou des index à examiner. Si un rafraîchissement des statistiques ou une montée de version a déjà fait basculer une requête critique vers un plan moins bon, les SQL Plan Baselines permettent de figer le plan reconnu comme performant tout en laissant l'optimiseur explorer des alternatives en toute sécurité.