RE: Enable Parallel DML or Not?

  • From: Kellyn Pedersen <kjped1313@xxxxxxxxx>
  • To: oracle Freelists <oracle-l@xxxxxxxxxxxxx>, Akhmadeev@xxxxxxxxxxxxxx
  • Date: Thu, 8 Oct 2009 10:22:12 -0700 (PDT)

I've been adding hints to broadcast on one table only and that has taken care 
of some of the hash-hash issues that have occurred in some instances, but most 
of the time, (I used the DDL below as just an example of what most of the code 
looks like here...) this has not been the issue and we still are seeing the 
parallel slaves being distributed only for the select and not for the table 
creation.
 
I was hoping someone else had fully tested to verify that in no way could a 
CTAS fall into the requirement of needing parallel_dml enabled-  just in case 
today is the day that logic has gone out the window-  it's happened before...:)
 
Thanks,
Kellyn Pedersen
Multi-Platform DBA
I-Behavior

--- On Thu, 10/8/09, Timur Akhmadeev <Akhmadeev@xxxxxxxxxxxxxx> wrote:


From: Timur Akhmadeev <Akhmadeev@xxxxxxxxxxxxxx>
Subject: RE: Enable Parallel DML or Not?
To: kjped1313@xxxxxxxxx, "oracle Freelists" <oracle-l@xxxxxxxxxxxxx>
Date: Thursday, October 8, 2009, 6:04 AM








Hi,
 
If by any chance you are using HASH-HASH distribution combined with a hash-join 
(you didn’t provide us execution plan for the query), then you should read 
Jonathan Lewis’s note here: 
http://jonathanlewis.wordpress.com/2008/11/05/px-buffer/
 
One solution to this issue is to try to get rid of HASH-HASH distribution to 
employ broadcasting of one table if possible; another one would be to supply 
Oracle with equi-partitioned tables to use partition-wise join.
 

Thanks,
Timur Akhmadeev




From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Kellyn Pedersen
Sent: Wednesday, October 07, 2009 19:20
To: oracle Freelists
Subject: Enable Parallel DML or Not?
 





I have to say as much as I learn about parallel processing everyday, the more 
questions I have...:)

 

I have numerous CTAS operations that load tables with large parallel degrees in 
both the insert as well as the select, (see example below...)

 

create table member_0_ordersum2 compress pctfree 0 tablespace MARTLARGE 
parallel(degree 16) as
SELECT/*+ parallel (i 16) parallel (s 16)*/ o1.ibid
,recency_key
, ROUND(NVL(AVG(o1.nrt_gap_days), 0), 0) AS adbo
, ROUND(NVL(AVG(o1.onl_gap_days), 0), 0) AS adbo_o...

When I view these through OEM, it is not receiving the parallel on the table 
creation(insert) and is, of course, using a huge amount of temp space for the 
hash/sort processes, so I'm seeing large waits on direct read and writes to my 
temp tablespace group, along with sequential reads on the partitions in 
question, (but I find this to be an actual index from the data dictionary 
causing it to be a sequential read wait, not the actual partition, BTW.)

 

I've read misleading information view the web on CTAS being a parallel_DML 
statement, which I didn't think it was and if it is, would require me to enable 
parallel DML before the create table statement.  

 

QUESTION-  Is this just a mistake by some educated folks out there or are CTAS 
considered a parallel DML statement along with merge, insert, update and delete 
when using parallel?

 

Thanks!

Kellyn Pedersen

Multi-Platform DBA

I-Behavior
 


      

Other related posts: