Écritures lentes (latence INSERT/UPDATE)

Les opérations INSERT/UPDATE/DELETE sont plus lentes que prévu. Nous déterminons d'abord ce que l'écriture attend (commit/redo, verrous, disque ou surcoût par ligne), puis remontons pas à pas jusqu'à la cause racine.

Qu'attend l'écriture ?

Classez le goulot en examinant les événements d'attente : est-il dominé par la synchronisation commit/redo (WAL), les verrous, la latence d'écriture disque ou le surcoût par ligne des index/triggers ?

SELECT event, total_waits, ROUND(time_waited/100,1) AS sec_waited
FROM v$system_event
WHERE event IN ('log file sync','log file parallel write','enq: TX - row lock contention','db file sequential read')
ORDER BY time_waited DESC;

• Attente sur commit / redo (sync WAL)
• Attente sur un verrou
• Surcoût par ligne suspecté (index/triggers)
• Latence d'écriture disque élevée

Schéma de commit ou fsync ?

Un commit distinct par ligne (autocommit) déclenche la synchronisation redo/journal autant de fois qu'il y a de lignes. À l'inverse, si le nombre de commits est raisonnable mais que chaque commit attend le fsync, le problème est la latence du disque des journaux.

SELECT name, value FROM v$sysstat WHERE name IN ('user commits','redo writes','redo size');
SELECT ROUND(time_waited_micro/GREATEST(total_waits,1)/1000,2) AS ms_per_sync FROM v$system_event WHERE event='log file sync';

• Autocommit / un commit à chaque ligne
• Le commit attend la synchronisation du journal (fsync)

Qui détient le verrou ?

Si l'écriture attend un verrou : une autre session détient peut-être la même ligne/le même index, ou de nombreuses sessions tentent d'écrire sur la même ligne 'chaude'.

SELECT s.sid, s.serial#, s.username, s.blocking_session, s.event, s.seconds_in_wait
FROM v$session s WHERE s.blocking_session IS NOT NULL;

• Une autre session détient le verrou
• De nombreuses sessions écrivent sur la même ligne chaude

D'où vient le surcoût par ligne ?

Chaque INSERT/UPDATE met aussi à jour tous les index de la table et déclenche les triggers/contraintes éventuels. Un nombre excessif d'index ou un trigger lourd ralentit fortement les écritures.

SELECT index_name, uniqueness FROM user_indexes WHERE table_name='T';
SELECT trigger_name, triggering_event FROM user_triggers WHERE table_name='T';

• La table a trop d'index / des index inutiles
• Il y a un coût de trigger ou de contrainte lourd

Cause racine : autocommit / commit par ligne

Un commit distinct pour chaque ligne répète la synchronisation redo/journal autant de fois qu'il y a de lignes. En regroupant les opérations, vous pouvez réduire considérablement le nombre de commits (et donc de fsync).

Cause racine : disque redo/journal lent (latence fsync)

Chaque commit attend que l'enregistrement redo/WAL soit écrit sur un stockage durable (fsync). Si le disque des journaux est lent (forte latence d'écriture, IOPS insuffisantes), chaque commit paie cette latence.

Cause racine : une autre session détient le verrou

L'écriture attend un verrou de ligne/d'index détenu par une autre session. Le bloqueur est généralement une transaction longue ou dont le commit a été oublié.

Cause racine : contention sur une ligne chaude

De nombreuses sessions tentent d'écrire sur la même ligne (par ex. un compteur, un numéro de séquence, un solde) ; elles font toutes la queue pour le même verrou et les écritures sont sérialisées.

Cause racine : surcoût excessif des index

Chaque index de la table est mis à jour à chaque écriture. Des index nombreux ou inutilisés ralentissent directement les INSERT/UPDATE.

Cause racine : trigger / contrainte lourd(e)

Un trigger déclenché à chaque écriture ou une contrainte en cours de validation (surtout un contrôle de clé étrangère sans index) ajoute un coût supplémentaire à chaque ligne.