Oracle – 11) Pièges, mythes & anti-patterns

11.1 Mythe du “paramètre magique” : pourquoi c’est un piège récurrent

Pourquoi on y croit (et pourquoi c’est faux)

Parce que certains paramètres changent beaucoup de choses… mais rarement dans le sens attendu.
Parce qu’un paramètre touche plusieurs workloads (OLTP + batch) et crée des effets de bord.
Parce qu’on confond “ça a marché une fois” avec “c’est une règle”.

Anti-pattern : “On a un problème → on monte la mémoire → ça passera”. En prod, ça finit souvent en swap ou en cache OS détruit.

SGA/PGA : l’illusion du “plus = mieux”

Action	Ce qu’on espère	Ce qui arrive parfois
SGA ↑	plus de cache, moins I/O	cache OS ↓, I/O global ↑
PGA ↑	moins de spills TEMP	overcommit, OS pression, instabilité
Both ↑	“ça va mieux”	swap + latence partout

Règle : augmenter mémoire uniquement si les workarea histograms / TEMP spill prouvent le besoin, et si l’OS a le headroom.

Optimizer params : le risque “plan tsunami”

Changer un paramètre optimiseur peut modifier des centaines/milliers de plans.
Sans baselines, tu “lâches un tsunami” en prod : quelques plans gagnent, d’autres cassent.
Les regressions arrivent parfois plus tard (données qui évoluent).

Production-safe : préférer un changement ciblé (SQL/profile/baseline) plutôt qu’un knob global.

Règle safe (simple)

1) Prouver le goulot (AWR/ASH + métriques OS/app)
2) Changer le plus petit levier possible (ciblé)
3) Mesurer avant/après (fenêtre comparable)
4) Rollback prêt (script + seuils)
5) Documenter (ticket + post-mortem)

11.2 Tuning sans mesure : comment on fabrique des régressions invisibles

Signaux

“On a changé X hier, ça a l’air mieux” → aucune preuve.
Amélioration sur une requête mais hausse des timeouts ailleurs.
Les incidents reviennent “aléatoirement” (effets secondaires non mesurés).

Le coût caché

Tu perds la causalité (impossible de savoir ce qui a marché).
Tu crées une dette : config instable, runbooks inexistants.
Tu augmentes le risque sécurité : changements en urgence, sans contrôle.

Sans baseline, tu ne peux pas prouver une amélioration : tu ne peux pas non plus prouver une régression.

Fix

Définir 3-5 métriques SLA (p95/p99, erreurs, throughput).
Capturer AWR/ASH + top SQL_ID + top waits avant.
1 changement à la fois + stop rules.
Comparer sur fenêtre comparable.

Baseline mini (si tu n’as que 10 minutes)

- p95/p99 app + erreurs
- DB Time / DB CPU
- top waits (non-idle)
- top 10 SQL_ID
- TEMP top consumers
- UNDO/redo signaux (v$undostat + log file sync)

11.3 Stats “sauvages” : régressions de plans, histograms partout, instabilité

Pourquoi les stats cassent parfois

Le CBO change de décision si la cardinalité estimée bouge.
Les données évoluent : la “vérité” statistique d’hier n’est pas celle d’aujourd’hui.
Une recollect globale peut changer trop de plans en même temps.

Les stats ne sont pas “bonnes” ou “mauvaises” : elles sont plus ou moins adaptées à ton workload.

Histograms : utiles, mais pas partout

Cas	Histo utile ?	Risque
Skew fort sur colonne filtrée	souvent oui	plan sensible aux valeurs
Colonne très uniforme	souvent non	bruit / instabilité
Beaucoup de binds	attention	peeking / ACS

Anti-pattern : “mettons des histograms partout” → tu crées de l’instabilité.

Régressions de plan : ce qui arrive en prod

Un plan “meilleur” pour un cas devient “pire” pour un autre (valeurs différentes).
Le plan change après recollect, sans que personne ne comprenne.
Régression silencieuse : plus de DB Time, SLA qui se dégrade sur certains endpoints.

