When 2 queries are running in SAME session, 1st query is failing with spool issue ( after running with high skew after a minute), and a second query( started after the 1st one respended ) , also failing with spool issue immediately in 1 sec. ( 2nd query is a tuned query and running fine when we run seperately )
spool is allocated 5TB for the user. what scenarios this case happens? and how to resolve?
appreciate your help...
Don't know if you've managed to get an answer, but it is quite possible that the second query is running 'too quickly'.
When a query aborts the dbms will clear up any left over spool files. However, that is not always done immediately and so if another query comes in on the same session (or just for the same username) then it is possible that the old spool files haven't been cleared yet. This could lead to what you're seeing.
Thanks Dave.. I agree with you.
We have a BO application in which couple of queries ( reports ) executing in the same session. Some times, if a query fails with a spool issues, subsequent queries running in the 'same session' are also getting failed with "spool issue" even if there is no issue with the performance of them.
Since we can't have control on what user can run, is there any way we can stop this to happen from DB end or Application end ?
Appreciate your response.
There is nothing obvious that comes to mind from the db end. Perhaps the 'solution' is to fix the query that is failing wiht the spool error?