Re: Parallel Query Sync Wait

In article <932121209.12865.0.nnrp-13.9e984b29_at_news.demon.co.uk>,

        "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:

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

 Thanks,
This gives me something to work with. It really bothered me that Precise was labeling this wait as Parallel Query Sync. Wait. The "reason" found in the v$session_wait makes more sense. By the way SID 121 was the QC. When it fails again, I'll look at the slaves and see what they are reporting. I did not check to see if all the slaves were still executing during the lock-up but I have a suspicion that one of the slaves terminated. Do you know any source for detailed information on the v$ view fields?

I will continue to post progress on this thread as I progress. I know that many others using PQO must be having this problem. I will be upgrading to 7.4 soon and am going to create an exact duplicate of my 7.3.3.4 environment and application and see if I have the same problems.

Again, thanks for the help. Received on Sat Jul 17 1999 - 02:32:43 CDT