Safe mode (recommandation opérationnelle)

Recollect ciblée (table/index impliqués), pas “tout le schéma” par réflexe.
Exporter stats avant → rollback possible.
Valider sur fenêtre comparable (AWR/ASH).
Prévoir plan stability (baselines) sur SQL critiques.

Golden rule :
Stats = change control (même niveau de rigueur qu’un déploiement).

11.4 Over-indexing : quand “plus d’index” rend tout plus lent

Le coût réel d’un index

INSERT/UPDATE/DELETE plus lents (maintenance index).
Redo/undo ↑, contention possible.
Space + maintenance (rebuild, stats, partition indexes).
Plans instables si index “semblables” (CBO hésite).

Un index est un contrat : tu payes le coût à chaque DML.

Signaux d’over-indexing

Beaucoup d’index peu utilisés (rarely used).
DML coûteux (log file sync, buffer busy, latch) pendant pics.
Index “doublons” (même préfixe de colonnes).

Approche safe

Créer index uniquement si SQL_ID critique prouve le gain (plan + stats).
Vérifier impact DML et volumétrie.
Prévoir rollback (drop index) + fenêtre si besoin.
Documenter : “quel SQL est amélioré ?”

Nettoyage : enlever les index fantômes

Process:
1) Identifier index jamais utilisés (sur période significative)
2) Vérifier absence de dépendances (constraints/queries batch)
3) Désactiver/retirer progressivement (staging -> prod)
4) Monitorer SLA + plans

11.5 Hints partout : patch rapide, dette durable

Quand un hint est acceptable

Tu as un SQL critique, une preuve, et une validation stricte.
Le hint est temporaire, documenté, avec revalidation planifiée.
Tu as un rollback simple (retirer hint / baseline).

Quand c’est une mauvaise idée

Tu mets des hints “par défaut” dans tout le code.
Tu utilises hints pour compenser des stats pourries / modèle de données mauvais.
Tu ne documentes pas et tu ne revalides jamais.

Risques

Plan “figé” qui devient mauvais quand la data change.
Compatibilité version/patch : comportement de hint peut évoluer.
Les hints masquent la cause racine (dette).

Un hint non maîtrisé est souvent un “bug latent” en attente du prochain pic de charge.

Policy simple

- Hint = ticket obligatoire
- Hint = SQL_ID identifié + preuve AWR/ASH
- Hint = validation avant/après
- Hint = date de revalidation
- Hint = rollback documenté

11.6 Optimiser le mauvais problème : symptôme ≠ cause

Carte “waits → direction”

Wait dominant	Souvent	Piste
db file sequential read	index lookup I/O	index/plan/data model
log file sync	commit latency	app commits, redo I/O
enq: TX	locks	app pattern, hot rows
direct path * temp	spills TEMP	workareas/plan/cardinality
CPU (DB CPU)	compute bound	SQL rewrite, reduce rows

Exemples typiques

On “optimise I/O” alors que c’est du lock contention → aucun gain.
On monte PGA pour TEMP spill alors que la cardinalité est fausse → TEMP explose quand même.
On ajoute un index mais le problème est log file sync (commits).

Méthode

Identifier 1 symptom mesurable (SLA, DB Time).
Identifier 1 cause dominante (top waits / SQL_ID).
Formuler 1 hypothèse testable.
1 changement, puis validation.

Red flags

Tu changes “beaucoup” sans comprendre “pourquoi”.
Le wait dominant ne bouge pas malgré tes actions.
Tu n’as pas de fenêtre comparable (bruit).

11.7 “Quick wins” dangereux : accélérer 1 truc, casser le reste

Quick wins toxiques

Monter DOP “pour que ça aille plus vite”.
Monter PGA “pour supprimer TEMP”.
Monter SGA “pour supprimer I/O”.
Recollect stats global “pour corriger CBO”.

Pourquoi c’est dangereux

Tu consommes une ressource commune (CPU/I/O/mémoire) partagée par tous.
Tu déplaces le goulot (TEMP → I/O, CPU → contention).
Tu modifies beaucoup de comportements d’un coup.

