I have a query that picks up different columns from different tables by joining them . INNER Join and LEFT outer join are used . 7 tables are used. Now, the query gives me spool space error. But the explain plan shows that it should take 5 secs or so to get executed.
if the estimated row counts to not match it is indicating that the optimizer does not have the right infos to come up with a good plan. Missing or wrong stats.
So you need to spend time to recollect the stats and to maybe collect new stats.
This might result in a different explain plan. When the row counts reflect reality - in high not in absolut values - and you still get the spool space error you might need to check if the spoolspace assigned to your user is sufficient.
Try to do collect stats on the columns used in the join.
If you are using SQA [sql assistant] try this,
Diagnostic Helpstats on for session;
execute the above sql and check the explain plan.
Follow the recommended stats suggested by the explain plan and try executing the sql.
if there are any previous queries still running and consumed all the availble spool sapce and if more spool is required and out of spool space the yongest query will abort . this could be a reson too