Introduction
I was asked to investigate an issue on a system the other day that was generating a lot of buffer busy waits. One query saw half its time spent waiting on a buffer busy wait. This article explains how it was investigated.
Step-By-Step
1. Work out what type of buffer busy waits we are seeing.
SELECT event, p1, p1text, p2text, p2, p3text, p3, COUNT (*) FROM dba_hist_active_sess_history WHERE sql_id = '&MySQLID' AND sample_time BETWEEN TO_DATE ('2022-11-25 17:00:00', 'yyyy-mm-dd hh24:mi:ss') AND TO_DATE ('2022-11-25 18:15:00', 'yyyy-mm-dd hh24:mi:ss') GROUP BY event, p1, p1text, p2text, p2, p3text, p3 ORDER BY 8 DESC

2. identify the File
Form the output above we can see that p1 gives us 201 for file#
3. identify the block type
From the output in step 1 we see that p2 (bliock#) is 2 and p3 (class#) is 13.
So, what is going on? What is file# 201? What is block 2 and what is block class 13 and why so many "Buffer Busy Waits"?
Well, I was given a few hints when I asked for a few more details on the process running. I was told there was a job that had 48 parallel processes that consumed a lot of temp space. So much, that it failed with the standard ORA-01652: unable to extend temp segment…. Temp was extended and the job restarted. When it ran the second time, we saw a lot of buffer busy waits.
Firstly, its important to remember how temp files get their p1 value. Temp gets a P1 value based on the file# from v$tempfile + the value of parameter db_files. (In this case the default of 200). hence, 1+200=201
So, we now know the file involved is Temp.
Now we need to find out what a block class# of 13 is.
A useful query I picked up from Tanel Poder (https://github.com/tanelpoder/tpt-oracle/blob/master/bclass.sql) shows us that block class 13 is a file header block.
So now we are getting somewhere…
What all this is telling us, is that the LMT bitmap file header (thats block 2 in a LMT tablespace) is getting a large number of buffer busy waits when a parallel process is consuming a lot of temp space.
Or in other words, we have a lot of parallel threads all trying to extend temp at the same time and blocking on block 2 of the temp file.
Published 28th November 2022