Home » RDBMS Server » Performance Tuning » PX Deq: Execute Reply (ORACLE 10G,10.2.0.3.0,AIX)
PX Deq: Execute Reply [message #479726] Tue, 19 October 2010 00:34 Go to next message
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 Go to previous messageGo to next message
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 #479737 is a reply to message #479733] Tue, 19 October 2010 01:51 Go to previous messageGo to next message
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
Senior Member
It is an idle event:
select wait_class from v$system_event where event like 'PX Deq: Execute Reply';

so can it not be ignored?
Re: PX Deq: Execute Reply [message #479738 is a reply to message #479737] Tue, 19 October 2010 02:48 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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. Sad


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
Re: PX Deq: Execute Reply [message #479772 is a reply to message #479757] Tue, 19 October 2010 04:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
I did some search myself and found this.

ALWAYS post a link to what you find in order to:
1/ Give the credit to the author
2/ Make us estimate and check the validity of the quote

Regards
Michel
Re: PX Deq: Execute Reply [message #479835 is a reply to message #479772] Tue, 19 October 2010 11:52 Go to previous message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Dear All,

Please find the link which i have searched.

http://forums.oracle.com/forums/thread.jspa?threadID=906940

http://www.slideshare.net/dougburns/introduction-to-parallel-execution#

Regards,
Rajat Ratewal

[Updated on: Tue, 19 October 2010 23:53]

Report message to a moderator

Previous Topic: MV refresh with atomic_refresh false
Next Topic: PCTFREE AND PCTUSED
Goto Forum:
  


Current Time: Fri Nov 22 07:21:34 CST 2024