Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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