High buffer Busy Waits on file# XXXX and block# X
(Doc ID 2579040.1)
Last updated on JUNE 28, 2021
Applies to:Oracle Database - Enterprise Edition - Version 126.96.36.199.0 and later
Information in this document applies to any platform.
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
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!!"
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.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document