| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Parallel Query Sync Wait
In article <931507854.25665.0.nnrp-01.9e984b29_at_news.demon.co.uk>,
"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
>
>What tool do you use, and which version of Oracle.
>
>'Parallel Query Sync Wait' does not exist as a
>wait event in 7.3.3, 8.0.4, or 8.1.5
>
>When it stick again, identify the SID, then do:
>
>set linesize 2000
>set trimspool on
>spool {spool file}
>select * from v$session_wait where sid = {whatever it was};
>
>repeat the select every few seconds to get 5 consecutive results
>spool off
>and post the results
>
>--
>
>Jonathan Lewis
>Yet another Oracle-related web site: www.jlcomp.demon.co.uk
>
>Ben F Oliver wrote in message <7m3v4m$2i8_at_dfw-ixnews15.ix.netcom.com>...
>>I am running PQ Option on a multi-processor HP-UX system. Occasionally, one
>batch process
>>locks up. When investigated, the tool I use shows Parallel Query Sync Wait.
>I cannot find any
>>info on Metalink and forget about asking Oracle Support.
>
>
>
OK, it finally failed again. What the process is doing is the following: Main shell script kicks off four jobs in backgroud. Each of the foru jobs is a Pro*C program that calls a package with a parameter. The package calls a proceedure with the passed parameter and the proceedure gets a selected hunk of data based on the parameter passed. Therefore, four processes are going after the same tables seeking data from different range based on the parameter passed. Confusing!
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
SQL> /
SID SEQ#
---------- ----------
EVENT
----------------------------------------------------------------
P1TEXT P1
---------------------------------------------------------------- ----------
P1RAW P2TEXT
-------- ----------------------------------------------------------------
P2 P2RAW
---------- --------
P3TEXT P3
---------------------------------------------------------------- ----------
P3RAW WAIT_TIME SECONDS_IN_WAIT STATE
-------- ---------- --------------- -------------------
121 5645
parallel query dequeue wait
reason 4
00000004 sleeptime/senderid
268435457 10000001
passes 2836
00000B14 0 3 WAITING
SQL> /
SID SEQ#
---------- ----------
EVENT
----------------------------------------------------------------
P1TEXT P1
---------------------------------------------------------------- ----------
P1RAW P2TEXT
-------- ----------------------------------------------------------------
P2 P2RAW
---------- --------
P3TEXT P3
---------------------------------------------------------------- ----------
P3RAW WAIT_TIME SECONDS_IN_WAIT STATE
-------- ---------- --------------- -------------------
121 5653
parallel query dequeue wait
reason 4
00000004 sleeptime/senderid
268435457 10000001
passes 2844
00000B1C 0 3 WAITING
SQL> /
SID SEQ#
---------- ----------
EVENT
----------------------------------------------------------------
P1TEXT P1
---------------------------------------------------------------- ----------
P1RAW P2TEXT
-------- ----------------------------------------------------------------
P2 P2RAW
---------- --------
P3TEXT P3
---------------------------------------------------------------- ----------
P3RAW WAIT_TIME SECONDS_IN_WAIT STATE
-------- ---------- --------------- -------------------
121 5664
parallel query dequeue wait
reason 4
00000004 sleeptime/senderid
268435457 10000001
passes 2855
00000B27 0 0 WAITING
SQL> /
SID SEQ#
---------- ----------
EVENT
----------------------------------------------------------------
P1TEXT P1
---------------------------------------------------------------- ----------
P1RAW P2TEXT
-------- ----------------------------------------------------------------
P2 P2RAW
---------- --------
P3TEXT P3
---------------------------------------------------------------- ----------
P3RAW WAIT_TIME SECONDS_IN_WAIT STATE
-------- ---------- --------------- -------------------
121 5672
parallel query dequeue wait
reason 4
00000004 sleeptime/senderid
268435457 10000001
passes 2863
00000B2F 0 3 WAITING
SQL> /
SID SEQ#
---------- ----------
EVENT
----------------------------------------------------------------
P1TEXT P1
---------------------------------------------------------------- ----------
P1RAW P2TEXT
-------- ----------------------------------------------------------------
P2 P2RAW
---------- --------
P3TEXT P3
---------------------------------------------------------------- ----------
P3RAW WAIT_TIME SECONDS_IN_WAIT STATE
-------- ---------- --------------- -------------------
121 5681
parallel query dequeue wait
reason 4
00000004 sleeptime/senderid
268435457 10000001
passes 2872
00000B38 0 0 WAITING
SQL> spool off
I hope you can determine something from this as I cannot find any info on the web that can help me.
Thanks Received on Thu Jul 15 1999 - 00:02:12 CDT
 |
 |