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

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 - 06:29:52 CDT