What are
solutions to handling high concurrent inserts in Oracle?
How does Oracle,
internally, manage index block maintenance in this way?
What is best Approach
to Overcoming this issue?
Index Blocks Maintenance:
An update simply inserts a row in the
leaf block, an update deletes the old key value row reference and inserts a new
row with the new key value, and a delete operation just deletes the key value
row reference.
During an insert operation, remember,
indexes are maintained in sequence.
IF there is not room in the leaf block
for the row that is being added (either from an INSERT or an UPDATE operation),
then the leaf block is split into two leaf blocks,
the prior and next DBA leaf block addresses
are updated, and half of the rows are put in one leaf block and the other half
in the new leaf block.
Upside here is this does not take that
long to do, but it is extra transaction and archive work for the Oracle
RDBMS.
Downside is that these splits are
generally not good for performance because it is another physical I/O process
to read both half-empty leaf blocks.
Recommend that the DBA staff
reorganize indexes on tables with a lot of DML. This will realign the
index leaf blocks with the assigned PCTFREE.
Rebuilding of Indexes have some consideration
and doesn’t need to rebuild all indexes in a same fashion.
You can visit https://www.linkedin.com/feed/update/urn:li:activity:7254434622145499137 to get
more info about when we have to rebuild indexes in Oracle.
Oracle Index
Splitting Mechanism:
🔹 What is Index Pre-Splitting in
Oracle?
In Oracle, index
pre-splitting refers to designing your indexes (or partitioning them) in
such a way that future inserts won’t all target the same leaf block.
Oracle’s B-Tree index naturally grows from left to right, so if you
always insert sequential values (like timestamps, sequence numbers,
identity columns),
then all
inserts pile into the rightmost leaf block and finally you have a right-hand
index that can lead to a performance bottleneck.
That leaf block becomes a hot block → causing buffer busy waits
and performance bottlenecks.
Oracle does
not let you manually "pre-split" B-Tree leaf blocks (like in MongoDB
or HBase), but you can achieve the same effect with index design strategies.
Oracle Indexing:
- B-tree Index Structure:
B-tree
indexes are balanced tree structures used by Oracle to efficiently locate
data. New entries are typically added to the "right-hand side"
of the index, meaning the leaf blocks containing the highest key values.
- Contention with Sequential Keys:
When an
application frequently inserts data with sequentially increasing primary keys
(e.g., an ORDER_ID generated by a sequence), all new index entries
tend to be added to the same few leaf blocks on the right-hand side of the
index.
- "Hot Blocks" and
Contention:
This
concentration of activity on a limited set of blocks creates "hot
blocks" in the buffer cache. In a multi-user or Oracle Real
Application Clusters (RAC) environment, multiple sessions or instances may
attempt to access and modify these same "hot blocks"
concurrently. This leads to contention, resulting in wait events like
"buffer busy waits" or "gc buffer busy acquire" in RAC,
which can severely impact performance and scalability.
Solutions
to Right-Hand Index Problems:
Oracle
offers several strategies to mitigate "right-hand index problems":
- Reverse Key Indexes:
This index
type reverses the byte order of the key, distributing inserts across all leaf
blocks of the index, not just the right-hand side. This reduces contention
but can hinder range scans.
- Hash Partitioning:
For tables
with sequentially increasing keys, hash partitioning can distribute data and
index entries across multiple partitions, each with its own "right-hand
side," thus reducing contention on a single index.
- Scalable Keys:
Designing a
primary key that does not strictly increase sequentially (e.g., incorporating a
non-sequential component like an instance ID in RAC) can also help
distribute inserts more evenly or using scalable sequences.
- Index Compression:
While not
directly addressing right-hand index issues, index compression can reduce the
physical size of the index, potentially leading to fewer block splits and
better buffer cache utilization.
We looking
to a solution to minimize contention on high load inserts on a table and we want
to knowing how Oracle handle this issue:
One of solution
called Pre-Splitting that handle in the Internal of Oracle:
🔹 Pre-Splitting Solutions in Oracle
Here are the
practical techniques:
1.
Reverse Key Index
o Reverses the
bytes of the indexed column.
o Sequential
values (1, 2, 3…) become non-sequential keys internally.
o Inserts are
spread randomly across many leaf blocks instead of piling up at the right edge.
Best when: Index is used mostly for equality
searches, not range scans.
2.
Hash Partitioned Index
o Instead of one
big index, Oracle splits the index into multiple partitions based on a
hash of the key.
o Inserts are
evenly distributed across partitions (so across many leaf blocks).
o Prevents
rightmost hot block contention.
Best when: You want balanced inserts and
can afford managing partitions.
3.
Range-Partitioned Table with Local Indexes
o Partition the
table (for example by date or ID range).
o Create local
indexes tied to each partition.
o New inserts go
into the correct partition → each index partition grows separately.
Best when: You have time-series data or
predictable ranges.
4.
Reverse + Partitioning Combination
o For very high
concurrency OLTP, you may combine reverse key index or hash
partitioning with partitioned tables for maximum distribution.
Benefits of Pre-Splitting in Oracle
1.
Removes Hot Block Contention
o No more
“rightmost leaf block” being hammered by inserts.
2.
Improves Insert Performance
o Spreads load
across multiple index partitions/blocks.
3.
Higher Concurrency
o Multiple
sessions can insert/update without blocking each other.
4.
Better Scalability
o Works well in
OLTP systems with thousands of concurrent users.
5.
Predictable Performance
o Avoids sudden
contention spikes when index grows.
In summary:
In Oracle, index pre-splitting isn’t a direct command, but is achieved
by reverse key indexes, hash partitioned indexes, or local partitioned
indexes.
The main benefit is eliminating hot index blocks, giving faster
inserts and scalable OLTP performance.
Here’s a comparison
table of Oracle’s main index pre-splitting solutions:
📊 Oracle Index Pre-Splitting Solutions
|
Solution |
How it Works |
Best For |
Advantages |
Limitations |
|
Reverse Key Index |
Reverses the byte order of the index key so
sequential values (1,2,3…) spread across many leaf blocks |
High-concurrency OLTP with monotonically increasing
keys (sequence, identity, date) |
- Eliminates rightmost hot block |
- Range scans become impossible |
|
Hash Partitioned Index |
Index is divided into partitions by hashing the key
value |
Workloads needing both scalability and some
range/equality search support |
- Balances inserts across partitions |
- Slightly higher management overhead |
|
Local Partitioned Index |
Indexes are created separately for each table
partition (e.g. per day/month) |
Time-series data, large tables naturally
partitioned (by date, ID ranges) |
- Inserts spread by table partition |
- Requires table partitioning |
|
Combination (e.g. Reverse +
Partitioning) |
Apply reverse or hash index on partitioned tables |
Very high concurrency + partitioned model (like
telecom, finance OLTP) |
- Maximum distribution |
- More complexity to design & maintain |
Quick
Rules of Thumb
- Use Reverse Key Index
when you only care about fast inserts and don’t need range scans.
- Use Hash Partitioned Index
when you want both scalability and query flexibility.
- Use Local Partitioned Index when you already have a partitioned table (like time-series or data warehouse).
Pre-splitting
an index in Oracle
means
deliberately creating multiple branch and leaf blocks in an index ahead of time
so that concurrent inserts or updates won’t all contend for the same block.
It’s mostly
relevant when you know an index will be under high concurrent insert load, and
that load would otherwise funnel into the same right-most leaf block (the hot
block problem).
More
about Pre-Splitting index blocks:
Oracle's
index implementation uses a technique called "pre-splitting" to
optimize index performance during insertions, especially when dealing with
monotonically increasing data or high concurrency. This involves proactively
creating new index blocks before they are actually needed, reducing the
overhead associated with splitting existing blocks during inserts and
minimizing contention.
1. Leaf
Node Splits: When a leaf block in the index is full and a new row is
inserted, Oracle typically splits the block's contents into two, updating
pointers in the index structure.
2. 90-10
Splits: A special case of leaf node split, called a "90-10
split", occurs when inserting a new key that is greater than the current
highest key in the rightmost block of the index. Instead of splitting the
existing block, Oracle creates a new block and inserts the new key there,
leaving the existing block relatively untouched.
3. Pre-splitting: When Oracle anticipates a high rate of insertions, it
can pre-split index blocks. This means creating new, empty leaf blocks before
they are needed, anticipating the need for more space. This reduces the
overhead of splitting existing blocks during inserts and minimizes contention,
especially in high concurrency scenarios.
Benefits
of Pre-splitting:
- Reduced Contention: By pre-splitting, Oracle
reduces the need for leaf block splits during inserts, minimizing
contention for index blocks, especially in RAC (Real Application Clusters)
environments.
- Improved Performance: Pre-splitting can lead to
faster insert operations, as the database doesn't need to perform the
split operation on-the-fly during each insert.
- Optimized for Monotonically
Increasing Data:
It is particularly beneficial when dealing with data that is inserted in a
sequential order (e.g., using sequences), as the 90-10 split strategy can
be used to efficiently append new data.
When to
Consider Pre-splitting:
- High Insert Rates: If you are experiencing high
insert rates into your indexes, especially those with monotonically
increasing data, pre-splitting can be beneficial.
- RAC Environments: In RAC, index contention can
be a major bottleneck. Pre-splitting can help mitigate this
by reducing the need for block splits during insert operations.
- Monitoring: Monitor your index performance
(e.g., using AWR reports or other monitoring tools) to identify areas
where pre-splitting could be beneficial.
There’s
no direct “PRE-SPLIT” command in Oracle, but you can cause splits
intentionally.
Dummy
Inserts as a basic Idea (is not a normal solution)
1. Create
the table and index normally.
2. Insert
“dummy” rows with key values spaced apart enough to force the index to split
into multiple leaf blocks.
3. Delete
the dummy rows — the leaf blocks remain in the index structure.
Example:
-- Create the table
CREATE TABLE orders (
order_id NUMBER PRIMARY KEY,
data VARCHAR2(100)
);
-- Populate to pre-split
BEGIN
FOR i IN 1 .. 100000 LOOP
INSERT INTO orders VALUES (i * 1000, 'dummy');
END LOOP;
COMMIT;
END;
/
-- Delete the dummy data
DELETE FROM orders WHERE data = 'dummy';
COMMIT;
Now the
index has many pre-created leaf blocks ready for new inserts.
Recently I tested an old idea: “What if we insert dummy rows into a table, then delete them, to pre-split index blocks and leave space for future inserts?”
At first
glance, this sounds like it might reduce hot block contention in Oracle
indexes.
So, I set up
the experiment:
Create a
table with a sequential primary key.
Run multiple
sessions inserting rows → immediately saw buffer busy waits on the index
(classic rightmost leaf block problem).
Insert 500K
“dummy” rows, then delete them (leaving free space inside the index).
Start
inserting again with new IDs…
Guess what?
The hot block issue did not disappear.
Why? Because
Oracle’s B-Tree index grows rightward, and sequential inserts always hit the
rightmost leaf block — not the earlier freed ones.
The proof
was clear in AWR/ASH:
•buffer busy
waits
•enq: TX -
index contention
Still
present, even after dummy pre-splitting.
The Real
Solutions in Oracle
If you want
to eliminate hot block contention, you need proper index design:
• Reverse
Key Index → spreads Inserts randomly, perfect for equality lookups.
• Hash
Partitioned Index → distributes inserts across partitions.
• Local
Partitioned Indexes → great for time-series and large partitioned tables.
• Adjust PCTFREE
of Index or Table and review AWR report.
If you would
like to learn more about the pre-split mechanism:
https://www.linkedin.com/feed/update/urn:li:activity:7166601444387635200
https://richardfoote.wordpress.com/category/index-block-splits/
No comments:
Post a Comment