Oracle – Cas d’Usage (Use Cases)

Comment utiliser cette page “Use Cases”

Workflow recommandé :
1) Choisir le use case qui ressemble le plus à tes symptômes
2) Exécuter les scripts “check rapide” (copier-coller)
3) Identifier : top wait + SQL_ID + objet(s) + sessions
4) Appliquer 1 action ciblée (pas 10 knobs) + rollback
5) Valider sur fenêtre comparable + documenter

Preuve > intuition : AWR/ASH + DBMS_XPLAN + métriques SLA app/OS. Sans baseline “avant/après”, tu ne sais pas si tu as aidé.

Safe-first : on évite les changements globaux (optimizer params, “stats sauvages”, mémoire énorme) tant qu’on n’a pas prouvé la cause dominante.

UC-01 · Blocages : sessions bloquantes / enq: TX

Timeouts, p99 qui explose, sessions “en attente”
AWR : enq: TX - row lock contention dominant
Spikes pendant batch / pics de trafic

Un lock, c’est rarement “un problème Oracle” : c’est souvent un pattern applicatif (hot row, longs TX, ordre d’updates).

Objectif	Ce qu’on cherche	Signal
Arbre de blocage	qui bloque qui	blocking_session
Objet	table/row concernée	row_wait_obj#
SQL	SQL_ID du bloqueur	sql_id / module

-- 1) Qui bloque qui
SELECT s.sid, s.serial#, s.username, s.sql_id, s.module, s.action,
       s.blocking_session, s.event, s.seconds_in_wait
FROM v$session s
WHERE s.blocking_session IS NOT NULL
ORDER BY s.seconds_in_wait DESC;

-- 2) Bloqueur (détails)
SELECT sid, serial#, username, sql_id, module, action, status
FROM v$session
WHERE sid IN (SELECT DISTINCT blocking_session FROM v$session WHERE blocking_session IS NOT NULL);

-- 3) Objet attendu (approx)
SELECT sid, row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
FROM v$session
WHERE blocking_session IS NOT NULL;

Actions safe (immédiat) :

Identifier le SQL_ID du bloqueur + module
Réduire durée TX (commit plus tôt / batch)
Limiter concurrence sur la même ligne (hot row)
Indexer FKs si besoin (selon cas)

À éviter :

toucher des “knobs” globaux pour un lock
ajouter des index au hasard
tuer des sessions sans comprendre (risque rollback long)

UC-02 · Contraintes & verrous “surprises” (FK, TM, DDL)

Attentes enq: TM, blocages lors de DML/DDL
Deletes/updates qui bloquent “sans raison”
Maintenance (DDL) qui gèle l’app

Cause typique	Pourquoi ça bloque	Indice
FK sans index	verrouillage & scans sur table enfant	DML parent lent
DDL concurrent	verrous dictionnaire / table locks	maintenance
Constraints “deferrable” mal gérées	locks qui durent jusqu’au commit	TX longues

-- FKs sans index (à adapter: requêtes DBA_*)
-- (Lister contraintes FK)
SELECT owner, constraint_name, table_name, r_owner, r_constraint_name
FROM dba_constraints
WHERE constraint_type='R'
ORDER BY owner, table_name;

-- Locks (table / TM)
SELECT s.sid, s.serial#, s.username, l.type, l.lmode, l.request, l.id1, l.id2
FROM v$lock l JOIN v$session s ON s.sid=l.sid
WHERE l.type IN ('TM','TX')
ORDER BY s.sid;

-- DDL / verrous: identifier sessions et objets (selon droits)

Actions

Vérifier FKs sans index (cas classique)
Déplacer DDL en fenêtre et privilégier opérations online
Réduire durée TX lors de maintenance

UC-03 · Régression : plan qui change (stats/patch) → p99 explose

Dégradation brutale après recollect stats / patch / deploy
Top SQL_ID devient dominant dans AWR
Plan actuel différent du plan “historique”

Règle : stabiliser d’abord (baseline/plan), puis optimiser proprement.

Check	But	Sortie
DBMS_XPLAN ALLSTATS	voir A-Rows vs E-Rows	cardinality issues
Stats stale	vérifier dérives	recollect ciblé
Bind peeking / ACS	plan dépend des valeurs	plan instability

-- Plan actuel
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(':sql_id', NULL, 'ALLSTATS LAST'));

-- (Optionnel) infos stats table/index (selon droits)
-- SELECT owner, table_name, last_analyzed, num_rows FROM dba_tables WHERE table_name='...';

Actions safe :

Baselines sur SQL critique (stabiliser)
Stats ciblées + export avant
Réduire dépendance aux hints globaux

