This is essentially a not for myself for the next time I forget. It seems to be like once every other year I figure this out. It is not too easy to google for so it usually takes a bit too long.
So you have identified the event “direct path read temp” or “Direct path write temp” as your main culprit in a SQL. Great, now let’s just see what temp file it is.
That is how I’ve started a few times and I end up not finding it. Let’s take a look at why it can be confusing at first.
Your select runs long and you take a look at v$session_wait for your SID. If you see “Direct path read temp” as the event being the one your session waits on most of the time, you may want to know what file it is to understand more about why.
It is easy enough, the documentation states:
- P1 = File number
- P2 = First dba
- P3 = block cnt
P1-P3 are columns found in views such as v$session_wait giving parameters the describes the details of different wait events. The names are generic due to different events having different parameters.
If you try to look up the file number in v$data_files or v$temp_files you may find that there is no such file. In my case I was looking for file number 1038, with the database having just a handful datafile and even fewer temp files. Even looking for all files in the CDB, I only found files ranging from 1-650.
It turns out that both temp files and data files are numbered from 1, meaning the database can have a temp file 5 and a datafile 5, but wait events need to be able to distinguish between them with having just one parameter.
After a lot of searching for things like “file number over 1000”, “missing file number oracle” and so forth I usually strike gold with some odd search term and find my way back to this post where the first comment is by Jonathan Lewis and as usual in a few words clearly explains the issue.
For a temp file you have to subtract the initialization parameter db_files from the number found in wait events. After that you have a file number you can look up in v$temp_files.
This post was just to make a post about this to make it easier to find than what the existing ones seems to be when you do not know the reason for this. Maybe it will mostly be used by me next time I forget why temp files does not seem to exist with the numbers reported by the events.