Tested on: Oracle Database 19c Enterprise Edition 19.27, 16 CPUs | Level: Intermediate to Advanced
Eight sessions insert rows into a table whose primary key comes from a sequence. Nothing is wrong with the SQL, yet the load is twice as slow as it should be. The cause is one hot index block that every session fights over. In this post I reproduce the problem on 19c, measure it, and compare the two standard fixes, a reverse key index and a scalable sequence, including the trade-offs most articles skip. Every number below comes from a real lab run.
Results at a glance
| Metric (8 sessions × 50,000 rows) | Normal B-tree | Reverse key | Scalable sequence |
|---|---|---|---|
| Elapsed time | 9.2 s | 4.0 s | 4.0 s |
| Buffer busy waits on the PK index | 261,900 | 9,232 | 2,358 |
| enq: TX - index contention waits | 9,292 | 150 | 35 |
| Index leaf blocks after load | 1,515 | 1,024 | 2,677 |
| Index range scan on ID possible? | Yes | No | Yes, but IDs are not in time order |
Both fixes made the load 2.3 times faster and removed 96 to 99 percent of the index waits. They are not interchangeable, though. The reverse key index gave up range scans, and the scalable sequence produced the largest index and 34-digit keys.
The problem: a right-hand hot block
A sequence produces ascending numbers, so every new key belongs at the far right edge of the B-tree index. With one session that is efficient. With many sessions inserting at the same time, they all need to change the same rightmost leaf block. Only one session can modify a block at a time, so the others queue:
- buffer busy waits: a session waits because another session is changing the block it needs.
- enq: TX - index contention: when that leaf block is full, one session splits it, and the others wait for the split to finish.
Adding CPUs does not help, because the work is serialised on one block. The fix is to stop all sessions from inserting into the same place.
How to spot it in production
Find the segments with the most buffer busy waits since instance startup. This uses V$SEGMENT_STATISTICS, which needs no extra license:
SELECT owner, object_name, object_type, value AS buffer_busy_waits
FROM v$segment_statistics
WHERE statistic_name = 'buffer busy waits'
ORDER BY value DESC
FETCH FIRST 10 ROWS ONLY;
A primary key index on a sequence column at the top of this list, together with enq: TX - index contention in your top wait events, is the classic signature.
If you are licensed for the Diagnostics Pack, Active Session History shows the object for recent waits:
SELECT o.owner, o.object_name, a.event, COUNT(*) AS samples
FROM v$active_session_history a
JOIN dba_objects o ON o.object_id = a.current_obj#
WHERE a.sample_time > SYSTIMESTAMP - INTERVAL '1' HOUR
AND a.event IN ('buffer busy waits', 'enq: TX - index contention')
GROUP BY o.owner, o.object_name, a.event
ORDER BY samples DESC
FETCH FIRST 10 ROWS ONLY;
V$ACTIVE_SESSION_HISTORY, AWR and ASH reports require the Oracle Diagnostics Pack. Use the V$SEGMENT_STATISTICS query if you are not licensed for it.The lab
Three identical tables, each with a numeric primary key filled from a cached sequence. Only the key design changes:
-- A: normal B-tree index (the problem)
CREATE TABLE t_a (
id NUMBER NOT NULL,
created_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
payload VARCHAR2(100)
);
CREATE UNIQUE INDEX t_a_pk ON t_a (id);
ALTER TABLE t_a ADD CONSTRAINT t_a_pk PRIMARY KEY (id) USING INDEX t_a_pk;
CREATE SEQUENCE seq_a CACHE 1000;
-- B: reverse key index
CREATE UNIQUE INDEX t_b_pk ON t_b (id) REVERSE;
CREATE SEQUENCE seq_b CACHE 1000;
-- C: normal index, scalable sequence (Oracle 18c and later)
CREATE UNIQUE INDEX t_c_pk ON t_c (id);
CREATE SEQUENCE seq_c CACHE 1000 SCALE EXTEND;
Each worker session inserts 50,000 rows and commits every 100 rows:
CREATE OR REPLACE PROCEDURE load_rows (p_label VARCHAR2, p_rows PLS_INTEGER) AS
BEGIN
FOR i IN 1 .. p_rows LOOP
IF p_label = 'A' THEN
INSERT INTO t_a (id, payload) VALUES (seq_a.NEXTVAL, RPAD('x', 100, 'x'));
ELSIF p_label = 'B' THEN
INSERT INTO t_b (id, payload) VALUES (seq_b.NEXTVAL, RPAD('x', 100, 'x'));
ELSE
INSERT INTO t_c (id, payload) VALUES (seq_c.NEXTVAL, RPAD('x', 100, 'x'));
END IF;
IF MOD(i, 100) = 0 THEN
COMMIT;
END IF;
END LOOP;
COMMIT;
END;
/
For each design, a driver procedure takes a snapshot of V$SYSTEM_EVENT and V$SYSSTAT, starts 8 sessions at once with DBMS_SCHEDULER, waits for all of them to finish, and takes a second snapshot. The difference is the cost of that design alone:
FOR j IN 1 .. 8 LOOP
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'HOTJOB' || p_label || j,
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN load_rows(''' || p_label || ''', 50000); END;',
enabled => TRUE,
auto_drop => TRUE
);
END LOOP;
All three runs completed with no failed sessions and loaded exactly 400,000 rows each.
Result 1: the index is the bottleneck
System-wide waits during each run (deltas from V$SYSTEM_EVENT and V$SYSSTAT):
METRIC NORMAL_BTREE REVERSE_KEY SCALABLE_SEQ
------------------------------------------ -------------- -------------- --------------
buffer busy waits - seconds 13.8 1.2 0.9
buffer busy waits - waits 286,528.0 50,073.0 39,980.0
elapsed seconds 9.2 4.0 4.0
enq: TX - index contention - seconds 4.3 0.8 0.0
enq: TX - index contention - waits 9,292.0 150.0 35.0
latch: cache buffers chains - seconds 0.0 0.0 0.0
latch: cache buffers chains - waits 645.0 737.0 396.0
leaf node 90-10 splits 567.0 0.0 204.0
leaf node splits 1,514.0 1,023.0 2,676.0
With the normal index, sessions spent 13.8 seconds in buffer busy waits and 4.3 seconds in index contention, during a load that took only 9.2 seconds of wall-clock time. Most of each session's time was spent waiting, not inserting.
V$SEGMENT_STATISTICS shows exactly which segment suffered:
OBJECT_NAM BUFFER_BUSY_WAITS ITL_WAITS
---------- ----------------- ----------
T_A 33355 0
T_A_PK 261900 0
T_B 51165 0
T_B_PK 9232 35
T_C 47174 0
T_C_PK 2358 10
In the normal design, 89 percent of buffer busy waits were on the index T_A_PK, not the table. The table sits in a tablespace with automatic segment space management (ASSM, the default for new tablespaces), which already spreads concurrent inserts across different blocks. The index cannot do that, because the key value decides where each entry goes.
Result 2: both fixes work, and the bottleneck moves
Index buffer busy waits fell from 261,900 to 9,232 with the reverse key index (96 percent less) and to 2,358 with the scalable sequence (99 percent less). Index contention waits fell from 9,292 to 150 and 35.
Look at the table rows, though: buffer busy waits on the tables went up, from 33,355 to 51,165 and 47,174. With the index fixed, sessions insert faster and now meet each other on table blocks instead. That is the normal pattern in performance work: removing one bottleneck exposes the next. At this scale it is harmless, but on a much busier system the table is where you would look next.
How a scalable sequence spreads the inserts
A scalable sequence adds a 6-digit prefix to each value: 3 digits for the instance and 3 digits for the session. Each session therefore inserts into its own region of the index instead of all sessions sharing the right edge.
ID_TEXT
----------------------------------------
1013720000000000000000000000000551
1013720000000000000000000000000552
1013720000000000000000000000000553
PREFIX ROW_COUNT
-------- ----------
101187 50000
101251 50000
101312 50000
101372 50000
101428 50000
101666 50000
101726 50000
101841 50000
Eight sessions produced exactly eight prefixes with 50,000 rows each. In 101187, 101 is the instance part and 187 comes from the session ID. On RAC, the instance part also stops different instances from fighting over the same block, which is what scalable sequences were designed for.
The trade-offs
Reverse key index: no range scans
A reverse key index stores the bytes of each key in reverse order, so consecutive values land in different leaf blocks. The price is that the index no longer keeps keys in order, so the optimizer cannot use it for a range predicate:
-- Normal index: SELECT * FROM t_a WHERE id BETWEEN 1000 AND 1100
| Id | Operation | Name |
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T_A |
|* 2 | INDEX RANGE SCAN | T_A_PK |
2 - access("ID">=1000 AND "ID"<=1100)
-- Reverse key index: SELECT * FROM t_b WHERE id BETWEEN 1000 AND 1100
| Id | Operation | Name |
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| T_B |
1 - filter("ID"<=1100 AND "ID">=1000)
Equality lookups (WHERE id = :x) still use the index. Range queries on the key, such as batch jobs that process ID ranges or pagination by ID, become full table scans. Also, inserts now land all over the index, so the whole index needs to stay in the buffer cache. On a very large index that does not fit, you trade contention for physical reads.
Scalable sequence: bigger keys, bigger index
INDEX_NAME BLEVEL LEAF_BLOCKS ROWS_PER_LEAF
---------- ---------- ----------- -------------
T_A_PK 2 1515 264
T_B_PK 2 1024 391
T_C_PK 2 2677 149
The scalable sequence index was the largest: 1.8 times the normal index, with only 149 entries per leaf block. The keys are 34 digits long, so each index entry is much wider. Check three things before using it:
- Application data types. A 34-digit number does not fit in a 64-bit integer (Java
long,BIGINT,NUMBER(18)). Every application, ORM, ETL tool and downstream system that reads the key must handle it.SCALE NOEXTENDwith a smallerMAXVALUEkeeps values shorter, but leaves fewer digits for the counter. - No time ordering. A higher ID no longer means a newer row. Never use the key for "latest rows" logic.
- Index size. Expect a larger index and plan storage and buffer cache for it.
A surprise: the normal index was not compact either
The textbook says an ascending key index fills its blocks almost completely, because full right-hand blocks split 90-10. Under concurrency that did not happen: only 567 of 1,514 leaf splits were 90-10 splits. With 8 sessions, sequence values reach the index slightly out of order, so many splits became 50-50 splits, and those half-empty blocks are never filled again. The result was 264 entries per leaf, against 391 for the reverse key index. Under concurrent load, "ascending keys give a compact index" does not hold.
Which fix should you use?
| Situation | Recommendation |
|---|---|
Key is only used for equality lookups (id = :x) and joins | Reverse key index. Smallest index in this test and no application change. |
| You need range scans on the key, or you run RAC | Scalable sequence, if every consumer can handle 34-digit values. |
| Application uses 64-bit integer keys and you cannot change it | Reverse key index, or a hash-partitioned global index (needs the Partitioning option). |
| Low concurrency, only a few inserting sessions | Keep the normal index. Measure first, because the fix has costs. |
CACHE 1000. A sequence with NOCACHE or a small cache adds a second bottleneck, enq: SQ - contention, which none of the index fixes solve. On RAC, also avoid ORDER unless you truly need it.Caveats
- One run on a 16-CPU lab database in NOARCHIVELOG mode with 8 sessions. Your absolute numbers will differ. The direction of the results is what matters.
- More sessions or more CPUs usually make the normal index worse and the gap bigger.
- Changing a production primary key index needs an outage or online rebuild plan, and testing against your real query mix, not only the insert load.
Clean-up
The whole lab lives in one schema. To remove it, as SYSDBA:
DROP USER hot_demo CASCADE;
Summary
A sequence-based primary key under concurrent inserts turns the rightmost index leaf block into a queue. On 19c with 8 sessions, that single block caused 261,900 buffer busy waits and doubled the load time. A reverse key index or a scalable sequence removed 96 to 99 percent of those waits and made the load 2.3 times faster. Choose by how the key is used: reverse key if you never range-scan it, scalable sequence if you do and every consumer can handle 34-digit values. In both cases, measure first, because each fix has a cost.
Tamilselvan Perumal is an Oracle ACE Apprentice and Senior DBA with 12+ years in banking databases, working on database performance and benchmarking. Connect on LinkedIn.
