I am getting out of spool space error, I checked the explain plan of the query, it looks good. Even after increasing the spool space around 20 % , still getting the same error.
Someone suggested me to collect stats on few columns to mitigate the spool space problem. If this is true could you please explain me the reason behind this. Also, is there any way of getting round this problem-
Re: relation between spool space and collect stats?
Collecting statistics doesn't reduce spool space on it's own. It helps optimizer to make "better" decisions. which might often lead to reduced spool space usage. (For example it might decided to duplicate the small table in All amps rather than redistributing the large table, etc ...)
So you can try collecting statistics. But that need not help if the plan was already the best optimizer could do for the given scenario.
You can also check for recommended statistics in explain by using dianostic
diagnostic helpstats on for session;
So if all that doesn't help, you might have to look into re-writing the query or increase the spool space.