From: "Bob Fazio" Subject: Re: analyse_database Date: 2000/07/04 Message-ID: #1/1 References: <39605344.DDCCDDD2@adelaide.edu.au> <8jq5tt$hhq$1@nnrp1.deja.com> X-Priority: 3 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600 X-Complaints-To: abuse@home.net X-Trace: news1.rdc2.pa.home.com 962714024 24.8.218.197 (Tue, 04 Jul 2000 05:33:44 PDT) Organization: @Home Network X-MSMail-Priority: Normal Reply-To: "Bob Fazio" NNTP-Posting-Date: Tue, 04 Jul 2000 05:33:44 PDT Newsgroups: comp.databases.oracle.server,comp.databases.oracle.tools 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 '@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 @@/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@home.com remove nospam from reply address http://24.8.218.197/ "Mark D Powell" wrote in message news:8jq5tt$hhq$1@nnrp1.deja.com... > In article <39605344.DDCCDDD2@adelaide.edu.au>, > Steve Salvemini 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.