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