Spool Space Error while running a select query

Database

Spool Space Error while running a select query

Hi All,

We are facing Spool space issue with one of the query in our PROD environment.

1.The result set has 10 million records and after running for 40 minutes we are getting no more spool space error.

2.We have created Primar indexes on the columns of the tables which are being used in the query and collecting stats as well on regular bsis.

3.When we are looking at the explain plan at the time of aggregation/grouping of data we are hitting spool space issue.During aggregation/Grouping we notice a Session Skew values increases a lot.

4.Also we don't have any secondary indexes on fact table, are secondary indexes really helps? 

5.Currently DBA's has increased spool space to 6TB for our profiles even after we are getting the same Spool space error

6.Our physical data model is a star schema and due to certain constraints some joins dont go through regular surrogate keys

and fact table has 175 million rows in it.Also quite of our joins to the fact table are being done on Varchar data type

Any thoughts on how to get rid of the error or any tuning tips will be highly appreciated.

Regards,

Sangamesh

3 REPLIES
Enthusiast

Re: Spool Space Error while running a select query

Hi Sangamesh,

Hope you have the partition on your huge fact table and the partition condition exist in your query.

Ensure the stats exist not only on primary but on the aggregated columns, and other important where condtioned columns

In the explain steps find the exact step where it is giving sudden spike of counts, spool or time and concentrate on the columns used in it. If possible see this in viewpoint steps.

Regards,

Sri

Enthusiast

Re: Spool Space Error while running a select query

Are you using left outer joins anywhere?  You could be running out of spool when joining spool files as a result of outer joins.  Could also be natural data skew (i.e. sales in NYC vs Fargo N.D).  Make sure you don't have any aliases that you are not referencing.

Enthusiast

Re: Spool Space Error while running a select query

In order to help you, i would like to see the SQL statement. 

- How many rows are in each of the tables being joined?

- As you mentioned skew: you should check if the join columns have skewed values

- Do you have statistics on the join columns?

- What kind of join type is used by the Optimizer.

Please show us the EXPLAIN output, otherwise it's quite difficult to help.

BR,

Roland

Roland Wenzlofsky