Oracle – 3) Optimizer & statistiques

3.1 CBO : comment Oracle décide d’un plan

La chaîne logique (simple)

Étape	Entrées	Sorties
Parse	SQL + environnement	cursor + metadata
Estimation	stats (NDV, density, histo)	cardinalités
Recherche	accès + join order	plans candidats
Choix	coûts CPU/I/O	plan final

La phrase DBA à retenir

CBO choisit le plan le moins “coûteux”
selon ses estimations.
Si les estimations sont fausses,
le plan sera mauvais.

Donc : stats + cardinalité = la racine du problème (souvent).

De quoi dépend le “cost”

Cardinalité estimée (nb lignes) : le facteur #1.
Chemin d’accès : index range scan vs full scan.
Type de join : NL / HASH / MERGE.
Paramètres / features : adaptive, parallel, optimizer settings.

Symptôme typique :
- Estimation 10 lignes (CBO) -> NL join
- Réalité 10M lignes -> NL join catastrophique

Choix de join (raccourci mental)

Join	Quand c’est bon	Quand ça tue
Nested Loops	petit outer + index inner	outer énorme / index mauvais
Hash Join	volumes moyens/grands	PGA insuffisante → spill TEMP
Merge Join	données déjà triées / index	sort coûteux (CPU/TEMP)

Mythes courants

“Un index = toujours plus rapide” (non, dépend cardinalité + clustering + selectivity).
“Le cost est du temps réel” (non, c’est un modèle interne, pas des ms).
“Les hints sont la solution” (souvent un patch, pas une stratégie).

Règle :
Avant de mettre un hint,
prouve le problème d’estimation
(stats/histograms/corrélations).

3.2 Statistics : collecte & stratégie DBMS_STATS

Stratégie stats “propre”

Stats régulières, prévisibles, sur fenêtres contrôlées (éviter surprises prod).
Éviter les changements massifs “à l’aveugle” : schema entier sans justification.
Le but : réduire la variance → plans stables.

Paramètres clé (concepts)

estimate_percent method_opt granularity cascade

Stale stats & auto stats

Concept	Idée	Risque
STALE	modifs données dépassent seuil	plans dérivent sans prévenir
AUTO TASK	maintenance window	changement plan en prod
LOCK STATS	figer stats sensibles	bloquer évolution réelle

Bon réflexe :
- Identifier les tables critiques
- Mettre un calendrier stats contrôlé
- Sauvegarder stats avant refresh

Partitions : incremental & granularity

Sur gros volumes partitionnés : privilégier stats au niveau partition + global.
Incremental stats : recalcul “intelligent” pour éviter full refresh.
Attention aux stats globales incohérentes (partitions très inégales).

Symptôme classique :
- nouvelle partition chargée
- stats globales pas recalculées
- estimation skewée -> plan catastrophique

Backup/Restore : ton parachute

Avant refresh de stats “risqué”, sauvegarder (staging / prod).
Après : comparer plan hash + perf, et rollback si besoin.

Principe :
1) export stats (avant)
2) gather stats (test)
3) comparer plans & perf
4) import stats (rollback) si régression

3.3 Cardinalité : l’estimation qui pilote tout

Pourquoi c’est vital

Cardinalité (estimée) -> choix accès (index/scan)
                      -> choix join (NL/HASH/MERGE)
                      -> mémoire/temp (hash area)
                      -> temps total

Si la cardinalité est fausse, tout le plan peut partir en vrille.

Sources d’erreurs d’estimation

Skew (valeurs très fréquentes) sans histogram.
Corrélations colonnes (Oracle suppose souvent indépendance).
Predicats sur fonctions (UPPER(col), TRUNC(date)) sans stats d’expression.
Prédicats complexes / OR / IN volumineux.
Stats obsolètes / sample trop faible / NDV erroné.

Bind variables (peeking / sensitivity)

Le CBO peut “voir” la valeur bind au parse et choisir un plan.
Si la distribution est skewée, un plan bon pour 1 valeur est mauvais pour une autre.
Le système peut chercher des stratégies (bind-aware / adaptive cursor sharing).

Cas typique :
:country='FR' -> 80% des lignes -> full scan
:country='IS' -> 0.01% -> index range scan

Leviers (dans l’ordre)

Vérifier stats (stale ? NDV ? density ?)
Ajouter histogram si skew réel (sur colonne vraiment filtrante)
Corrélations : extended stats / column groups (selon version)
Expression stats si prédicats sur fonctions
Si nécessaire : SPM (baseline) / SQL patch (dernier recours)

3.4 Histograms : quand, comment, et les pièges

Quand un histogram est utile

Skew fort ET colonne utilisée en filtre dans des requêtes critiques.
Valeurs “ultra fréquentes” (hot values) qui faussent la selectivity.
Régressions de plans liées à “mauvaise selectivity”.

Si la distribution est uniforme,
un histogram apporte peu, voire du bruit.

Types (vue simplifiée)

Type	Usage	Note
Frequency	faible NDV, skew clair	très lisible
Top-Frequency	hot values	focus sur les valeurs dominantes
Hybrid	grande NDV	approximation plus complexe

Risques

Changement plan “surprise” après stats refresh.
Plans instables si la distribution bouge souvent.
Interaction avec binds (valeurs différentes = plan différent).

Histograms = puissant,
donc à cadrer :
- colonne critique
- preuve du skew
- test/validation avant prod

Règles pratiques (DBA)

Ne pas “histo partout”. Seulement sur colonnes filtrantes critiques.
Avant : capturer plan/perf, après : comparer plan hash + latence.
Prévoir rollback stats (import/export) si régression.

3.5 Régressions de plans : méthode de diagnostic

Signaux typiques

Une requête “toujours OK” devient lente du jour au lendemain.
Plan hash value change.
Cardinalités estimées très différentes.
Temps variable selon valeurs bind / heure / volume.

Comment prouver une régression

1) Identifier SQL_ID + plan_hash_value (avant / après)
2) Capturer stats (AWR/ASH ou v$sql)
3) Comparer :
   - elapsed / CPU / reads
   - cardinalités (estimated rows)
   - join order / accès
4) Relier à un changement :
   - stats refresh
   - patch / paramètre
   - volume / data skew

Causes les plus fréquentes

Cause	Indice	Ce que ça change
Stats modifiées	last_analyzed récent	selectivity/cost
Skew évolue	distribution change	index vs scan
Bind valeurs	perf dépend param	plan “moyen”
Param optimizer	init.ora change	search space
Data growth	table grossit	join algo change

Actions (ordre recommandé)

Valider stats / histograms / corrélations
Tester en staging (mêmes stats + même bind)
Corriger estimation (extended/expression stats)
Stabiliser : SPM baseline / profile (si nécessaire)
Rollback : restore stats / disable baseline

Philosophie :
Réparer la cause (estimation)
avant de figer le symptôme (baseline).

3.6 Plans adaptatifs & stabilité (SPM)

Adaptive plans (idée)

Oracle peut choisir entre options pendant l’exécution (selon observations).
But : éviter certains mauvais choix quand les estimations sont incertaines.
Attention : ça ne remplace pas de bonnes stats.

Adaptive = correctif dynamique,
pas une excuse pour stats cassées.

SQL Plan Management (SPM)

Objet	But	Usage
Baseline	plan “approuvé”	stabilité en prod
Evolve	tester plan alternatif	adopter si meilleur
Capture	collecter plans	audit / contrôle

SPM est top quand tu dois éviter les surprises (apps critiques).

Outils “stabilisation” (du + propre au + violent)

Corriger estimation (stats, histograms, extended stats)
SQL Plan Baseline (SPM)
SQL Profile (si tu as le contexte/licence)
SQL Patch / Hints (dernier recours, documenter)

Objectif :
contrôler le plan sans figer l’évolution inutilement.

Quand figer un plan ?

Régression fréquente et coûteuse, application critique.
Fenêtres de changements “risquées” (stats/patchs) où tu veux de la stabilité.
Le temps de corriger la cause (modèle de données / requête / corrélations).

Bon compromis :
Baseline temporaire + plan de correction durable.

A · Diagrammes (Mermaid-ready)

Chaîne décision CBO

flowchart TD
  SQL[SQL parse] --> STATS[Stats: NDV/Density/Histo]
  STATS --> CARD[Cardinalités estimées]
  CARD --> PLANS[Plans candidats]
  PLANS --> COST[Coûts CPU/I/O]
  COST --> CHOICE[Plan choisi]
  CHOICE --> EXEC[Exécution]

Stats → Cardinalité → Join

