Re: Environment for Parallel Query?

Your point about parallel_max_server is valid. Your query is (presumably) running two parallel hash joins, but can only use parallel degree 2 because it will need two layers of slaves.

I wouldn't set the parameter above 16 though.

Run the query, then check the contents of v$pq_tqstat. (script available on my web site - and in the book, I think) I suspect you will find that there is a huge amount of message passing going on, and this is
often the critical problem with PQ. You may also find that there is a massive imbalance in the work done by the two slaves at each level - which might balance automagically if you have 4 slaves per level.

Run a full explain plan on the query, and make sure you get the SQL passed to the
PQ slaves, so that you know what is happening where.

From the look of it, you are going to dump a huge amount of data to TEMP as you
use one hash join to generate data for the other. You MAY find that your optimum
strategy is change the execution path completely. even to the extent of running parallel tablescan on the largest table, then doing an indexed access from there. (The index root block latching problem may make this unusable as a strategy though).

In answer to your recent query about the direct_io parameter - a parallel scan uses direct I/O bypassing the buffer, so if everything going on here is in parallel, then the multiblock read count should have no effect, only the direct io read count (can't remember exact name) is likely to.

Your concern about striping may be valid - some people have been known to stripe as low as 8K per disc, which would be bad news for PQ scans. However if the stripe size is around 64K to 1MB per disc then it is in the right ball park.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar - UK, April 3rd - 5th
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases


Galen Boyer wrote in message ...


>Oracle 8.1.7.2.

>