Re: [Q] How much temporary space is needed to run ANALYZE command?

> Dana Stockler wrote:
> >
> >
> > The ANALYZE TABLE command apparently requires a large temporary
> > segment. I ran out of space in my temporary tablespace three
> > times while trying to analyze my tables. Each time I expanded
> > the tablespace and each time it was not enough.
> >
> > Does anyone know how much temporary space is necessary in order
> > to guarantee the error-free execution of the ANALYZE TABLE
> > command? I want to schedule a batch job for execution once a
> > week to run the ANALYZE command and I want to be sure it
> > works.
> >

On average, a COMPUTE STATISTICS takes temporary space about four times the size of the table. so if the table is 100M, you'll need a 400M temporary segment available - so your tablespace defaults will need to be something like initial 4M next 4M pctincrease 0, with a maxextents of at least 100.

You can minimize the temp space requirements by using estimate statistics, but it's not reliable imho. I have a friend who was having lots of problems with her system - wrong plans chosen by the CBO, etc. Her table is over 300 million records in size. She runs a compute statistics - using a 45G temp tablespace - and the problems disappear. Use Compute statistics until you run out of disk space to waste on Temp.

Kevin Received on Wed Nov 27 1996 - 00:00:00 CET