Path: news.easynews.com!easynews!news.he.net!news-hog.berkeley.edu!ucberkeley!newshub.sdsu.edu!west.cox.net!cox.net!newspeer1-gui.server.ntli.net!ntli.net!easynet-monga!easynet.net!newsfeed.esat.net!news.heanet.ie!news.indigo.ie!not-for-mail Reply-To: "Telemachus" From: "Telemachus" Newsgroups: comp.databases.oracle.server References: <24bad735.0206060329.673cfa61@posting.google.com> Subject: Re: Performance of Multi Stream Analyze Commands over large Schema with partitioned objects Lines: 66 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 Message-ID: Date: Thu, 6 Jun 2002 14:57:30 +0100 NNTP-Posting-Host: 194.125.187.82 X-Complaints-To: news@indigo.ie X-Trace: news.indigo.ie 1023371851 194.125.187.82 (Thu, 06 Jun 2002 14:57:31 BST) NNTP-Posting-Date: Thu, 06 Jun 2002 14:57:31 BST Organization: Indigo Xref: easynews comp.databases.oracle.server:149748 X-Received-Date: Thu, 06 Jun 2002 06:54:57 MST (news.easynews.com) Just my $.05 but the latter part of your question - doesn't 9i add a new facility for monitoring table changes for this very purpose ? (and SQL Server does it automatically) what about sample 0.01 % ? how long does that take ? or default estimate doesn't take more than 1064 rows doesn't it ? What about only analyzing the new partitions or alternatively if each new partition is gonna have pretty much the same data distrib just save a set of stats as user stats and then bang them back onto the new partitions "John O'Brien" wrote in message news:24bad735.0206060329.673cfa61@posting.google.com... > Question: > To sum up, given a high processor count SMP or NUMA box how can an > Oracle database be analyzed fast ? > > > Explanation and results: > > I have to analyze a large database weekly and sometimes daily. There > are partitioned objects. > > Running analyze in a single script takes way too long, no matter what > the sample rate. On 8.1.7 dbms_stats can do it in parallel but with > parallel servers, which I do not want to switch on as they can have an > adverse effect in my scenario. Anyway even that aside when a colleague > tested it he did not see big gains. The number of parallel query > servers can only be changed by bouncing the DB which I cannot do > either - not a solution outside of R&D. > > I want a solution that works for 4 to 32 cpus at least and I have the > hardware to test it. > > I wrote a perl script that extracts all the objects by partition out > of the database and forks off a number of streams as specified in a > parameter passed by the user. This means I can get all the paralelism > I need with out a database reboot, unlike parallel query servers. > > The results were not impressive. Massive waitio and the buffer cache > is maxed at over 2GB ( AIX ). This is 32 bit Oracle. > > I changed the db_file_multiblock_read_count to 2048 so it would assume > the max value and saw the waitio drop off somewhat. > > Now aix also uses asynchronous io natively on JFS and I have 10 db > writers running. > > Seems to me like the buffer cache and i/o is the throttle here but I > would like opinions,advice and discussion. > > For instance should I make the script smarter so I have some logic > that determines if an object needs to be analyzed because it has > changed significantly. This could reduce i/o. > > > Regards, > John