About Oracle query hint, this is often said that - "An hint, if ignored by Oracle, is just a comment". But I didn't think that a "mere" hint can lead to a job failure also.
My friend put a parallel hint in one of the SQL query in a job. And the SQL threw Ora-01652 error (Unable to extend temp space). The first thing that came to my mind is - "how come a parallel hint is causing temp space failure?". I do understand that parallel hint can require much more system resources for processing the query - BUT - the total amount of temp space required by that query should remain same. After all the amount of data in the table that the query accesses does not increase when we access the query through multiple parallel threads.
But I was wrong. Very Very wrong.
I put the question to Tom Kyte's forum asktom and here is the answer I got,
Looks like the px coordinators can require some "extra" spaces when they combine the results from different parallel processes.
Did you know that?