px等待事件

PX Deq: Execute Reply

Waiting Process: QC
The QC is expecting a response (acknowledgement) to a control message from the slaves or is expecting to dequeue data from the producer slave set. This means he waits that the slaves finished to execute the SQL statement and that they send the result of the query back to the QC.
Wait Time:
This a idle wait. The QC wait to get messages or data back from the slaves
Systemwide Waits:
Possible root causes are:
Investigate the slaves what are the doing. Bad tuned SQL statements can be a root cause. The slave needs a long time to execute the SQL statement and the QC waits for the slaves.
Reducing Waits / Wait times:
You can only reduce the wait time when the SQL command is faster executed by the slaves. So investigate what the slaves are doing and the execution plan of the statement.

-------------------------------------------------------
PX Deq: Table Q Normal
Waiting Processes:Slave
Indicates that the slave wait for data to arrive on its input table queue.
In a parallel execution environment we have a producer consumer model.
One slave set works on the data ( e.g. read data from disk , do a join ) called the produces slave set and the other slave set waits to get the data that the can start the work. The slaves in this slave set are called consumer. The wait event "PX Deq: Table Q Normal" means that the slaves in the consumer slave have to wait for rows( data ) from the other slave set that they can start there work.
Wait Time:
This is effectively an idle wait..
Systemwide Waits:
This is a idle wait event, but a high wait time can indicate that the other slave set is not fast enough to produce data or there is a problem in the communication between slave sets.
Reducing Waits / Wait times:
Investigate the slaves in other slave set why they are not able to send the data faster.
----------------------------------------------------
PX Deq: Execution Msg
Waiting Process: Slave
This is one of the main events used in a parallel query .
It is used when the PQ slave is waiting to be told what to do. (eg: when waiting to be told parse / execute / fetch etc..)
This is the same as the "KXFX: Execution Message Dequeue - Slave" wait event in Oracle 8.0.
Wait Time:
This is effectively an idle wait.
When the wait times out the process waits again (incrementing passes) until a message is received.
This should really be considered as an idle wait. The value of P1 gives the process being waited on for a message (if any).
Systemwide Waits:
At systemwide level this should be considered as an idle wait. It indicates parallel query slave processes exist.
Reducing Waits / Wait times:
Not applicable as this is an idle wait.
----------------------------------------------------------------
"PX Deq Credit: send blkd
Waiting Process: QC,Slave The process wishes to send a message and does not have the flow control credit. Process must first dequeue a message to obtain the credit. Indicates that the receiver has not dequeued and/or completely consumed the prior message yet. "PX Deq Credit: send blkd" and "PX Deq Credit: need buffer" are nearly the same. Due to internal reason you see "PX Deq Credit: send blkd" more on local systems and "PX Deq Credit: need buffer" more on RAC systems.
Wait Time:
This is considered as idle wait event.
You should investigate the sender (decode the senderid).
In same cases it is normal that we see high waits on "PX Deq Credit: send blkd".
An example: We do a select from a large table in parallel.
select /*+ parallel(sales, 10) +/ * from sales)
Than you see a lot of waits for "PX Deq Credit: send blkd" in the slave traces. All slaves wait for the QC to get back the credit bit. The QC can not dequeue fast enough the rows from the slaves, beause there are to many slaves that send rows back to the QC. In this case it is normal.
Systemwide Waits:
There is no general advice to reduce the waitime for this event.
Reducing Waits / Wait times:
There is no general advice to reduce the waitime for this event.
------------------------------------------------------------------------
The PX qref latch

In a parallel execution enviroment the query slaves and the
query coordinator are interconnected via queues to exchange
data and messages. The PX qref latch protects these queues.
Contention on this latch usually indicates that the sender
is faster than the receiver. In some cases this is unavoidable
if the activity that is being performed in the consumer is
computationaly complex. In some cases increasing the buffer
size ( via the parameter parallel_execution_message_size )
can help as the fraction of communication overhead
diminishes with respect to the amount of useful work being
done but it can also cause situations where the consumer
is idle for longer periods of time because it must wait
for the producer to produce larger batches of data.

If the system workload is high consider to decrease the degree
of parallelism. If you have DEFAULT parallelism on your object
you can decrease the value of PARALLEL_THREADS_PER_CPU.

Have in mind
DEFAULT degree = PARALLEL_THREADS_PER_CPU * #CPU's
----------------------------------------------------------------------
PX server shutdown
Waiting Process: Process performing database shutdown During normal or immediate shutdown ( or a logical standby failover can also trigger this ) the Parallel Query Slaves ( PX servers ) are posted to shutdown cleanly. If, after 10 seconds, any Parallel Query Slaves are still alive, they are just killed. The wait event indicates that the shutdown process is waiting for the slave to respond to the ORA-448 that was sent to it.
Wait Time:
This a non idle wait. The wait event indicates that the shutdown process is waiting for the slave to respond to the ORA-448 that was sent to it. When we get no response within 10 seconds we kill the slaves.

Systemwide Waits:
Possible root causes are: The slave may have been a PDML slave in which case some txn rollback work may be performed, or possibly the slave was in the middle of some operation where it doesn't check for interrupts as frequently as it should (as example hash joins have been known to be guilty of this ). If the slave process died for some reason, pmon must mark the process dead which can also add delay.
Reducing Waits / Wait times:
There is no general advice to reduce the waitime for this event.
----------------------------------------------------------------------
PX Idle Wait
Waiting Process: Slave
Indicates that a slave process is available and waiting for a request from a new QC to "acquire" him for a pending parallel operation. Time accrued to this event does not indicate a performance problem.
This is the same as the "Parallel Query Idle Wait - Slaves" wait event in Oracle 8.0.
Wait Time:
Waits for this waitevent can generally be ignored as the slaves are in an idle state.
Systemwide Waits:
This is a idle wait event.
Reducing Waits / Wait times:
This is a idle wait event.
----------------------------------------------------------
PX Deq: Join ACK
Waiting Process: QC
When we execute a statement in parallel, the Query Coordinator has to build the slave sets. The QC first sends join messages to each slave in the set being obtained, then waits for a join acknowledgement message to be returned from each slave in the set. This operation is repeated again if the parallel operation requires a second slave set.
Wait Time:
This a non idle wait. When we execute a statement in parallel, the Query Coordinator has to build the slave sets. The QC first sends join messages to each slave in the set being obtained, then waits for a join acknowledgement message to be returned from each slave in the set. This operation is repeated again if the parallel operation requires a second slave set. When a slave joins a slave set it allocates memory from the shared pool if PARALLEL_AUTOMATIC_TUNING = FALSE or in the large pool if PARALLEL_AUTOMATIC_TUNING = TRUE. We need this memory for the communication between 1.) slave to slave 2.) slave to QC.
The wait indicates that the QC sends a message to a slave and wait until memory is allocated and he gets a message back from the slave.
Systemwide Waits:
This wait event should be investigated at query level and not at instance level.
Reducing Waits / Wait times:
There is no general advice to reduce the wait time for this event.
Possible root causes are:
Someone killed on the OS level PQ slave processes or they died. Until PMON has not clean up the internal strutures a new QC assumes they are still alive. When a new QC tries to get slaves he sends message to the slave to join the slave set, but we get no acknowledge message back.Check if someone killed a idle slave or a slave process has died. Compare the entries in V$PX_PROCESS and on OS level the name of the started PQ slaves.
Another possible root cause is when SGA memory is so depleted that it is possible that the slave may not be able to establish its response channel to the QC to acknowledge the join message, force the slave to die so that the QC can be notified via PMON cleanup etc.
------------------------------------------------
PX Deq Credit: need buffer
Waiting Process: QC,Slave
The slaves and the QC use table queue's for the communication. Each table queue uses buffer to send data between two slaves or a slave and the QC. A credit bit protects the table queue that only one process ( slave /QC ) can send data.
There is one credit which protects the table queue's. A process needs the credit bit if it have to send data via a table queue. The credit bit is send via a buffer from slave to slave or from a slave to the QC. When we wait for "PX Deq Credit: need buffer" we wait for a buffer from the other slave /QC that the we get the credit bit and can send a block.
Wait Time:
This is considered as idle wait event. ( This means here that a user can not do so much to avoid this event. )
Systemwide Waits:
There is no general advice to reduce the waitime for this event
Reducing Waits / Wait times:
There is no general advice to reduce the waitime for this event.
----------------------------------------------------------
PX Deq: Signal ACKWaiting Process:QC
The QC sends control messages to the slaves. The slaves have to send back acknowledge receipt. The QC waits to get this acknowledge receipt and this is the wait time for this event.
Wait Time:
This idle wait. QC is waiting that he gets a acknowledge back from a slave.
Systemwide Waits:
There is no general advice to reduce the waitime for this event.
Reducing Waits / Wait times:
There is no general advice to reduce the waitime for this event.
-------------------------------------------------
PX Deq: Parse ReplyWaiting Process: QC
A SQL statement running in parallel is split into smaller parts by the QC until 10g. In 10g the behaviour change. From the smaller parts the QC makes SQL statements and sends these to the slaves. The wait indicates that the QC has to wait until slaves parse the SQL statements and binds variable.
Wait Time:
This a non idle wait. We are waiting that the slaves parse their SQL statements. In a single instance environment only 1 slave will do a hard parse and the other slaves will do a soft parse. The wait event does not tell you which slave will do the hard parse. If the slaves finshes his parse he can start to work.
Systemwide Waits:
Possible root causes are:
A problem with the library cache could cause long waits on this event. Possible a library cache latch contention. Tune the library cache.
Reducing Waits / Wait times:
There is no general advice to reduce the waitime for this event.

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/13177610/viewspace-680285/，如需轉載，請註明出處，否則將追究法律責任。