Re: Run ANALYZE regularly?

From: Martin Hepworth <maxsec_at_totalise.co.uk>
Date: Fri, 26 Nov 1999 09:23:20 +0000
Message-ID: <383E5188.1AA7C7E5@totalise.co.uk>

Brian
from the excellant "Oracle performance Tuning" by Gurry & Corrigan published by O'reilly here's a little script that will estimate the stats for large tables and compute them for small tables - should run inside a few hours even for a huge RDBMS.

set pages 0
set lines 132
set term off
set recsep off
spool /tmp/statistics.tmp

SELECT 'analyze table '||owner||'.'||table_name||' '||

       decode(sign(10485760 - initial_extent),1,'compute statistics;',
                                                'estimate statistics;') 

FROM sys.dba_tables
WHERE owner != 'SYS'
/

spool off
spool /tmp/statistics.log
@/tmp/statistics.tmp
spool off

Martin Hepworth

Brian Yan wrote:
>
> I am wondering the pros and cons of running ANALYZE regularly?
>
> We have one OLTP and one data warehouse in production. I found the
> optimizer is set to choose but ANALYZE is not run regulaly. I plan to
> run ANALYZE regularly against both system. But before I run it, I want
> to get some suggestion from experienced people?
>
> 1). Should I run ANALYZE reguarly against both OLTP and DSS system?
>
> 2). From Oracle Performance workshop, it is recommended to run ANALYZE
> regularly for DSS system, but my DSS database is too big, it takes long,
> long time to finish it. What should I do?
>
> 3). What is the optimal interval between each run?
>
> 4). Any other stuff I should pay attention to?
>
> Thank you very much for your help!!!
>
> Brian
>
> Any other suggestion
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Fri Nov 26 1999 - 03:23:20 CST