Oracle – 6) I/O & stockage (ASM, redo, temp, cache)

6.1 Latence I/O : lire les “vrais” signaux

Wait events I/O courants

Event	Lecture	Lecture simple
db file sequential read	read bloc “aléatoire”	souvent index/lookup
db file scattered read	read multi-blocs	souvent full scan
direct path read	bypass cache	parfois ETL/parallel
direct path write	écritures directes	temp/ETL

Latence vs débit (throughput)

Latence : temps par I/O (ms) → tue les workloads OLTP.
Débit : MB/s → important pour scans/ETL.
Un système peut avoir bon débit mais latence pourrie (queueing).

Heuristique DBA :
OLTP souffre d’abord de la latence (ms),
OLAP/ETL souffre d’abord du throughput (MB/s) + TEMP.

Mapper un problème I/O à des objets

Top SQL (AWR/ASH) → SQL_ID
Plan → quels segments / quels objets
Files/tablespaces → diskgroups/LUN
OS/storage → vérifier saturation, queue depth

La vraie question :
“Ce wait vient d’une requête (SQL)”
ou “d’un stockage saturé (infra)” ?

Actions (ordre recommandé)

Réduire I/O (SQL, index, pruning, cache) avant d’acheter du disque.
Isoler files/volumes “hot” (striping, ASM balance).
Vérifier contention infra (multipath, SAN, throttling).
Valider après : latence, reads, DB time.

6.2 Redo / Commit : log file sync, LGWR et chemin disque

Ce qui se passe à COMMIT (ultra résumé)

Session -> COMMIT
  -> LGWR doit flusher le redo jusqu’au commit record
  -> la session attend (log file sync)
  -> quand flush OK : commit validé

Donc “commit lent” = souvent “redo path lent”.

Waits à connaître

Event	Signification	Piste
log file sync	session attend flush	latence commit
log file parallel write	LGWR écrit redo	stockage redo
log buffer space	buffer redo plein	bursts DML

Causes fréquentes

Redo logs sur stockage trop lent / saturé / partagé.
Bursts commits (commit par ligne… ou trop fréquent).
Excessive redo (index inutiles, row chaining, etc.).
Archiving/IO contention selon infra.

Actions (pragmatiques)

Réduire fréquence COMMIT côté app (batch) si possible.
Mettre redo logs sur stockage faible latence (séparé si besoin).
Réduire redo généré (sur-indexation, LOB, etc.).
Valider via AWR : log file sync/parallel write en baisse.

Toujours corréler :
log file sync (sessions) ~ log file parallel write (LGWR).

6.3 TEMP : sorts, hash joins & spills

Pourquoi TEMP explose

SORT (ORDER BY, GROUP BY) sur gros volume
HASH JOIN / HASH AGG avec mémoire insuffisante
Parallel/ETL qui génère du spill

Quand la workarea (PGA) est insuffisante
=> spill to TEMP => I/O TEMP => latence.

Signaux / symptômes

Waits : direct path read/write (temp)
AWR : “Top Timed Events” orientés TEMP
SQL plans : gros sorts, hash join sur gros A-Rows

Actions

Réécrire SQL : réduire A-Rows avant sort/join.
Indexer pour éviter sort (ORDER BY) si cohérent.
Améliorer cardinalité (stats/histo) pour éviter mauvais join algo.
Ajuster PGA/workareas (attention global).

Prévenir

Limiter parallel sauvage
Workload management (Resource Manager)
Surveiller TEMP usage et top SQL consommateurs

6.4 Cache : buffer cache, logical vs physical reads (sans dogme)

Logical vs Physical reads

Logical read = lecture en mémoire (buffer cache) → coûte CPU, mais rapide.
Physical read = lecture disque → coûte latence I/O.
Objectif : réduire les lectures inutiles, pas “maximiser un ratio”.

Le cache hit ratio n’est pas un KPI “magique”.
Le KPI utile = DB Time + top SQL + reads.

Métriques à suivre

Metric	Pourquoi	Interprétation
buffer_gets / exec	coût logique	efficacité SQL
physical_reads / exec	coût disque	pression I/O
DB time	vrai coût	priorités

Hot blocks : quand le cache devient un problème

Insert sur index monotone (right-growing) → contention possible.
Segments “chauds” + concurrence → waits côté buffer/cache.

Actions “safe”

Optimiser top SQL (moins de buffer_gets).
Partitionner / répartir “hot spots”.
Éviter sur-indexation.
Ajuster mémoire uniquement après preuve (AWR/ASH).

6.5 ASM : diskgroups, striping/mirroring, rebalance

ASM en 4 idées

ASM gère allocation + striping sur disques.
Redondance via mirroring selon niveau.
Rééquilibrage (rebalance) lors changements disques.
Performance dépend de distribution et saturation disques.

Diskgroups : design (pragmatique)

Séparer si besoin : DATA / RECO / REDO (selon infra & criticité).
Éviter “tout dans 1 DG” si workloads conflictuels.
Surveiller usage + répartition I/O.

Rebalance : attention impact perf

Rebalance = I/O consommée pour redistribuer.
À planifier (fenêtre / puissance / monitoring).

Pièges

Rebalance en pleine prod sans limite → latence explose.
Diskgroup proche full → allocations plus difficiles.
Déséquilibre disques → “hot disks”.

6.6 Runbook I/O : storage vs SQL (template)

Preuve (minimum)

[Ticket] I/O & stockage

- Symptôme : lenteur / timeouts / commit lent
- Fenêtre :
- Top waits : (AWR/ASH)
- Top SQL_ID :
- Type I/O : read / write / redo / temp
- Hypothèse : SQL vs Infra

Isoler la cause

Si I/O dominants : identifier files/tablespaces/segments.
Vérifier si un seul SQL “fait tout”.
Comparer latence I/O sur plusieurs fichiers (hot spot ?).
Corréler avec OS (saturation/queue) si possible.

Actions (ordre)

Réduire I/O via SQL/index/pruning.
Réduire redo/commit (batch commits, index inutiles).
Réduire TEMP spill (rewrite, PGA, stats).
Optimiser stockage/ASM en dernier (si preuve infra).

Rollback

- revert SQL rewrite
- drop index (si ajout)
- revert paramètres mémoire (si modifiés)
- stopper/reduire rebalance ASM
- revenir configuration stockage (si changement)

A · Diagrammes (Mermaid-ready)

Chaîne I/O → waits → symptôme

flowchart TD
  A[Workload SQL] --> B[Logical reads (cache)]
  B -->|miss| C[Physical reads (disk)]
  C --> D[Waits I/O]
  D --> E[DB Time ↑]
  E --> F[Latence app / timeouts]

Décision : redo vs temp vs read

flowchart TD
  S[DB lente] --> W{Top waits ?}
  W -- log file sync --> R[Redo/commit path]
  W -- direct path read/write --> T[TEMP spill / sorts]
  W -- db file sequential/scattered read --> I[Reads datafiles / index / scans]
  R --> A1[Réduire commits / accélérer redo storage]
  T --> A2[Réduire A-Rows / PGA / rewrite]
  I --> A3[Index/pruning/cache/infrastructure]

B · Commandes & vues (I/O, redo, temp, ASM)

Top waits système

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

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

I/O par datafile (approche simple)

SELECT df.file#, df.name,
       fs.phyrds, fs.phywrts,
       fs.readtim, fs.writetim,
       fs.singleblkrds
FROM v$datafile df
JOIN v$filestat fs ON fs.file# = df.file#
ORDER BY (fs.readtim + fs.writetim) DESC;

Selon version, vues I/O peuvent varier (v$io_*). Adapter au contexte.

TEMP usage (rapide)

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

-- Tablespaces tempfiles
SELECT tablespace_name, file_name, bytes/1024/1024 mb
FROM dba_temp_files
ORDER BY bytes DESC;

ASM (si DB sur ASM)

-- Diskgroups
SELECT name, state, type, total_mb, free_mb
FROM v$asm_diskgroup;

-- Disks
SELECT group_number, name, path, state, total_mb, free_mb
FROM v$asm_disk
ORDER BY group_number, name;

Les vues ASM dépendent du contexte (ASM instance, droits, etc.).

💠 6) I/O & stockage (ASM, latence, redo, temp, cache)

Latence I/O : lire les “vrais” signaux

Redo / Commit : log file sync & friends

TEMP : sorts, hash joins & spills

Cache : buffer cache & smart usage

ASM & diskgroups : architecture & pièges

Runbook I/O : diagnostiquer “storage vs SQL”

Diagrammes (Mermaid-ready)

Commandes & vues (I/O)