Buffer Busy Wait on Temp

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
buffer%20busy%20waits.jpg

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

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License