DBAtlas · Database operations atlas

Slow writes (INSERT/UPDATE latency)

INSERT/UPDATE/DELETE operations are slower than expected. First we determine what the write is waiting on (commit/redo, locks, disk or per-row overhead), then drill down to the root cause step by step.

What is the write waiting on?

Classify the bottleneck by looking at wait events: is it dominated by commit/redo (WAL) synchronization, locks, disk write latency, or per-row index/trigger overhead?

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;
  • Waiting on commit / redo (WAL sync)
  • Waiting on a lock
  • Suspected per-row overhead (indexes/triggers)
  • Disk write latency is high

Commit pattern or fsync?

A separate commit per row (autocommit) triggers redo/log synchronization as many times as there are rows. Conversely, if the commit count is reasonable but each commit waits on fsync, the problem is log disk latency.

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 / a commit on every row
  • Commit waits on log synchronization (fsync)

Who is holding the lock?

If the write is waiting on a lock: another session may be holding the same row/index, or many sessions may be trying to write to the same 'hot' row.

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;
  • Another session is holding the lock
  • Many sessions write to the same hot row

Where does the per-row overhead come from?

Every INSERT/UPDATE also updates all indexes on the table and fires any triggers/constraints. An excessive number of indexes or a heavy trigger slows writes significantly.

SELECT index_name, uniqueness FROM user_indexes WHERE table_name='T';
SELECT trigger_name, triggering_event FROM user_triggers WHERE table_name='T';
  • The table has too many / unnecessary indexes
  • There is a heavy trigger or constraint cost

Root cause: autocommit / commit per row

A separate commit for each row repeats redo/log synchronization as many times as there are rows. By grouping operations you can dramatically reduce the number of commits (and therefore fsyncs).

Root cause: slow redo/log disk (fsync latency)

Every commit waits until the redo/WAL record is written to durable storage (fsync). If the log disk is slow (high write latency, insufficient IOPS), every commit pays that latency.

Root cause: another session is holding the lock

The write is waiting for a row/index lock held by another session. The blocker is usually a long-running transaction or one whose commit was forgotten.

Root cause: hot row contention

Many sessions try to write to the same row (e.g. a counter, sequence number, balance); they all queue for the same lock and writes are serialized.

Root cause: excessive index overhead

Every index on the table is updated on every write. Many or unused indexes slow INSERT/UPDATE directly.

Root cause: heavy trigger / constraint

A trigger that fires per write or a constraint being validated (especially a foreign key check without an index) adds extra cost to every row.

Open in DBAtlas →