Re: Parallel Query: lots of wait time in event "PX qref latch"

This is conjecture, but I think you are running parallel queries which your system cannot support.

You have 4 CPUs, but you are asking 8 PX slaves in one 'layer' to distribute messages to 8 other PX slaves in the other 'layer'.

Message passing under parallel execution is done through 'table queues' (see v$pq_tqstat for stats on table queues) and I THINK that each table queue is protected by a latch, which is a PX qref latch. (I may be wrong, it is possible that each slave has a latch, rather than each table queue).

The problem you are seeing is that you have some very busy PX slaves, and more slaves at each end of the table queues than there are CPUs, so inevitably you are seeing
collisions when two slaves want to stuff data into the same table queue.

It may be possible to improve the situation by rewriting or hinting the query so that the lower level slaves do more complex
work that allows them to pass fewer message through the table queues - alternatively, simply reduce the parallelism of the table to 4 and see what happens.

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

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

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.

Herman de Boer wrote in message ...


>Dear *,

>

>I am investigating a site running RDBMS 8.1.6.2.0, on HP V2500, UX11,

>2Gb memory, a 4-cpu box hosting a ~400 Gb database with Parallel

>Query.

>

>Most time is spent in event PX qref latch, with parallel degree 8 (so

>16 slaves are running), with about 50% idle time (as given by top).

>There is a certain amount of disk I/O, but far below the bandwidth.

>Complex sql-statements, mostly pctas.

>

>Anyone useful hints or advice or information what this event is about,

>and why so much time is spent in this one?

>

>Kind Regards,

>

>Herman de Boer

>IT Consultancy Group bv