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

Seems to me you're re-inventing the wheel. Check out dbms_stats, which can collect an entire database's stats with a degree of parallelism, and has a 'gather stale' option for collecting only those stats which need collecting.

analyze table...xxxxx statistics is officially deprecated in 9i. It was getting that way in 8i too.

Regards
HJR "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 - 14:57:02 CDT