PX Deq: Execute Reply [message #479726] |
Tue, 19 October 2010 00:34 |
rajatratewal
Messages: 507 Registered: March 2008 Location: INDIA
|
Senior Member |
|
|
Dear All,
I have a partitioned table with degree for parallelism
defined as 10.
I am getting maximum wait events on PX Deq: Execute Reply.
For 2 hours trace the wait event is almost 1.5 hour.
I have done some seraching and i found this.
Quote:
In principle:
A parallel query against a partitioned table will use one slave per partition if the query is thought to span multiple partitions, and it can use all slaves on a single partition if the query is thought to target just one partition. Unfortunately, this is NOT strictly true. It is possible for the optimizer to decide to use parallelism at degree M when accessing N partitions. Sometimes this can lead to very inefficient, brute-force, processing when a more efficient path is available. This can be a particular problem with multi-table joins that should be partition-wise joins. You may be better off leaving the tables defined as non-parallel and adding explicit parallel hints to the code for critical queries.
So i have following questions
1) What is the meaning of PX Deq: Execute Reply.
2) Is this not recommended to use DEGREE clause in
Partitioning.
3) Defining DEGREE clause in partioning of table will
automatically executes DML on table PARALLELY same as
PARALLEL hint clause.
Regards,
Rajat Ratewal
|
|
|
Re: PX Deq: Execute Reply [message #479733 is a reply to message #479726] |
Tue, 19 October 2010 01:28 |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
I saw some PX Deq: Execute Reply recently and did a little research. It was inconclusive, but my best interpretation was that it was either:
- A Parallel Slave that has provided all of its rows to a parent and is waiting for a signal from the parent to shut down, or
- A Parallel Slave that is waiting for more data from other slaves.
I tend to think it is the first one. If I am right, it could be a sign that the workload is unevenly distributed across the slaves. That being the case, you would need to determine how the workload is divided up and see whether it can be leveled.
Ross Leishman
|
|
|
|
Re: PX Deq: Execute Reply [message #479738 is a reply to message #479737] |
Tue, 19 October 2010 02:48 |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Maybe. It's idle, but WHY is it idle. If one thread is idle, does that imply others are busy? If so, is that because the workload has not been spread evenly, or is it because different threads are doing different jobs?
An interesting benchmark would be to unevenly partition a table and then perform a partition-wise parallel scan of the table, checking to see where the unavoidable wait events resulting from uneven distribution showed up.
Ross Leishman
|
|
|
Re: PX Deq: Execute Reply [message #479757 is a reply to message #479738] |
Tue, 19 October 2010 04:21 |
rajatratewal
Messages: 507 Registered: March 2008 Location: INDIA
|
Senior Member |
|
|
Dear All,
Thanks for the inputs.I did some search myself and found this.
Quote:
'PX Deq: Execute Reply' is considered as 'Idle' by Oracle wait interface, but for session it is not idle - it is waiting.
1) Session is executing a SQL, for which plan is build to parallelize work
2) Session becomes a QC (Query coordinator), which is given certain number of parallel slaves
3) QC instructs PX slaves what kind of work they should perform.
When QC has instructed PX slaves about work, it waits for the reply from slaves - since it has no work to do,but it have do something This something is "sleep and wake up sometimes (when wait timeout is passed)".This kind of wait is called 'PX Deq: Execute Reply'.It is idle for session (since it is not performing significant work), but for query response time it is not idle, since you will receive your results only when the query will be finished.That will happen when all the necessary work will be done.
Quote:
Also forgive me for my earlier post the Degree of PARALLELISM
is 10 for indexes not for table.
I added following hint in my query NOPARALLEL_INDEX
and it runs much faster then previous query.
I think i should alter table indexes with NOPARALLEL and it should work fine.
Regards,
Rajat Ratewal
|
|
|
|
|