Oracle – 4) Tuning SQL & plans d’exécution

4.1 Lire un plan : DBMS_XPLAN (pratique DBA)

Plan “utile” : pas PLAN_TABLE, mais le plan exécuté

display_cursor : plan réel du cursor cache (si en mémoire).
ALLSTATS LAST : A-Rows, buffers, temps par opération (si stats dispo).
Lire de bas en haut : sources → joins → agrégations → projection.

La commande “de base”

SELECT * FROM TABLE(
  dbms_xplan.display_cursor(
    sql_id => :sql_id,
    cursor_child_no => NULL,
    format => 'ALLSTATS LAST +PEEKED_BINDS +OUTLINE +PREDICATE'
  )
);

Sans ALLSTATS LAST, tu lis un plan “théorique”.

E-Rows vs A-Rows = détecteur de cardinalité cassée

Cas	Symptôme	Action
A ≫ E	estimations trop faibles	stats/histo/corrélations
A ≪ E	estimations trop hautes	selectivity / predicates
A≈E mais lent	plan OK mais I/O/CPU	index/rewrite/partition

Heuristique :
Si E-Rows est faux ×100 ou ×1000,
le plan a de grandes chances d’être mauvais.

Predicates : FILTER vs ACCESS

ACCESS : prédicat utilisé pour accéder (index range scan par ex.).
FILTER : appliqué après lecture (plus coûteux).
Objectif : transformer FILTER en ACCESS quand ça fait sens.

Exemples fréquents :
- fonction sur colonne => FILTER (index inutilisé)
- conversion implicite => FILTER
- OR => FILTER (souvent)

Checklist lecture plan (5 minutes)

SQL_ID + child cursor ? (binds / ACS)
Plan hash value stable ?
E-Rows vs A-Rows (où ça diverge ?)
Opérateur dominant (temps/buffers) ?
Predicates ACCESS/FILTER corrects ?

Toujours corréler plan + métriques (elapsed/CPU/reads),
sinon tu “tunes du papier”.

4.2 Anti-patterns SQL (Top 15) – et comment les corriger

Les classiques (qui coûtent cher)

Anti-pattern	Pourquoi c’est mauvais	Symptôme plan
SELECT *	I/O & réseau inutiles	TABLE ACCESS FULL + large bytes
Fonction sur colonne	index inutilisable	FILTER / full scan
LIKE '%xxx%'	pas de range scan	full scan / large reads
OR non sargable	dégrade access	FILTER / concat
Conversion implicite	stats & access cassés	FILTER + cardinalité fausse
DISTINCT abusif	sort/agg inutile	SORT UNIQUE
N+1	trop d’exécutions	executions énormes
Pagination naïve	scan & sort massif	SORT ORDER BY + full scan

Avant / Après (exemples)

-- 1) Fonction sur colonne (mauvais)
WHERE TRUNC(order_date) = :d

-- mieux : range predicate (sargable)
WHERE order_date >= :d
  AND order_date <  :d + 1

-- 2) OR pénalisant
WHERE status='A' OR status='B'

-- mieux : IN (souvent)
WHERE status IN ('A','B')

-- 3) LIKE '%x%' (full scan)
WHERE name LIKE '%MARTIN%'

-- alternative : index text / search (selon besoin)

Recettes de correction

Rendre les predicates “sargables” (range, pas de fonction sur colonne).
Réduire tôt la cardinalité (filtrer avant de joindre).
Éviter DISTINCT “pour réparer” un JOIN (corriger le JOIN).
Traquer N+1 via logs/APM : regrouper en 1 requête.

Objectif :
moins de lignes lues,
moins de blocs touchés,
moins d’exécutions.

En prod : méthode safe

Isoler SQL_ID + métriques
Tester rewrite en staging
Comparer plan hash + A-Rows + elapsed
Déployer avec rollback (SPM si besoin)

4.3 Réécritures SQL & accès (réduire I/O, CPU, cardinalité)

Principes d’or

Filtrer le plus tôt possible (réduire A-Rows avant joins).
Éviter de ramener des colonnes inutiles (bytes).
Éviter les sorts (ORDER BY) coûteux sans index adapté.
Optimiser la cardinalité, pas seulement le “cost”.

