RE: Can't get INSERT /*+ APPEND */ to indicate direct load in execution plan.....

Now I'm confused. Why do you think that PARALLEL will work with XMLTYPE?

According to:
http://download.oracle.com/docs/cd/E11882_01/appdev.112/e23094/appjspec.htm#ADXDB3700

(which is the link you sent me):
" No Parallel DML for XMLType - DML operations on XMLType data are always performed in serial. Parallel DML is not supported for XMLType. (Parallel query and DDL are supported for XMLType.)"

That sounds pretty clear to me....

-Mark

-----Original Message-----
From: Radoulov, Dimitre [mailto:cichomitiko_at_gmail.com] Sent: Tuesday, November 01, 2011 4:06 PM To: Bobak, Mark
Cc: oracle-l_at_freelists.org
Subject: Re: Can't get INSERT /*+ APPEND */ to indicate direct load in execution plan.....

Hi Mark,
after reading your post again, I realized I was wrong, sorry.

P->S near the top of the plan output is normal (it's when the final result is send to the QC),
so to recap: in that case (if I'm not missing something again) it seems that, just as you correctly have already noticed, only the append hint was ignored.

So I suppose I'll need to reread the whole document I've previously linked, I mean: it seems that a table with an XMLTYPE column actually *could be* read in parallel ...

Best regards
Dimitre

On 01/11/2011 14:30, Bobak, Mark wrote:
> Thanks Dimitre!
>
> Yes, I do have serial direct load working now, and it appears that the limitation on parallel direct load is due to the XMLTYPE column. I suspected that was the problem, but wasn't seeing a documented restriction in that regard. So, thanks for that!
>
> Guess I'll have to suffer with serial direct load. Come to think of it, I guess I could run multiple, concurrent, serial direct loads. I'll be looking into that next.
>
> -Mark
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Radoulov, Dimitre
> Sent: Monday, October 31, 2011 7:51 PM
> To: oracle-l_at_freelists.org
> Subject: Re: Can't get INSERT /*+ APPEND */ to indicate direct load in execution plan.....
>
> Just to add that I'm trying to clarify the missing parallel execution in the insert part ( P->S ).
> Mark could of course try with append without parallel (or with
> noparallel) to see if direct operations take place.
>
>
>
> Dimitre
>
> On 01/11/2011 00:33, Radoulov, Dimitre wrote:
>> On 31/10/2011 20:09, Bobak, Mark wrote:
>>> Hi all,
>>> I'm trying to get direct load insert working. I'm running 11.2.0.2.0 RAC on Linux x86-64.
>>>
>>> The table is range partitioned, and has one XMLTYPE column with SECUREFILES BINARY XML storage.
>>>
>> [...]
>>
>> I found these:
>>
>> bit.ly/tJlCBT
>>
>> No Parallel DML for XMLType -- DML operations on |XMLType| data are
>> always performed in serial. Parallel DML is not supported for
>> |XMLType|. (Parallel query and DDL are supported for |XMLType|.)
>>
>>
>> I didn't read the whole document,
>> but it seems that one could use parallel DML with the XMLIndexes:
>>
>> bit.ly/sIUkH0
>>
>> You can use a |PARALLEL| clause (with optional degree) when creating
>> or altering an |XMLIndex| index to ensure that index creation and
>> maintenance are carried out in parallel. If the base table is
>> partitioned or enabled for parallelism, then this can improve the
>> performance for both DML operations (|INSERT|, |UPDATE|, |DELETE|) and
>> index DDL operations (|CREATE|, |ALTER|, |REBUILD|).
>>
>>
>> And this one close as "Not a bug":
>>
>> Bug 6453331: INSERT INTO TABLE WITH COLUMN OF XMLTYPE IS NOT USING
>> PARALLEL
>>
>> Status 92 - Closed, Not a Bug
>>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>

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