Hot Blocks Analysis (hotblocks.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script identifies “hot blocks” in the buffer cache that are causing cache buffers chains latch contention. It:

• Finds cache buffers chains latches with high sleep counts
• Maps these latches to specific database blocks
• Identifies the segments (tables/indexes) containing these hot blocks
• Shows the touch count (TCH) indicating how frequently blocks are accessed

The Script

set lines 132

select /*+ ordered */
  e.owner ||'.'|| e.segment_name  segment_name,
  e.partition_name,
  e.segment_type,
  e.extent_id  extent#,
  x.dbablk - e.block_id + 1  block#,
  x.tch,
  l.child#
from
  v$latch_children  l,
  x$bh  x,
  dba_extents  e
where
  l.name    = 'cache buffers chains' and
  l.sleeps  > &sleep_count and
  x.hladdr  = l.addr and
  e.file_id = x.file# and
  x.dbablk between e.block_id and e.block_id + e.blocks - 1
/

Usage

-- Basic usage
@hotblocks.sql

-- When prompted, enter:
-- sleep_count: Minimum number of sleeps (e.g., 100)

Parameters

The script prompts for:

• &sleep_count - Minimum sleep count threshold for latches to investigate

Required Privileges

SELECT ON V$LATCH_CHILDREN
SELECT ON X$BH
SELECT ON DBA_EXTENTS

Sample Output

SEGMENT_NAME              PARTITION_NAME SEGMENT_TYPE    EXTENT#    BLOCK#        TCH     CHILD#
------------------------- -------------- --------------- ---------- ---------- ------- ----------
HR.EMPLOYEES                             TABLE                    3        142     255       1024
HR.EMPLOYEES_PK                          INDEX                    1         47     198       1024
SALES.ORDERS                             TABLE                   15       3891     312       2048
SALES.ORDER_ITEMS         P_2024_01      TABLE PARTITION          8        892     201       2048

Key Output Columns

• SEGMENT_NAME - Owner and name of the hot segment
• PARTITION_NAME - Partition name if applicable
• SEGMENT_TYPE - Type of segment (TABLE, INDEX, etc.)
• EXTENT# - Extent number within the segment
• BLOCK# - Block number within the extent
• TCH - Touch count (number of times block accessed)
• CHILD# - Child latch number

Common Use Cases

Diagnose Application Scalability Issues

@hotblocks.sql
-- Enter: 1000 for sleep_count
-- Look for blocks with very high TCH values

Identify Contention Points

@hotblocks.sql
-- Enter: 100 for sleep_count
-- Focus on segments appearing multiple times

Troubleshooting Hot Blocks

Common Causes:

1. Primary key index root blocks - Frequent inserts using sequences
2. Hot table blocks - Tables with high concurrent updates to same blocks
3. Segment header blocks - Frequent space management operations

Resolution Strategies:

1. For indexes: Consider reverse key indexes or hash partitioning
2. For tables: Use ASSM, increase PCTFREE, or partition tables
3. For sequences: Increase sequence cache size or use NOORDER
4. Application changes: Reduce contention through design changes

Additional Analysis:

-- Get more details about a specific hot block
SELECT * FROM v$bh
WHERE file# = &file_number
AND dbablk = &block_number;

-- Check current latch statistics
SELECT * FROM v$latch_children
WHERE name = 'cache buffers chains'
ORDER BY sleeps DESC;

Related Scripts

• Blocking Locks - Find blocking sessions
• Buffer Pool Statistics - Buffer cache analysis
• Latch Statistics - Overall latch contention