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.
Senior Oracle DBA