Enable Parallel DML or Not?
Date: Wed, 7 Oct 2009 08:20:13 -0700 (PDT)
Message-ID: <686086.12366.qm_at_web32003.mail.mud.yahoo.com>
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
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Oct 07 2009 - 10:20:13 CDT