Re: Parallel Query Sync Wait

I think I've seen this problem before in 7.3.3.5 very intermittently.

The p2raw value on a pq dequeue is the wait timeout in hundredths of a second if the high bit is 0, but identifies the slave that is expected to send a reply if the high bit is 1.

In your case, this session is waiting for a specific slave, which is:

    instance 0 (rest of high word)

    P001 (low word).

The p1 parameter is the 'reason for dequeue', and I think reason 4 is 'waiting for reply to join group',

The odd thing is that I don't think there is an instance 0 - I haven't been able to catch a 'dequeue waiting for a slave' on my v7 instance but on an OPS system, the smallest instance number is 1, and on my v8.1.5 the instance number also comes out at 1. Maybe this is different in v7 single instance.

I think I would call it in to Oracle support, and send them the results from the V$session_wait.

They might ask you to set a couple
of events to trace the query co-ordinator activity to see when this problem occurs. (Was SID 121 the QC, by the way, i.e. the initial background process) ?

My guess at the moment is that you've managed to catch a race condition when 2 parallel queries start at once.

--

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

Ben F Oliver wrote in message <7mjj4o$kus_at_dfw-ixnews9.ix.netcom.com>...

>
>The database is Oracle 7.3.3.4. The tool I use to monitor is Precise SQL
and it
>indicates Parallel query sync wait when the process goes into a lull. I
caught it
>when it was in the wait state and queried the data dictionary thoroughly
and did not
>see any processes waiting on locks or latches. I queried v$session_wait and
got the
>following:
>
>QL> /
>
> SID SEQ#
>---------- ----------
>EVENT
>----------------------------------------------------------------
>P1TEXT P1
>---------------------------------------------------------------- ----------
>P1RAW P2TEXT
>-------- ----------------------------------------------------------------
> P2 P2RAW
>---------- --------
>P3TEXT P3
>---------------------------------------------------------------- ----------
>P3RAW WAIT_TIME SECONDS_IN_WAIT STATE
>-------- ---------- --------------- -------------------
> 121 5636
>parallel query dequeue wait
>reason 4
>00000004 sleeptime/senderid
> 268435457 10000001
>passes 2827
>00000B0B 0 0 WAITING
>
>
Received on Fri Jul 16 1999 - 05:22:53 CDT