Plan idéal :
peu de rows tôt,
accès index quand utile,
joins adaptés au volume,
pas de sort massif.

EXISTS vs IN (et semi-joins)

-- IN (attention au volume / nulls selon cas)
WHERE c.id IN (SELECT customer_id FROM orders WHERE ...)

-- EXISTS (souvent meilleur pour “existence”)
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id
    AND ...
)

EXISTS exprime mieux l’intention : “je veux savoir si ça existe”.

Join order & réduction de cardinalité

Joindre tôt une table énorme = risque majeur.
Si possible : filtrer une sous-population (CTE/subquery) puis join.
Attention aux CTE matérialisées (selon version/paramètres) → tester.

Pagination : anti-pattern “ORDER BY + OFFSET énorme”

-- Mauvais (offset grand = scan+sort énorme)
ORDER BY created_at DESC
OFFSET :n ROWS FETCH NEXT :m ROWS ONLY

-- Mieux (keyset pagination)
WHERE created_at < :last_seen
ORDER BY created_at DESC
FETCH NEXT :m ROWS ONLY

Keyset pagination évite de “trier tout le monde” à chaque page.

4.4 Bind variables : performance & stabilité (mais attention au skew)

Pourquoi on aime les binds

Réduit hard parse (reutilisation curseurs)
Améliore stabilité (moins de variations SQL text)
Réduit pression sur shared pool

Hard parse : symptômes & impacts

CPU côté parse, latch contention, library cache locks
beaucoup de “similar SQL” avec littéraux différents

Bon indicateur :
si executions ≫ parse calls,
c’est sain.
Si parse calls explosent,
le système brûle du CPU.

Adaptive Cursor Sharing (ACS)

Quand la distribution est skewée, Oracle peut maintenir plusieurs child cursors pour des valeurs bind “différentes” (bind-sensitive).

Cas :
:country='FR' -> full scan
:country='IS' -> index range scan
=> Oracle peut gérer 2 plans (child cursors)

Pièges

Skew + 1 seul plan = perf variable (p95/p99 instables)
Conversions implicites sur binds (types) => index ignoré
“Cursor sharing force” peut masquer des problèmes (à manier avec précaution)

4.5 Indexes : choisir, valider, éviter la sur-indexation

Règles DBA (simple)

Un index n’est utile que si la selectivity est bonne.
Le clustering factor indique si l’index “saute partout” sur la table.
Index = lecture + coût d’écriture (DML plus lent).

Composite index : ordre des colonnes

-- colonnes d’égalité d’abord, range ensuite
WHERE a = :a AND b = :b AND c BETWEEN :x AND :y
=> index (a,b,c)

L’ordre est critique : sinon l’index peut être inutilisable.

Covering index (index-only)

Si toutes les colonnes demandées sont dans l’index, Oracle peut réduire les table lookups.
Mais attention taille index et coût DML.

À éviter

Sur-indexer “au cas où”
Index sur colonnes très faibles NDV (ex: sexe) sans contexte
Index redondants (mêmes prefixes)

4.6 Hints (prudence) & stabilité (SPM)

Quand on touche aux hints ?

Quand tu as prouvé une erreur d’estimation et que tu dois stabiliser vite.
Quand tu n’as pas la main sur l’app et qu’il faut une rustine contrôlée.
Jamais “en première intention” : corriger stats/cardinalité d’abord.

Hints : avantages & dangers

+	-
Forcer un join / index	Fragile si data évoluent
Contenir une régression	Peut bloquer un plan meilleur
Debug rapide	Maintenance + dette

Hints = chirurgie.
Documenter : pourquoi, quand, rollback.

SPM (SQL Plan Baselines) : plus propre

Permet d’approuver un plan connu bon.
Évite surprises lors refresh stats / patch.
Peut évoluer (evolve) de manière contrôlée.

Rollback

Rollback options :
- désactiver baseline/profile/patch
- restaurer stats si changement
- revert param optimizer
- revenir à l’ancienne version SQL

A · Diagrammes (Mermaid-ready)

