Spool Space Issues

Database
Enthusiast

Spool Space Issues

Need tips on handling spool space issues?

 

3 REPLIES
Senior Apprentice

Re: Spool Space Issues

Hi Sonya,

 

Thats a bit broad as a topic.

Can you narrow it down a little?

What type of 'spool issues' are you facing (skewed processing or large spool files)?

 

Having said that you could try the following:

- for queries that are giving you skewed spool, check the data value distribution of column(s) named in "redistributed by hash code of **bleep**"

- for queries that are building large spool files, is the SQL sensible? Does it have appropriate selection criteria?

- for all queries that give you any kind of spool issue, check that the estimated row counts are accurate (within an order of magnitude). If they are not then the optimiser may well be producing a 'sub optimal' plan

 

Cheers,

Dave

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

Re: Spool Space Issues

Hi Dave,

 

I have two tables(T1 with 270 million rows and T2 with 3 million rows and I want to join these two tables. Primary index for both the tables is same column(transaction_id). 

 

Thanks

Sonia

Senior Apprentice

Re: Spool Space Issues

Hi Sonia,

 

Thanks for that. Is the PI of each table also included in the join condition(s) of your query?

If it is not then this is possibly going to be a horrible join involving a large spool file.

 

Can you please provide the SQL, DDL of T1 and T2 and the explain of the query?

Also, what is the spool limit of the user running the query?

 

Cheers,

Dave

 

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