Re: analyse_database

I agree somewhat with Mark. analyze_database fails quite often in my experience, and if it does, it just stops, leaving the database half done. Also, it does analyze sys. Always run
dbms_utility.analyze_schema('SYS','DELETE') when done. Also analyze_database, doesn't do histograms, which have made a HUGE impact in performance in my experience.

As far as how often should you analyze you database. It depends on you data, and how often it really changes much. I estimate daily for the most part. Attached is a sql script that I run.

do_analyze.sql
set echo off
set linesize 200
set con off
set feed off
set head off
set pagesize 0
spool /tmp/runme.sql
select '_at_analyze '||owner||'.'||table_name||' &1' from all_tables where
not (owner = 'ENTOWN' and table_name='SWAK') and owner!='SYS' order by owner,table_name;
spool off
set echo on
set con on
set feed on
spool /tmp/analyze.log
_at_@/tmp/runme
spool off
set head on
exit;

analyze.sql ...

analyze table &1 &2 statistics;
analyze table &1 &2 statistics for all indexes;
analyze table &1 &2 statistics for all indexed columns;

Note histograms (for all indexed columns) take a lot of space. If you have the space, then do it everywhere, but if not, just do it where the data is not evenly distributed.

--
Robert Fazio, Oracle DBA
rfazio_at_home.com
remove nospam from reply address
http://24.8.218.197/
"Mark D Powell" <markp7832_at_my-deja.com> wrote in message
news:8jq5tt$hhq$1_at_nnrp1.deja.com...


> In article <39605344.DDCCDDD2_at_adelaide.edu.au>,


>   Steve Salvemini <steve.salvemini_at_adelaide.edu.au> wrote:

> > Is the DBMS_UTILITY.analyze_database something that should be run

> > regularly, ie on a weekly basis, or for new DB refreshes, and does it

> > update SYS tables as well?

> >

> > (Following this, should all SYS tables have stats on them?)

> >

> > I guess the analyse_schema is only really for specific tasks, such as

 a


> > need only to update the stats for a particular schema.

> >

> > Thanks Folks

> >

> 1 - I do not see a need for the analyze_schema command at all.  As a

> DBA you can issue the analyze command directly and it is easy to use


> SQL to generate analyze commands per table/index as desired.  If you

> grant someone execute on dbms_utility they can then mess up the

> optimizer because they do not have the knowledge to know what objects

> require computing vs estimating vs estimating with a large sample to

> prevent bad plans.

>


> 2 - Sys owned objects should definately not be analyzed for ver 7.0 -


> 8.0.  I imagine the same is true for 8.1 or 8i as it is called.

> --

> 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.