Re: parallel query and latch waits

Is a query that completes in 3 elapsed minutes with 30 seconds of latch waits and 11 minutes of CPU usage better than a query that completes in 12 minutes using 6 minutes of CPU and 6 minutes of disk waits ? It depends on circumstances and side-effects.

In simple cases, PX slaves behave exactly as you say, they don't touch each others data because the typical, simple, PX query either runs one partition per slave for partitioned tables, or separates non-partitioned tables into disjoint rowid ranges. Of itself, this would not stop 'cache buffers chains' latching - however PX slaves also tend to do direct reads, bypassing the buffer and therefore having nothing to do with the hash buckets.

However, non-trivial parallel queries are often driven in parallel from one table, but index into another table, leaving you with a second stage of processing that (a) cannot be made disjoint, and (b) has to go through the cache buffer.

Maxim: Parallel Execution wastes resources in order to reduce elapsed time to completion.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases
Publishers:  Addison-Wesley

Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Yong Huang wrote in message ...


>Based on your analysis, I guess it's even better the original poster

>runs his query without parallel execution because PX slaves have to

>sleep or spin on the latches anyway, and sleep involves context switch

>overhead.

>

>I wonder if we can tell each PX slave to read certain rows so two

>slaves won't touch each other's "assignment". At the end, the

>SUM(IMPRESSIONS) in the original query is done based on each slave's

>result.

>

>Yong Huang