Re: PX Deq: Table Q qref
You could try setting event 10391,
levels 64, 512, and 2048 are likely to be
the most informative for you problem.
Add the values together to run all three
at once. The level is a bit flag that goes
up to a total of 8191 - but when you set
8191 it looks as if a couple of the flags
may disable each other.
The trace file is usually a little way behind real
time - you can try:
oradebug setospid {slave o/s pid}
oradebug flush
to get the last bit out.
The Execute Reply is a slave that has
sent all it's data and is waiting for new
instructions from the co-ordinator - typically
a 'die' command.
You are dumping a lot of data to a spool file -
you haven't exceeded the file size that
SQL*Plus can managed have you ? Perhaps
something is going wrong there.
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Author of:
Practical Oracle 8i: Building Efficient Databases
Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html
Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
-Original Message-
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: 11 April 2002 23:25
|All
|
|I have some more information on this. I turned on event 10046 on
this. From
|this I was able to note the following...
|
|1. I start the sql script and spool the results
|
|2. It waits for less than a second on process startup and PX Deq:
Join
|ACK. I think these are waits for starting up the parallel query
slaves and
|then they acknowledging the startup.
|
|3. Then it starts waiting on PX Deq Credit: need buffer and PX Deq
|Credit: send blkd. I think these are when PQ actually returns data
to the
|QC
|
|4. Then it starts witing on PX Deq: Execute Reply. I see numerous
waits on
|this. I am not sure on what this wait means.
|
|5.After two hours, it starts waiting on PX Deq: Table Q qref and
SQL*Net
|message to client. By this time, it has finished writing to the
spool file.
|I verified this from the unix timestamp. The trace also shows FETCH
|#1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=4,tim=57419669
|
|6. After about 3-4 hours later the SQL prompt returns to me. During
this
|time it has not written to the spool file. So I am not sure what is
being
|done in this time...
|
|What is it doing during the Step5 desciribed above? Also why does it
show
|Fetch when I have already gotten the data..
|
|
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jonathan Lewis
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: PX Deq: Table Q qref
All I have some more information on this. I turned on event 10046 on this. From this I was able to note the following... 1. I start the sql script and spool the results 2. It waits for less than a second on process startup and PX Deq: Join ACK. I think these are waits for starting up the parallel query slaves and then they acknowledging the startup. 3. Then it starts waiting on PX Deq Credit: need buffer and PX Deq Credit: send blkd. I think these are when PQ actually returns data to the QC 4. Then it starts witing on PX Deq: Execute Reply. I see numerous waits on this. I am not sure on what this wait means. 5.After two hours, it starts waiting on PX Deq: Table Q qref and SQL*Net message to client. By this time, it has finished writing to the spool file. I verified this from the unix timestamp. The trace also shows FETCH #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=4,tim=57419669 6. After about 3-4 hours later the SQL prompt returns to me. During this time it has not written to the spool file. So I am not sure what is being done in this time... What is it doing during the Step5 desciribed above? Also why does it show Fetch when I have already gotten the data.. Can any of you pl comment on the above... Am I missing anything or am I interpreting it wrong... Thanks a lot Babu - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, April 11, 2002 4:03 AM Not entirely sure, but I think this is a query slave trying to make, or break a link from another layer in the DFO. As the query gets to completion, I would expect to see the QC gathering results, and the slaves either in: PX Deq Send Blkd (i.e. more data to send but another slave is currently passing data to the QC) or PX Deq Execution message (roughly, this is from memory) which means all its data has been transmitted and it is waiting for a message from the QC to tell it to die. 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 -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 10 April 2002 18:05 All What does this wait event mean? I have a process that connects to the database through ODBC and this process is waiting on this event and SQL*Net message from client. Often this process waits for an unreasonable amout of time (4 seconds or more) on SQL*Net MEssage from client and PX Deq: Table Q qref before it successfully completes... TIA Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Babu Nagarajan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
PX Deq: Table Q qref
All What does this wait event mean? I have a process that connects to the database through ODBC and this process is waiting on this event and SQL*Net message from client. Often this process waits for an unreasonable amout of time (4 seconds or more) on SQL*Net MEssage from client and PX Deq: Table Q qref before it successfully completes... TIA Babu
RE: PX Deq: Table Q qref
Looks like you have PQ enabled. These waits are considered idle. The PQ slave processes arewaiting for work to do. - Kirti -Original Message-From: Babu Nagarajan [mailto:[EMAIL PROTECTED]]Sent: Wednesday, April 10, 2002 12:51 PMTo: Multiple recipients of list ORACLE-LSubject: PX Deq: Table Q qref All What does this wait event mean? I have a process that connects to the database through ODBC and this process is waiting on this event and SQL*Net message from client. Often this process waits for an unreasonable amout of time (4 seconds or more) on SQL*Net MEssage from client and PX Deq: Table Q qref before it successfully completes... TIA Babu
Re: PX Deq: Table Q qref
Thats what I thought. But here is more details on the issue.. I am running a SQL*PLus session with a massive query that returns about 2.5g of data, i am spooling the data to file - that is something similar to what the essbase application is trying to do. that session spooled the data in around 2 hours- so the access paths are fine. after getting the data though, my sql session has still not completed. from the size of the spool file i can see the data came more then three hours ago. this session is still doing something and these waits are increasnig and sql is not returning the SQL prompt to me... trying to find out why... babu - Original Message - From: Deshpande, Kirti To: [EMAIL PROTECTED] ; [EMAIL PROTECTED] Sent: Wednesday, April 10, 2002 2:52 PM Subject: RE: PX Deq: Table Q qref Looks like you have PQ enabled. These waits are considered idle. The PQ slave processes arewaiting for work to do. - Kirti -Original Message-From: Babu Nagarajan [mailto:[EMAIL PROTECTED]]Sent: Wednesday, April 10, 2002 12:51 PMTo: Multiple recipients of list ORACLE-LSubject: PX Deq: Table Q qref All What does this wait event mean? I have a process that connects to the database through ODBC and this process is waiting on this event and SQL*Net message from client. Often this process waits for an unreasonable amout of time (4 seconds or more) on SQL*Net MEssage from client and PX Deq: Table Q qref before it successfully completes... TIA Babu
RE: PX Deq: Table Q qref
Babu - Are you running this session in the background? On my system (Compaq Tru64), when I run a SQL*Plus script in the background, I have to put the EXIT command as the last command of the script. Otherwise the script just sits there and echos the prompt into the spool file until it fills up the disk. My system administrator isn't too happy when this occurs, so I try to be careful. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, April 10, 2002 4:38 PM To: Multiple recipients of list ORACLE-L Thats what I thought. But here is more details on the issue.. I am running a SQL*PLus session with a massive query that returns about 2.5g of data, i am spooling the data to file - that is something similar to what the essbase application is trying to do. that session spooled the data in around 2 hours - so the access paths are fine. after getting the data though, my sql session has still not completed. from the size of the spool file i can see the data came more then three hours ago. this session is still doing something and these waits are increasnig and sql is not returning the SQL prompt to me... trying to find out why... babu - Original Message - To: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] ; [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Sent: Wednesday, April 10, 2002 2:52 PM Looks like you have PQ enabled. These waits are considered idle. The PQ slave processes are waiting for work to -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: PX Deq: Table Q qref
No. i am running it from the foreground... babu - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, April 10, 2002 5:48 PM Babu - Are you running this session in the background? On my system (Compaq Tru64), when I run a SQL*Plus script in the background, I have to put the EXIT command as the last command of the script. Otherwise the script just sits there and echos the prompt into the spool file until it fills up the disk. My system administrator isn't too happy when this occurs, so I try to be careful. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, April 10, 2002 4:38 PM To: Multiple recipients of list ORACLE-L Thats what I thought. But here is more details on the issue.. I am running a SQL*PLus session with a massive query that returns about 2.5g of data, i am spooling the data to file - that is something similar to what the essbase application is trying to do. that session spooled the data in around 2 hours - so the access paths are fine. after getting the data though, my sql session has still not completed. from the size of the spool file i can see the data came more then three hours ago. this session is still doing something and these waits are increasnig and sql is not returning the SQL prompt to me... trying to find out why... babu - Original Message - To: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] ; [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Sent: Wednesday, April 10, 2002 2:52 PM Looks like you have PQ enabled. These waits are considered idle. The PQ slave processes are waiting for work to -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Babu Nagarajan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