À éviter :

Optimizer params global “à l’aveugle”
Recollect full schema en prod

UC-04 · TEMP/UNDO saturés : spills, longs TX, snapshot too old

AWR : direct path write/read temp
TEMP proche 90-100%, batch qui “fond”
UNDO sous pression, queries longues, erreurs snapshot too old

-- 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;

-- UNDO trends
SELECT begin_time, undoblks, txncount, maxquerylen
FROM v$undostat
ORDER BY begin_time DESC FETCH FIRST 30 ROWS ONLY;

Actions (priorité) :

Vérifier plan (hash/sort) + cardinalité (E vs A)
Limiter PQ / DOP en coexistence
Corriger stats ciblées (export avant)
Réduire durée des transactions (UNDO)

UC-05 · Commit lents / redo en souffrance (log file sync / parallel write)

log file sync dominant : commits lents (souvent app)
log file parallel write : latence write redo (souvent storage)
Timeouts en pic, throughput qui s’effondre

Check	But	Indice
commits/s	commits trop fréquents ?	user commits
redo size/s	volume redo	redo size
latence storage	redo device	parallel write

SELECT name, value
FROM v$sysstat
WHERE name IN ('redo size','user commits','user rollbacks');

SELECT event, time_waited/100 time_s
FROM v$system_event
WHERE event IN ('log file sync','log file parallel write');

Actions

Réduire commits (batch) si possible (app)
Vérifier placement redo (ASM/disks rapides)
Redimensionner redo logs (taille/nb)

À éviter

tuner SQL sans traiter commit/redo
mettre DOP pour compenser (aggrave)

UC-06 · Hard parse / no binds : mutex, library cache, churn

AWR/ASH : mutex/library cache, hard parse élevé
CPU consommé dans parsing
Beaucoup de SQL similaires, mais pas identiques

Cause #1 : pas de binds (SQL dynamique).
Cause #2 : churn (cursors invalidés, objects recompilés).
Cause #3 : pool sizing insuffisant (cas plus rare, à prouver).

-- Active sessions + SQL_ID
SELECT sid, serial#, username, sql_id, module, action, event, seconds_in_wait
FROM v$session
WHERE status='ACTIVE'
ORDER BY seconds_in_wait DESC;

-- (optionnel) Top waits parse/library
SELECT event, time_waited/100 time_s
FROM v$system_event
WHERE event LIKE '%mutex%' OR event LIKE '%library%'
ORDER BY time_waited DESC;

Actions

Introduire bind variables (prepared statements)
Réduire SQL dynamiques non paramétrées
Limiter invalidations d’objets (deploys fréquents, DDL)

UC-07 · I/O : full scans, segments chauds, latence disque

AWR : db file sequential/scattered read
Latence storage élevée, IOPS saturés
Segments “hot” (tables/index)

Check	But	Résultat
Top SQL	qui lit ?	SQL_ID
Plan	full scan vs index	opérations
Segments	objets chauds	reads by segment

-- Waits I/O
SELECT event, time_waited/100 time_s
FROM v$system_event
WHERE event IN ('db file sequential read','db file scattered read')
ORDER BY time_waited DESC;

-- Plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(':sql_id', NULL, 'ALLSTATS LAST'));

Actions

Index / rewrite (réduire lignes lues)
Partition pruning / filtrage
Corriger stats ciblées si cardinalité fausse
Vérifier latence ASM/storage

UC-08 · PQ/DOP qui casse l’OLTP : coexistence workloads

Quand batch démarre : p99 OLTP explose
CPU/TEMP/I/O saturent, files d’attente
PQ (DOP) trop haut, pas de caps

Anti-pattern : “mettre plus de PQ pour que le batch finisse” → tu tues l’OLTP.

Check	But	Indice
DOP réel	combien de workers	PQ usage
TEMP	spills hash/sort	temp waits
CPU	saturation	run queue

Actions

Caps DOP par service / workload (throttle)
Fenêtre batch dédiée si possible
Réduire cardinalité et I/O (plan, pruning)
Stop rules (TEMP > 90%, p99 +30%)

-- TEMP 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;

-- Top waits
SELECT event, time_waited/100 time_s
FROM v$system_event
WHERE wait_class <> 'Idle'
ORDER BY time_waited DESC FETCH FIRST 25 ROWS ONLY;

Oracle Cas d’Usage

🎯 Choisis un scénario

Blocages : sessions bloquantes

Contraintes & verrous “surprises”

Régression : plan qui change

TEMP/UNDO saturés

Commit lents / redo en souffrance

Hard parse / no binds

I/O : full scans, latence disque

PQ/DOP qui casse l’OLTP