Resolving Issues Where "Segment With Hot Blocks" is Impacting Performance

Resolving Issues Where "Segment With Hot Blocks" is Impacting Performance

Brief Definition:

Hot blocks refer to block headers that are accessed very frequently (via logical reads) and the frequent access may lead to contention on the cache buffers chains latch.

Problem Confirmation:

Only certain sessions, queries or jobs are experiencing slowness (not throughout the database).
Segments by buffer busy waits , logical reads point to the hot segment.
  

Reducing Waits:

• Inefficient execution plans may perform many logical reads while they visit many blocks. If this query is executed by many sessions concurrently (or other similar queries against the same blocks), then there will be contention on these blocks. Check for suboptimal SQL (this is the most common cause of the events) - look at the execution plan for the SQL being run and try to reduce the gets per executions which will minimize the number of blocks being accessed and therefore reduce the chances of multiple sessions contending for the same block.
• When execution plans are optimal then further checks are needed from your application side to validate whether the concurrency on blocks imposed via repeated execution of sqls are justified.
• If you have assigned a very large buffer cache, then reducing the size may help, in very rare case.
• Index leaf blocks may see contention due to key values that are increasing steadily (using a sequence) and concentrated in a leaf block on the "right-hand side" of the index. Look at using reverse key indexes if range scans aren't commonly used against the segment (remeber that this will mean that range scans won’t be used for this index).
• Use hash partitioning to spread values across blocks.
• Rebuilding table with more PCTFREE may distribute rows in multiple blocks , This will reduce the number of rows per block and hopefully, spread out contention for the blocks (at the expense of wasting space). Ths may cause some queries to run slower because they will need to access more blocks to obtain the same number of rows. 

Measuring Success:

Once you have applied the changes to resolve the issues you have found, compare the latest AWR to the AWR that led you here via Guided Resolution (that AWR becomes your baseline). Look at the percentage decrease total wait time for this event. If there are still issues, re- evaluate those and address them according to the specific symptom.

========================================================================