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

From: Sridhar Subramaniam <avion_at_ozemail.com.au>
Date: 1996/11/26
Message-ID: <329AC62A.7D5E_at_ozemail.com.au>#1/1


Dana Stockler wrote:
>
> I asked this question before, but got no answer so I'll try
> again.
>
> 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.
>
> Regards,
>
> -----------------------------------------------------------
> Dana Stockler
> -----------------------------------------------------------
> Home: Solstadlia 10, 1364 Hvalstad, Norway ((+47)66789741)
> Work: IngeniørData as, Strøket 9, 1370 Asker, Norway
> Email: stockler_at_ingdata.no
> -----------------------------------------------------------
Hi Dana,

'analyze' on a table produces stats which update user_tab_cols, user_tables ... dictionary views. This surely would need a full table scan on tables if you use it with the 'coumpute stats' option. So, i guess you temporary tablespace should be atleast as large as the largest table.

You may want to consider doing an estimation instead. You can, with this option, specify the %age of the table you want to scan to produce the stats.

analyse table xxx estimate statistics ... ( check the manuals for syntax )

hope this helps

-- 
Cheers

Sridhar Subramaniam
Avion Consulting Services
Sydney - Australia
Email : avion_at_ozemail.com.au

Disclaimer : All opinions are truly and just mine.
Received on Tue Nov 26 1996 - 00:00:00 CET

Original text of this message