Garde-fous

- caps DOP par service
- stop rules TEMP/CPU/I/O
- 1 change à la fois
- validation SLA p95/p99
- rollback prêt

Alternatives safe

Réduire A-Rows : filtres, pruning, agrégation tôt.
Corriger cardinalité : stats ciblées, histo justifiés.
Améliorer plan : index utile, rewrite join.
Gérer coexistence : fenêtre batch, throttle PQ.

11.8 Anti-patterns applicatifs : ce qui tue Oracle (et comment le voir)

Liste (top) anti-patterns

Pas de bind variables → hard parse, mutex, library cache.
N+1 queries → milliers de petites requêtes.
Commits trop fréquents → log file sync dominant.
Pagination naïve (OFFSET énorme) → reads inutiles.
Hot rows → contention TX / buffer busy.

Symptômes (vues Oracle)

Symptôme	Wait/Signal	Cause probable
Parse élevé	library cache / mutex	no binds
Commit lent	log file sync	commits fréquents
Locks	enq: TX	hot rows / longs tx
Reads énormes	db file * read	pagination naïve

Fix côté app

Introduire binds partout (prepared statements).
Regrouper commits (batch) et réduire roundtrips.
Éliminer N+1 (join, eager fetch, batch queries).
Optimiser pagination (keyset pagination).
Éviter hot rows (sharding logique, sequence, design).

Preuve (workflow)

1) Identifier SQL_ID top (AWR/ASH)
2) Vérifier parse/calls/executions (ratio)
3) Lire waits dominants
4) Corréler à un pattern code (module/action)
5) Fix app -> re-mesurer

A · Checklist anti-régressions (prod)

Avant

Baseline : p95/p99 + erreurs + throughput
AWR/ASH : top waits + top SQL_ID
Headroom : CPU/I/O/TEMP/UNDO
Rollback prêt (scripts + seuils)
1 changement défini (pas “une liste”)

Pendant

Must-not-break : SLA + erreurs + TEMP + CPU + I/O
Si nouveau wait dominant apparaît → stop
Si gain pas prouvable (bruit) → hold

Après

Comparer sur fenêtre comparable
Documenter delta (DB Time, waits, top SQL)
Valider app (p95/p99, erreurs)
Mettre à jour runbook / knowledge base

Stop rules (exemples)

- p99 +30% sur 5 minutes
- erreurs/timeouts > seuil
- TEMP > 90% et croissance
- CPU run queue > X stable
- I/O latency > seuil stable
- nouveau wait dominant critique inconnu

B · Blacklist : gestes à bannir (cause fréquente d’incidents)

Blacklist (top)

Changer optimizer params globalement sans preuve.
Recollect stats global “en prod” sans rollback.
Ajouter des index “au pif” (sans SQL_ID et plan).
Mettre des hints dans tout le code (copier-coller).
Monter DOP/PGA/SGA agressivement en pleine charge.
Faire 10 changements dans la même fenêtre et “espérer”.

Pourquoi c’est destructeur

Tu changes trop de comportements → effet tsunami.
Tu perds la causalité → impossible de diagnostiquer.
Tu crées des regressions silencieuses (SLA).

Alternative safe

Diagnostiquer : AWR/ASH → 1 SQL_ID / 1 wait dominant.
Changer petit : action ciblée + rollback prêt.
Valider sur fenêtre comparable.
Documenter et capitaliser.

Policy simple (prod)

- Toute action = ticket + baseline + rollback
- 1 changement par fenêtre
- Toute recollect stats = export avant + validation après
- Toute action global optimizer = interdite sauf exception majeure validée

⚠️ 11) Pièges, mythes & anti-patterns

Mythe du “paramètre magique”

Tuning sans mesure (le grand classique)

Stats “sauvages” & régressions de plans

Over-indexing (et index “fantômes”)

Hints partout (et dépendance au plan)

Optimiser le mauvais problème

“Quick wins” dangereux en prod

Anti-patterns applicatifs

Checklist anti-régressions

Blacklist (anti-patterns “à bannir”)