1. What is a “Hot Block” in Oracle Database ?
How to detect and resolve it in Oracle Database?
When your Oracle database slows down, the culprit isn’t always CPU, memory, or storage — sometimes it’s a single data block causing traffic jams for every session.
These hot blocks can cripple throughput, inflate wait events like buffer busy waits, and frustrate both DBAs and end users.
In this post, I’ll walk through how to detect them, understand the root cause, and apply proven strategies to resolve them — from schema design tweaks to indexing techniques — so your system stays fast and scalable.
A hot block is a block that gets hit too often by concurrent sessions, often due to:
•Multiple sessions trying to update rows stored in the same block.
•Multiple sessions trying to read the same block when it’s being frequently modified.
•Index root or branch block contention (common in monotonically increasing index keys, e.g., on an ID column that always grows).
Hot block issues are a latch contention problem — usually visible as “buffer busy waits” or “gc buffer busy” in V$SESSION_WAIT or ASH views.
2. What Problems Does It Cause?
•Performance bottleneck: Sessions queue up waiting to access the block.
•High wait events: buffer busy waits, gc buffer busy acquire (RAC), read by other session.
•Throughput drop: Even if CPU and IO are free, the block contention slows down the application.
In short, you get queueing at the door — the door being that one hot block.
3. Why It Happens
Typical scenarios:
•Index Hot Blocks:
•Inserting sequential keys into a B-tree index → root and right-most leaf blocks are hot.
•Example: CREATE INDEX idx1 ON table (created_date) with all inserts using SYSDATE in order.
•Table Hot Blocks:
•Many inserts into a table with few blocks (small table, or using INSERT /*+ APPEND */ repeatedly).
•Many updates hitting the same small set of rows.
•Sequence hot spot:
•A table with primary key from a sequence without caching or with a single cache block.
4. Does Oracle Automatically Handle It?
•Partially, yes, but not always enough:
•In RAC, Oracle uses block-level concurrency control and cache fusion to coordinate access.
•Automatic segment space management (ASSM) and freelists help reduce some block contention for inserts.
•Index root block splitting is handled automatically when needed.
•But Oracle cannot magically avoid a workload pattern that hammers the same block — you must fix it at the schema, index, or application level.
5. Solutions to Handle Hot Blocks
It depends on whether the hot block is in a table or index:
A. If it’s a Table Hot Block
•Increase concurrency by spreading rows across more blocks:
•Introduce reverse key indexes for monotonically increasing keys.
•Use hash partitioning or range partitioning to spread data.
•Change PCTFREE to leave more space per block.
•For sequences, use CACHE and NOORDER to spread inserts across different sequence numbers.
•Application changes:
•Avoid all inserts targeting the same small set of rows.
•If updating counters, consider using materialized views or in-memory tables.
B. If it’s an Index Hot Block
•Reverse key index for sequential keys (avoids right-most leaf hot spot).
•Hash partitioned index (Enterprise Edition only).
•Pre-split the index (manually insert values to spread branch blocks before heavy inserts).
•Consider a local index on a partitioned table.
C. If it’s in RAC
•Reduce gc buffer busy waits by:Partitioning data to ensure each RAC instance works mostly on its own subset.
•Using instance affinity for sequences
or Scalable sequence.
•Using cache sequences with different starting points per instance.
6. How to Detect Hot Blocks
ASH:
SELECT event, current_obj#, current_file#, current_block#, COUNT(*)
FROM v$active_session_history
WHERE event LIKE 'buffer busy%'
GROUP BY event, current_obj#, current_file#, current_block#
ORDER BY COUNT(*) DESC;
Segment Statistics:
SELECT *
FROM v$segment_statistics
WHERE statistic_name LIKE 'ITL waits%' OR statistic_name LIKE 'buffer busy%'
ORDER BY value DESC;
In short:
Oracle can mitigate but not eliminate hot blocks. The permanent fix is usually schema design changes or application logic changes that spread data access across multiple blocks.
No comments:
Post a Comment