Forte attente E/S / latence disque

Le système attend les E/S disque. Nous distinguons d'abord si la charge est dominée par les lectures ou les écritures, puis remontons aux causes : buffer cache insuffisant, parcours complets de tables, tempêtes de checkpoints, limites d'IOPS ou débordements vers l'espace temporaire.

Les E/S sont-elles dominées par les lectures ou les écritures ?

Utilisez les statistiques d'E/S fichiers pour déterminer si l'attente porte sur la lecture (données lues sur disque) ou sur l'écriture (écritures redo/checkpoint/données). Cela divise l'arbre en deux.

SELECT function_name, small_read_reqs+large_read_reqs AS reads, small_write_reqs+large_write_reqs AS writes,
       small_read_megabytes+large_read_megabytes+small_write_megabytes+large_write_megabytes AS mb
FROM v$iostat_function ORDER BY mb DESC;

• Surtout des E/S en lecture
• Surtout des E/S en écriture

Pourquoi les E/S en lecture sont-elles élevées ?

Des lectures physiques excessives ont trois causes principales : le buffer cache est trop petit (le working set ne tient pas en RAM), les requêtes effectuent des parcours complets de tables, ou de grandes opérations de tri/hachage débordent vers l'espace temporaire.

SELECT ROUND((1-(pr.value/(dbg.value+cg.value)))*100,2) AS buffer_hit_pct
FROM v$sysstat pr, v$sysstat dbg, v$sysstat cg
WHERE pr.name='physical reads' AND dbg.name='db block gets' AND cg.name='consistent gets';
SELECT sql_id, disk_reads, executions FROM v$sql ORDER BY disk_reads DESC FETCH FIRST 10 ROWS ONLY;

• Le taux de succès du buffer cache est faible, le working set ne tient pas
• Les requêtes effectuent des parcours complets de tables
• De grands tris/hachages débordent vers l'espace temporaire

Pourquoi les E/S en écriture sont-elles élevées ?

Un goulot côté écriture provient soit de checkpoints/flushes périodiques qui saturent brièvement le disque (tempête de checkpoints), soit d'un stockage manquant d'IOPS/de débit pour soutenir la charge.

SELECT event, total_waits, ROUND(time_waited/100,1) AS sec
FROM v$system_event
WHERE event IN ('log file parallel write','db file parallel write','checkpoint completed')
ORDER BY time_waited DESC;

• Vagues soudaines de checkpoint/flush (tempête de checkpoints)
• Le stockage ne suit pas une charge soutenue (limite d'IOPS)

Cause racine : buffer cache insuffisant

Le jeu de données fréquemment consulté (working set) ne tient pas dans le buffer cache, si bien que les blocs sont constamment relus sur disque. Le taux de succès est faible et les lectures physiques élevées.

Cause racine : parcours complets de tables

Les requêtes parcourent de grandes tables de bout en bout faute d'index adapté ; cela provoque de nombreuses lectures physiques et pollue le cache avec des blocs inutiles.

Cause racine : tris/hachages débordant vers l'espace temporaire

Les grandes opérations de tri, hash join ou group by ne tiennent pas en mémoire de travail et écrivent dans l'espace temporaire (temp/tempdb), générant de fortes E/S en lecture/écriture.

Cause racine : tempête de checkpoints

Les pages modifiées (dirty pages) s'accumulent longtemps puis sont écrites en masse sur disque à intervalles rapprochés ; ces vagues d'écriture soudaines saturent périodiquement les E/S.

Cause racine : IOPS / débit insuffisants

Le stockage manque d'IOPS ou de bande passante pour servir les requêtes de lecture+écriture qu'exige la charge ; la profondeur de file et la latence restent durablement élevées.