Workflow tuning SQL

flowchart TD
  A[Identifier SQL_ID] --> B[Capturer plan exécuté (XPLAN ALLSTATS)]
  B --> C[Localiser l'opérateur dominant]
  C --> D[Comparer E-Rows vs A-Rows]
  D --> E[Hypothèse: stats/cardinalité/index/rewrite]
  E --> F[Test en staging]
  F --> G[Validation avant/après]
  G --> H[Déploiement + rollback]

Arbre décision : rewrite / index / hint

flowchart TD
  P[SQL lent] --> R[Lire plan + métriques]
  R --> K{E-Rows proche A-Rows ?}
  K -- Non --> S[Corriger stats/histo/corrélations]
  K -- Oui --> O{Opérateur dominant ?}
  O --> I[Index / accès]
  O --> J[Join algo]
  O --> T[Sort/Aggregation]
  I --> W[Rewrite predicate / index ciblé]
  J --> X[Réduire cardinalité tôt / rewrite]
  T --> Y[Index pour ORDER BY / réduire rows]
  W --> Z{Stabilité ?}
  Z -- Non --> B1[SPM baseline / hints prudents]

B · Commandes (DBMS_XPLAN + V$) – toolkit SQL tuning

Trouver le SQL_ID

-- si tu as un morceau du texte SQL
SELECT sql_id, executions, elapsed_time/1e6 elapsed_s
FROM v$sql
WHERE sql_text LIKE '%your_fragment%'
ORDER BY elapsed_time DESC FETCH FIRST 20 ROWS ONLY;

-- sessions actives + sql_id
SELECT s.sid, s.serial#, s.username, s.status, s.sql_id, s.event
FROM v$session s
WHERE s.status='ACTIVE'
ORDER BY s.last_call_et DESC;

Afficher plan exécuté (le vrai)

SELECT * FROM TABLE(
  dbms_xplan.display_cursor(
    sql_id => :sql_id,
    cursor_child_no => NULL,
    format => 'ALLSTATS LAST +PEEKED_BINDS +OUTLINE +PREDICATE'
  )
);

Métriques (v$sql)

SELECT sql_id, plan_hash_value, executions,
       elapsed_time/1e6 elapsed_s, cpu_time/1e6 cpu_s,
       buffer_gets, disk_reads, rows_processed
FROM v$sql
WHERE sql_id = :sql_id;

Plan depuis AWR (si licence)

SELECT * FROM TABLE(
  dbms_xplan.display_awr(
    sql_id => :sql_id,
    plan_hash_value => :phv,
    db_id => NULL,
    format => 'OUTLINE'
  )
);

C · Runbook SQL tuning (template)

Template ticket

[Ticket] SQL tuning

SQL
- SQL_ID :
- Module/Service :
- Fenêtre :
- Symptôme (p95/p99/timeout) :

Plan
- plan_hash_value :
- Opérateur dominant :
- E-Rows vs A-Rows (où diverge ?) :

Hypothèse
- stats/cardinalité ?
- anti-pattern identifié ?
- index manquant / mauvais ?
- bind skew / ACS ?

Action
- rewrite :
- index :
- stabilité (baseline/hint) :

Avant/Après

Avant :
- elapsed (p95/p99) :
- executions :
- buffer_gets / disk_reads :
- rows processed :
- waits dominants :

Après :
- mêmes métriques
- delta (%)
- plan hash stable ?

Rollback

Rollback options :
- revert SQL rewrite
- drop index (si inutile)
- disable baseline/profile/patch
- restore stats (si changées)

💚 4)ORACLE : Tuning SQL & plans d’exécution

Lire un plan : DBMS_XPLAN (vrai usage)

Anti-patterns SQL (Top 15)

Réécritures & accès

Bind variables : performance & stabilité

Indexes : choisir, valider, éviter

Hints (avec prudence) & stabilité (SPM)

Diagrammes (Mermaid-ready)

Commandes & scripts (DBMS_XPLAN + V$)

Runbook SQL tuning (template)

Plan “utile” : pas PLAN_TABLE, mais le plan exécuté

La commande “de base”