flowchart LR
  S[Stats] --> C[Cardinalité]
  C --> A[Accès (index/scan)]
  C --> J[Join algo (NL/HASH/MERGE)]
  J --> P[Perf]

Cycle régression

flowchart TD
  OK[Plan stable] --> CHG[Changement: stats/patch/data/bind]
  CHG --> NEW[Plan différent]
  NEW --> SLOW[Lenteur]
  SLOW --> PROVE[Preuve: SQL_ID + plan_hash + metrics]
  PROVE --> FIX[Fix: stats/estimation/SPM]
  FIX --> OK2[Retour stabilité]

B · Commandes & vues (stats, histograms, plans)

Stats : inspecter / dater

-- Tables stats (date, num_rows, blocks, sample_size)
SELECT owner, table_name, num_rows, blocks, sample_size, last_analyzed
FROM dba_tab_statistics
WHERE owner = UPPER(:owner)
ORDER BY last_analyzed DESC NULLS LAST;

-- Index stats
SELECT owner, index_name, num_rows, distinct_keys, clustering_factor, last_analyzed
FROM dba_ind_statistics
WHERE owner = UPPER(:owner)
ORDER BY last_analyzed DESC NULLS LAST;

-- Stats stale ?
SELECT owner, table_name, stale_stats
FROM dba_tab_statistics
WHERE owner = UPPER(:owner)
ORDER BY stale_stats DESC, table_name;

Histograms : présence & colonnes

-- Colonnes avec histogram
SELECT owner, table_name, column_name, histogram, num_distinct, density, num_buckets
FROM dba_tab_col_statistics
WHERE owner = UPPER(:owner)
  AND histogram <> 'NONE'
ORDER BY table_name, column_name;

-- Détails histogram (buckets)
SELECT owner, table_name, column_name, endpoint_number, endpoint_value
FROM dba_tab_histograms
WHERE owner = UPPER(:owner)
  AND table_name = UPPER(:table)
  AND column_name = UPPER(:column)
ORDER BY endpoint_number;

Plans : afficher & comparer

-- Plan depuis cursor cache (si dispo)
SELECT * FROM TABLE(dbms_xplan.display_cursor(:sql_id, NULL, 'ALLSTATS LAST +PEEKED_BINDS +OUTLINE'));

-- Infos SQL (plan hash, executions, elapsed, reads)
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 AWR)
SELECT * FROM TABLE(dbms_xplan.display_awr(:sql_id, :plan_hash_value, NULL, 'OUTLINE'));

Régression : “preuve minimale”

1) Identifier SQL_ID
2) Capturer plan_hash_value avant/après
3) Capturer métriques :
   - elapsed, cpu, reads, rows
4) Capturer stats date:
   - last_analyzed table/index
5) Si besoin : restaurer stats ou activer baseline (SPM)

C · Runbook “Optimizer/Stats/Regression” (template)

Template ticket “Optimizer”

[Ticket] Optimizer / Stats / Plan

SQL
- SQL_ID :
- Module/Service :
- Période (fenêtre) :
- Symptôme :

Plan
- plan_hash_value (avant) :
- plan_hash_value (après) :
- Diff join/access :

Stats
- Tables impactées :
- last_analyzed :
- stale_stats :
- histograms (O/N) :

Hypothèse
- Cardinalité fausse ? (O/N)
- Skew ? corrélations ? binds ?

Action prévue
- Stats refresh contrôlé ? / histo ? / extended/expression stats ? / baseline ?

Avant/Après (preuve)

Avant :
- elapsed (p95/p99 si dispo) :
- executions :
- buffer_gets / disk_reads :
- rows processed :
- wait events dominants :

Après :
- mêmes métriques
- delta (%)
- plan_hash_value confirmé

Rollback (toujours prévu)

Options rollback :
1) Restore stats (import) si refresh a régressé
2) Disable histogram / revenir method_opt
3) Disable baseline/profile si effet négatif
4) Revenir param optimizer (si changé)

🧠 3) Optimizer & statistiques (CBO)

CBO : comment Oracle “pense”

Statistics : collecte & stratégie

Cardinalité : l’estimation qui casse tout

Histograms : utiles… mais dangereux

Régressions de plans : prévenir & diagnostiquer

Plans adaptatifs & stabilité (SPM)

Diagrammes (Mermaid-ready)

Commandes & vues (stats/plan)

Runbook : “plan regression” (template)

La chaîne logique (simple)

La phrase DBA à retenir