Spool error in same session

General
General covers Articles, Reference documentation, FAQs, Downloads and Blogs that do not belong to a specific subject area. General-purpose Articles about everything and anything

Spool error in same session

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?fail.PNG

appreciate your help...

 

 

3 REPLIES
Senior Apprentice

Re: Spool error in same session

Hi,

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.

 

HTH

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com

Re: Spool error in same session

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.

 

Regards,

Kiran

Senior Apprentice

Re: Spool error in same session

Hi,

 

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?

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com