High buffer Busy Waits on file# XXXX and block# X (Doc ID 2579040.1)

Last updated on APRIL 17, 2023

Applies to:

Symptoms

exadata : dbodsp12: High concurrency on "buffer busy waits"

Every blocker is waiting on file# 2001 and block# 2

 which is waiting for 'buffer busy waits' with wait info:
  {
  p1: 'file#'=0x7d1 >>>>>>>>>>>> In decimal 2001
  p2: 'block#'=0x2 >>>> In decimal 2
  p3: 'class#'=0xd
  time in wait: 0.662292 sec (last interval)
  time in wait: 32.524884 sec (total)
  timeout after: never
  wait id: 341004
  blocking: 33 sessions

"Even we tried to capture AWR report for the problem period, the session was waiting for the same wait event on the same file and block!!"

Changes

 Similar issue discussed in below bugs, but closed as not a bug

Bug 27391070: HIGH BUFFER BUSY WAITS ON ONLY FEW TEMPFILES
BUG 22294912: CONTENTION ON TEMPFILE BLOCK#2 SLOWDOWN THE CLUSTER DB  

This is not seen as a oracle code issue, rather an application Design/Tuning issue. The customer may think on the lines of :

1) Set up different temporary tablespaces based on the sorting requirements. The use of temporary tablespaces improves concurrency and decreases the frequent allocation and deallocation of space.

2) Temporary segments use the default storage settings specified for their tablespace. You should define identical values for the INITIAL and NEXT parameters. In addition, you should specify the value of the PCTINCREASE parameter as zero.

3) It is advisable to create tablespaces with different default storage clauses and assign them to users based on their sorting requirements.  Creating independently configured tablespaces will result in an optimum utilization of space in a database.

4) For estimating the size of the temporary tablespaces required to store sort segments, you need to know the number of extents and the blocks used by the largest sort operation that used a given segment. This can be retrieved from the MAX_SORT_SIZE and MAX_SORT_BLOCKS columns.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.