Re: Enable Parallel DML or Not?
- From: Kellyn Pedersen <kjped1313@xxxxxxxxx>
- To: "Jaromir D.B. Nemec" <jaromir@xxxxxxxxxxxx>
- Date: Wed, 7 Oct 2009 15:42:07 -0700 (PDT)
Thank you for the link, but I've gone through every manual I can find,
including this one and I also would consider the CTAS, since it's a create
table, in a DDL category of parallel processing and I have had your theory as
the most likely possibility, but I want to ensure that I'm not missing
something here. I also have read different websites that INCLUDE CTAS in with
standard insert, update, merge and deletes as requiring parallel_dml to be
enabled to get the full benefit of all parallel hints, but these may be on
older versions of Oracle, as I have seen a couple list Oracle 7.3.
Kellyn
--- On Wed, 10/7/09, Jaromir D.B. Nemec <jaromir@xxxxxxxxxxxx> wrote:
From: Jaromir D.B. Nemec <jaromir@xxxxxxxxxxxx>
Subject: Re: Enable Parallel DML or Not?
To: kjped1313@xxxxxxxxx, "oracle Freelists" <oracle-l@xxxxxxxxxxxxx>
Date: Wednesday, October 7, 2009, 4:20 PM
Hi Kellyn,
> 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
Other related posts:
