Query Generating 60 GB of Temp

A query that was running fine for over a year now generated 60 GB of temp causing all sorts of havoc on the system. I’ve never seen huge temp generation before from a single query with hash join of 4 medium size tables ranging from 800K to 7 million. An explain plan showed nothing outside of the ordinary, but I noticed 2 of the tables didn’t have any statistics. I then gathered stats and checked the explain plan again. Just the estimated number of rows changed a bit (became more accurate). I reran the query and it worked fine.

But wait a minute, the query was running fine before, what happened?

There was a bit more data (few hundred thousands more in 2 tables) and that caused Oracle to act erratically like this!!!

If you want to estimate temp size generated by your query without actually executing the sql, just do an explain plan, then select * from table(dbms_xplan.display). If your query will generate temp, you’ll see a column TempSpc with the amount of temp expected to be generated.

Hazem Ameen
Senior Oracle DBA


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s