⚡ 9) Parallel execution & TEMP
Le parallel (PQ) est un accélérateur… et parfois une arme de destruction massive : DOP, PX servers, PQ distribution, spill TEMP, saturation UNDO/redo et coexistence avec OLTP. Ici, on apprend à : gagner vite sans tuer le reste du système.
But #1 : SLA global
Accélérer un job sans casser le p95/p99 OLTP.
coexistence
Risque #1 : TEMP
Spill massif (sort/hash) + I/O + temps qui explose.
TEMP spill
Risque #2 : saturation
CPU run queue, I/O queueing, UNDO/redo, contention.
saturation
9.1
PQ 101 : concepts & architecture PX
Query Coordinator, PX servers, granules, distribution, DOP : comprendre pour maîtriser.
QCPXDOP
9.2
DOP : choix “safe” & auto DOP
DOP fixe vs auto, cap, fairness, que regarder avant d’augmenter.
Auto DOPCapFairness
9.3
Gains vs risques : quand PQ aide (vraiment)
Full scans, partitioned tables, ETL/OLAP vs OLTP : critères d’éligibilité.
OLAPETLNot OLTP
9.4
TEMP & spills : comprendre, mesurer, réduire
Sort/hash spills, distribution skew, workareas, symptômes AWR/ASH.
SpillSkewWorkarea
9.5
UNDO/Redo : saturation par parallélisme
PX DML, charge UNDO, commit path, redo contention : le coût “caché”.
UNDORedoLGWR
9.6
Coexistence workloads : protéger l’OLTP
Limiter, scheduler, fenêtres, caps, “resource discipline”, priorités.
ThrottleWindowsProtect
9.7
Diagnostiquer PQ : plans PX & métriques utiles
DBMS_XPLAN, PX stats, repartition, DFO trees, bottlenecks.
PX PLANDFOBottleneck
9.8
Anti-patterns PQ (la liste noire)
PQ sur petites tables, DOP délirant, skew extrême, joins mal designés, OLTP mix.
AntiSkewOLTP
A
Diagrammes (Mermaid-ready)
QC/PX pipeline, TEMP spill loop, coexistence OLTP/PQ.
copiablepédago
B
Runbook : PQ incident (CPU/TEMP)
Incident → preuve → mitigation immédiate → stabilisation → post-mortem.
IncidentMitigateRollback
C
Commandes & vues (PX/TEMP/UNDO)
V$PX_SESSION, V$PX_PROCESS, V$TEMPSEG_USAGE, V$UNDOSTAT, waits, AWR.
PXTEMPUNDO
9.1 Parallel Query 101 : architecture PX (QC, PX, DFO, distribution)
Les rôles
QC (Query Coordinator)
- session “chef d’orchestre”
- déclenche PX, agrège résultats
- peut devenir bottleneck si trop de trafic
PX Servers
- esclaves de calcul/scan/join/sort
- travaillent par granules (chunks)
- consomment CPU + mémoire + I/O
DFO (Data Flow Operation) : arbre de flux PX (producteurs/consommateurs) – le plan PX est une pipeline.
Pipeline mental model
QC
├─ DFO1: PX scan partition/table (PX producers)
├─ DFO2: PX join/sort/group by (PX redistribute)
└─ DFO3: QC aggregate/fetch (QC consumer)
- Le parallel peut accélérer un scan… mais aussi multiplier les I/O si le plan est mauvais.
- Le coût caché = redistribution (échanges PX) + skew (déséquilibre).
- Le gain dépend de la scalabilité réelle : CPU libres + I/O capables + absence contention.
Distributions : HASH / RANGE / BROADCAST
| Distribution | Quand | Risque |
|---|---|---|
| HASH | joins/group by sur clés | skew sur clés “hot” |
| RANGE | order by / range partition | déséquilibre si data non uniforme |
| BROADCAST | petite table vers tous PX | si table pas si petite → explosion trafic |
Skew = un PX fait 10× le boulot des autres → wall-clock dominé par 1 esclave.
Signaux “ça va mal se passer”
- QC devient saturé (beaucoup d’échanges PX).
- Skew : 1 PX “traîne”, les autres idle.
- TEMP explose : sorts/hash spills.
- OLTP latence ↑ : run queue CPU ↑, I/O queueing ↑, log file sync ↑.
9.2 DOP : choisir un degré de parallélisme “safe” (auto DOP, caps, fairness)
Le DOP n’est pas un “speed knob” linéaire
- Si tu doubles le DOP, tu doubles souvent la consommation (CPU/I/O/TEMP)… pas forcément le gain.
- Le gain est borné par le goulot : I/O, shuffle, skew, QC, contention.
- Le DOP doit respecter la coexistence : OLTP ≠ batch.
But : minimiser le temps total (job) sans augmenter le DB Time global et la latence OLTP.
Auto DOP (idée)
- Oracle peut décider du DOP selon statistiques, taille, et politique.
- Utiliser “auto” peut aider… mais il faut des caps et une discipline (sinon effet cascade).
- Auto DOP sans contrôle = risque d’ouvrir la porte à des DOP élevés sur jobs non prévus.
Heuristiques terrain
| Question | Si “oui” | Sinon |
|---|---|---|
| CPU libres ? | peut monter DOP | DOP ↑ = OLTP souffre |
| I/O capables ? | scan parallélisé utile | queueing → latence ↑ |
| Skew faible ? | scalabilité ok | 1 PX domine → gain nul |
| TEMP stable ? | ok | spill → job plus lent |
Garde-fous
- Limiter DOP max par classe de job/service.
- Fenêtres batch dédiées (no OLTP critical).
- Surveiller TEMP/UNDO/redo + I/O latency en live.
- “Kill switch” opérationnel : plan de mitigation si le PQ s’emballe.
Checklist avant DOP↑ :
- AWR/ASH sur fenêtre similaire
- TEMP headroom
- UNDO/redo headroom
- CPU run queue OK
- Storage latency OK
9.3 Gains vs risques : quand le PQ est le bon outil (et quand il faut éviter)
Quand PQ aide “vraiment”
- Gros scans (tables/partitions) en OLAP/ETL.
- Tables partitionnées : partition-wise joins, pruning, chunking.
- Workloads batch isolés (fenêtre dédiée, ressources prévisibles).
- Opérations parallélisables (agrégations lourdes, transform de gros volumes).
Signal favorable : la requête est dominée par une phase scalable (scan/compute) sans locks ni contention interne.
Quand PQ est une mauvaise idée
- OLTP : petites requêtes fréquentes, index lookups, transactions courtes.
- Workload déjà CPU-bound (run queue élevée).
- Stockage déjà en queueing (latence I/O monte).
- Plans instables, cardinalité fausse, skew extrême.
- Requêtes qui “spill” déjà en sériel : en PQ ça explose.
Checklist éligibilité
Éligible
- volumes élevés
- scan/compute dominant
- peu de contention
- fenêtre batch ou ressources garanties
- partition pruning efficace
À éviter
- workload mix OLTP
- TEMP déjà tendu
- I/O latence haute
- skew sur clés
- joins mal estimés
SLA : la vérité c’est l’impact global
Tu optimises un job = OK
Tu dégrades le p95/p99 OLTP = KO (même si ton job est plus rapide)
- Le PQ doit être “contenu” : caps, fenêtres, resource management.
- Mesure le coût : DB Time global, waits, top SQL, latence applicative.
9.4 TEMP & spills : réduire le “TEMP meltdown” (workareas, skew, redistribution)
Symptômes typiques
- Direct path read/write (temp) monte en flèche.
- Le job ralentit en montant le DOP (paradoxe).
- TEMP usage per session énorme, ORA-01652 possible.
- I/O latence ↑, “tout le monde” souffre.
Paradoxe classique : DOP↑ → plus de concurrent spill → TEMP I/O saturé → job plus lent.
Causes racines
| Cause | Pourquoi TEMP explose | Signal |
|---|---|---|
| Workarea insuffisante | hash/sort “onepass/multipass” | AWR workarea hist |
| Cardinalité fausse | join énorme non prévu | A-Rows >> E-Rows |
| Skew distribution | 1 PX fait un tri gigantesque | PX déséquilibrés |
| DOP trop haut | spill concurrent = I/O temp saturé | latence temp ↑ |
Mesurer (et attribuer à un SQL_ID)
-- TEMP par session (top consumers)
SELECT s.sid, s.serial#, s.username, s.sql_id,
tu.tablespace, tu.segtype, tu.blocks
FROM v$tempseg_usage tu
JOIN v$session s ON s.saddr = tu.session_addr
ORDER BY tu.blocks DESC;
-- ASH sur waits TEMP (fenêtre courte)
SELECT sql_id, COUNT(*) samples
FROM v$active_session_history
WHERE sample_time > SYSDATE - (15/1440)
AND event LIKE '%direct path%temp%'
GROUP BY sql_id
ORDER BY samples DESC FETCH FIRST 15 ROWS ONLY;
Fix (ordre recommandé)
- Plan & stats : corriger cardinalité (stats, histo, rewrite).
- Réduire A-Rows : filtrer tôt, index, partition pruning.
- Limiter DOP : moins de spills concurrents.
- Workareas : seulement si preuve (sinon overcommit).
- Skew : changer clé de distribution, réécrire join, partitions.
Golden rule :
On corrige TEMP en réduisant le volume de données à trier/joiner
avant d’augmenter la mémoire.
9.5 UNDO/Redo : le coût “caché” du parallélisme (PX DML, commits, redo contention)
Pourquoi PX DML fait souffrir UNDO
- Le DML parallèle multiplie les producteurs d’UNDO.
- Peut saturer les segments UNDO, et exposer des “long queries” (cohérence).
- Couplé à TEMP (spills), ça peut devenir explosif.
SELECT begin_time, undoblks, txncount, maxquerylen
FROM v$undostat
ORDER BY begin_time DESC FETCH FIRST 12 ROWS ONLY;
Redo : commit path et LGWR
- Le PQ peut accélérer un DML → mais augmente la génération redo.
- Si l’app commit trop souvent : log file sync explose.
- Si stockage redo lent : log file parallel write explose.
Waits à surveiller
| Event | Interprétation | Mitigation |
|---|---|---|
| log file sync | latence commit | batch commits, réduire commits |
| log file parallel write | LGWR I/O | redo sur storage low-latency |
| log buffer space | redo buffer saturé | bursts DML / DOP trop haut |
Fix (pragmatique)
- Limiter DOP sur DML (et tester).
- Regrouper commits côté app/batch.
- Réduire redo généré (design/index, stratégie DML).
- Isoler et optimiser I/O redo.
9.6 Coexistence workloads : protéger l’OLTP (throttle, fenêtres, caps)
Objectif : éviter la “prise d’otage” des ressources
- PQ consomme des pools : CPU, I/O, TEMP, UNDO, buffer cache.
- Sans garde-fou, un job peut tuer le p99 OLTP.
- La coexistence est un choix d’architecture : fenêtres, caps, priorités.
Politiques typiques
Fenêtre batch
- pas de PQ en heures ouvrées
- jobs regroupés la nuit
- monitoring agressif
Caps & quotas
- DOP max par service
- limiter PX servers actifs
- stop/kill switch
Coexistence = discipline : un “simple hint parallel” peut casser le système si tout le monde le copie.
Playbook coexistence
- Identifier workload OLTP critique (SLA/p99).
- Isoler batch windows / limiter PQ en journée.
- Définir caps DOP + règles de scheduling.
- Déployer progressivement (1 job, puis 2…).
- Valider : p95/p99 stable + DB Time global pas explosé.
Mesures “santé” système
- CPU : run queue / OS load + DB CPU.
- I/O : latence read/write, queueing, waits dominants.
- TEMP : top consumers, croissance, headroom.
- UNDO/redo : v$undostat + waits redo.
9.7 Diagnostiquer PQ : lire un plan PX, métriques, skew, DFO bottlenecks
Plan : afficher PX proprement
SELECT * FROM TABLE(
dbms_xplan.display_cursor(
sql_id => :sql_id,
cursor_child_no => NULL,
format => 'BASIC +OUTLINE +PREDICATE +PARALLEL'
)
);
Selon version, ajoute ALLSTATS LAST pour A-Rows si possible.
Métriques utiles
- A-Rows vs E-Rows sur opérateurs dominants.
- Temps sur redistribution PX (échanges).
- Opérateurs “SORT” / “HASH” (spills).
- Phase dominée par QC (bottleneck final).
Skew : comment le détecter
- PX workers : certains idle, un worker saturé (wall-clock dominé).
- Distribution sur clé non uniforme (hot key).
- Partitioning non aligné avec le join (partition-wise manqué).
Le skew tue le PQ : “N workers” n’aide pas si 1 worker fait 80% du travail.
Actions de remédiation
- Corriger stats/histograms si cardinalité fausse.
- Changer la clé de distribution (ou rewrite join).
- Réduire DOP (moins de pression) et réévaluer gain.
- Éviter les broadcast “pas si petits”.
9.8 Anti-patterns PQ : la liste noire (éviter la catastrophe)
À bannir (par défaut)
- PQ sur requêtes OLTP fréquentes (latence sensible).
- DOP élevé “par habitude” (copié/collé).
- Paralléliser une requête déjà dominée par TEMP spill.
- Broadcast sur table “moyenne” (trafic explosif).
- PQ + DML + commit fréquent (redo/undo meltdown).
- Skew connu non traité (hot key).
Smoke signals
- DOP↑ → durée ↑ (diminishing returns puis inverse returns)
- TEMP usage ↑↑ + direct path temp waits
- CPU run queue ↑ + DB CPU ↑ mais throughput stable
- OLTP p99 ↑ pendant job
Alternatives
- Partition pruning / partition-wise join plutôt que DOP↑.
- Réduction de données : filtres, pré-agrégations, materialized view.
- Réécriture join (hash vs nested loop) selon cas.
- Limiter DOP + fenêtre dédiée.
Policy simple (démarrage)
- PQ interdit en heures ouvrées (sauf exception)
- DOP max = X (par service)
- Chaque hint parallel doit être justifié (ticket + validation AWR)
- Post-mortem obligatoire si incident TEMP/CPU
A · Diagrammes (Mermaid-ready)
Pipeline QC/PX
flowchart TD
QC[Query Coordinator] --> P1[PX Producers: Scan/Filter]
P1 --> X[PX Exchange: redistribute]
X --> P2[PX Consumers: Join/Agg/Sort]
P2 --> QC
Loop “TEMP meltdown”
flowchart TD
DOP[DOP ↑] --> W[Workarea pressure ↑]
W --> S[Spill TEMP ↑]
S --> IO[TEMP I/O ↑]
IO --> L[Latency ↑]
L --> T[Job duration ↑]
T -->|souvent| DOP
Coexistence OLTP/PQ
flowchart LR
OLTP[OLTP SLA] -->|needs| CPU[CPU headroom]
PQ[Batch PQ] -->|consumes| CPU
PQ --> TEMP[TEMP]
PQ --> IO[I/O]
PQ --> UNDO[UNDO/Redo]
CPU --> OLTP
IO --> OLTP
TEMP --> OLTP
B · Runbook incident : PQ sature CPU/TEMP (mitigation immédiate + post-mortem)
Triage (5 minutes)
- Identifier si PQ est en cause : top sessions actives + SQL_ID + “PX”.
- Regarder TEMP : croissance rapide ? top consumers ?
- Regarder CPU : run queue / DB CPU / sessions actives.
- Vérifier l’impact OLTP (p95/p99, timeouts).
But : arrêter l’hémorragie, pas “analyser pendant l’incendie”.
Mitigation immédiate
- Réduire/stopper le job batch (si possible) ou le déplacer en fenêtre.
- Limiter DOP (au prochain run) + retirer hints parallel non maîtrisés.
- Réduire concurrence (1 job à la fois).
- Préserver OLTP : priorité à la stabilité.
Decision rule :
Si OLTP SLA est impacté => PQ doit être “throttlé” immédiatement.
Preuve après stabilisation
- ASH : SQL_ID top + waits (temp/IO/CPU).
- Plan : vérifier redistribution/skew/sort/hash.
- AWR : DB Time, top waits, TEMP usage, workarea stats.
Post-mortem (à formaliser)
- Job / SQL_ID :
- DOP utilisé :
- Fenêtre :
- TEMP peak :
- UNDO/redo impact :
- OLTP impact (p95/p99) :
Root cause :
- spill (workarea) ?
- cardinalité fausse ?
- skew ?
- DOP trop haut ?
Fix :
- rewrite/stats/partitioning/DOP caps/window
Validation :
- run contrôlé + comparaison AWR
C · Commandes & vues : PX / TEMP / UNDO (troubleshooting)
PX live (pistes)
-- Sessions actives (repérer QC/PX)
SELECT sid, serial#, username, sql_id, program, module, status,
event, wait_class, seconds_in_wait
FROM v$session
WHERE status='ACTIVE'
ORDER BY seconds_in_wait DESC;
-- PX sessions (selon version)
-- v$px_session / v$px_process souvent utiles
TEMP top consumers
SELECT s.sid, s.serial#, s.username, s.sql_id,
tu.tablespace, tu.segtype, tu.blocks
FROM v$tempseg_usage tu
JOIN v$session s ON s.saddr = tu.session_addr
ORDER BY tu.blocks DESC;
ASH : top SQL sur waits TEMP
SELECT sql_id, COUNT(*) samples
FROM v$active_session_history
WHERE sample_time > SYSDATE - (15/1440)
AND event LIKE '%temp%'
GROUP BY sql_id
ORDER BY samples DESC FETCH FIRST 20 ROWS ONLY;
UNDO / redo signaux
SELECT begin_time, undoblks, txncount, maxquerylen
FROM v$undostat
ORDER BY begin_time DESC FETCH FIRST 12 ROWS ONLY;
SELECT event, total_waits, time_waited/100 time_s
FROM v$system_event
WHERE wait_class <> 'Idle'
ORDER BY time_waited DESC FETCH FIRST 25 ROWS ONLY;