Re: Enable Parallel DML or Not?

> 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?

from Manual http://download.oracle.com/docs/cd/B28359_01/server.111/b28313/usingpe.htm#CACFJJGG

The session's PARALLEL DML mode does not influence the parallelism of SELECT statements, DDL statements, and the query portions of DML statements.

An other argument (though I can't prove it as the 7.3 mauals are no longer on-line) - the parallel CTAS is there around from 7.3 the ENABLE PARALLEL DML was introduced first in 8 or 8i. The actual degree of the parallelism of the create table and select depends on several factors and can be limited or suppressed completely.

Regards,

Jaromir

• Original Message ----- From: Kellyn Pedersen To: oracle Freelists Sent: Wednesday, October 07, 2009 5:20 PM 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 

--
http://www.freelists.org/webpage/oracle-l