Re: Run ANALYZE regularly?

In article <383E5188.1AA7C7E5_at_totalise.co.uk>,   Martin Hepworth <maxsec_at_totalise.co.uk> wrote:
> 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
>

Nice script, but may I suggest extending the estimate to include a sample size. A base estimate only reads the first 1064 rows in the table and caused us all kinds of grief prior to the sample size working back in ver 7.1.6. [Note in ver 7.0 the SQL manual had the wrong syntax and when you entered the correct syntax for a sample size it was accepted but ignored until sometime after 7.1.3] We use 50000 rows and this works well for us up to 40 million rows which is our largest row count.

Going back to Brian's post: Be careful about re-analyzing report/work tables that get emptied and reloaded all the time. You want to capture these kinds of tables at a time when they have representive data and keep the stats until you have to update them. Or code SQL against them with hints to always run a specific way.

Also there is no real need to re-analyze static tables at all.

And naturally you should re-analyze after every release upgrade to pick up any new changes in the optimizer statistics. --
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Nov 26 1999 - 13:04:59 CST