Re: parallel query and latch waits

By 'serial run time', 'parallel run time' I meant a simple clock-time for how long the query took to complete.

Your report on the latch seems to suggest a high rate of misses, but since I am not familiar with the particular tool you are using and the way it derives its numbers, I can't really comment. The number I would look at is from v$system_event (which records
wait times in hundredths of seconds if you have parameter timed_statistics set to true) for the event 'latch free'. If the TIME_WAITED is small compared to the run-time of the query then there really isn't much of a problem.

It is the nature of parallel queries that they will raise the level of buffer latch contention - especially if they involve some sort of join or correlated subquery, as you are much
more likely to get several processes (i.e. the PX slaves) all demanding the same set of buffered blocks at the same time.

If the lost time is significant, then you might review the execution plan to see
if you can get a different plan which accesses the data through a different path that results in less contention for buffered blocks.

--
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



hell wrote in message ...


>oracle version 8.17

>"hell" <robkato_at_earthlink.net> wrote in message

>news:8T6U6.2580$c37.298772_at_newsread1.prod.itd.earthlink.net...

>> I looks like the wait is on the cache buffers chains

>> stats look like this

>> latches 1,024

>> gets/sec 126,085

>>  %gets 91.64

>> sleeps /sec 15.47 %

>> %sleeps 100

>> misses/sec 4,540

>> %misses 99.94

>>

>> how do i find the

>> serial run time

>> parallel run time

>>