RE: Parallel slaves blocking each other during CTAS with Parallel Clause
Date: Tue, 17 Dec 2013 19:33:31 +0000
Message-ID: <1E24812FBE5611419EFAFC488D7CCDD1187B8427_at_G5W2716.americas.hpqcorp.net>
I did not have time to read the entire article but in case it is not mentioned use of too high a degree of parallelism can lead to heavy pqo waits.
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Prabhu Krishnaswamy
Sent: Monday, December 16, 2013 6:04 PM
To: mwf_at_rsiz.com; oratune_at_yahoo.com; oracle-l_at_freelists.org
Subject: RE: Parallel slaves blocking each other during CTAS with Parallel Clause
Thanks All..
The version of the oracle is 11.2.0.3. I found the below article which explains about the root cause of blocking sessions during the parallel processes
Thanks.
From: mwf_at_rsiz.com<mailto:mwf_at_rsiz.com> To: oratune_at_yahoo.com<mailto:oratune_at_yahoo.com>; prabhu_adam_at_hotmail.com<mailto:prabhu_adam_at_hotmail.com>; oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> Subject: RE: Parallel slaves blocking each other during CTAS with Parallel Clause Date: Thu, 12 Dec 2013 14:42:14 -0500
+1 to what David wrote.
Also, perhaps you tell us exactly what you mean by a "temp. table" and any indexes and partitioning that may be involved.
Perhaps the CTAS statement might be helpful as well.
A little description of "parallel slave sessions are blocking each other" would also be helpful, for example, is exactly one proceeding at a time or is the blocking more complex?
mwf
From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of David Fitzjarrell Sent: Thursday, December 12, 2013 2:14 PM To: prabhu_adam_at_hotmail.com<mailto:prabhu_adam_at_hotmail.com>; oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> Subject: Re: Parallel slaves blocking each other during CTAS with Parallel Clause
We need to know which release of Oracle you're using.
David Fitzjarrell
On Thursday, December 12, 2013 11:11 AM, Prabhu Krishnaswamy <prabhu_adam_at_hotmail.com<mailto:prabhu_adam_at_hotmail.com>> wrote: Lists,
When we're creating a table using CTAS with parallel clause, we observed the parallel slave sessions are blocking each other. We are using this temp. table for the report purpose and before querying the temp table, it will be created with the required data.
We are not sure during which phase its blocking each other. Is this the expected behavior or any way to avoid this lock.
Highly appreciate any insight on this.
Thanks.
Prabhu
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Dec 17 2013 - 20:33:31 CET