Re: Performance of Multi Stream Analyze Commands over large Schema with partitioned objects

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" <jobrien_at_ieee.org> wrote in message news:24bad735.0206060329.673cfa61_at_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
Received on Thu Jun 06 2002 - 08:57:30 CDT