I am facing a problem with collect stats.
One collect statistics is submitted using a ETL id, it runs for more than an hour and errors out with spool space error. The table is a big one. When the same collect stats is submited with a sysdba id, it goes fine.
The stats in being collected on the table level.
what is the solution for this.
1. The ETL id is part of a batch profile for which the spool limit is fixed. Is this not working becasue the spool allocated for the ETL id is being surpased by the stats ? I cant increase the sppol space for one particular id as it is part of batch profile.
2. If i collect on column level instead of table level in the job, will that make any difference ?
3. Generally spool error is encountered if the joins are bad in a query, but i am seeing this eror in stats collection for the first time. does stats collection also use spool ?
We also encounter this issue.Generally the tablesize will be more than the spool space allocated to Profile of the Batch user-id.Either reduce the table size by moving old data to history table to avoid table becoming bigger.(This will also help in query performance)
for 1) yes this is because while performing stats collection the spool requiorement of table is more and hence failure.
2)Collecting stats at table column level is what utlimately gets executed when we execute collect stats at table level.So will not make any diff.
3)For analysis of table during collect stats..spool space is used.
hope this clarifies..
As Collect Stats is similar to a "SELECT col, COUNT(*) FROM tab GROUP BY 1 ORDER BY 1;" you might need a lot of spool when it's a large column/multiple columns which are almost unique.
You can check the output of HELP STATS to spot it.
And if it's a large table with almost unique you can probably switch to SAMPLE stats.
Thanks Diether for the reply.
We had two collect stats going on at the same time, with the same id. That might have caused the sppol error. We have changed code to submit one after another. This might help.
I had posted another question for which i have not got a reply from anyoen. Could you please help. The post subject is: