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

  • From: "Radoulov, Dimitre" <cichomitiko@xxxxxxxxx>
  • To: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
  • Date: Tue, 01 Nov 2011 22:45:57 +0100

Hi Mark,
yes, the documentation is clear.

Actually I should have said:  the append hint was ignored (not *only* 
the append hint was ignored).

I mean that, if I'm reading this correctly, the plan output alone is not 
sufficient to determine if the table TEST
is accessed in parallel or only the table CS_CONTENT_XML_DATA is read in 
parallel.

With a simple test I'm getting the following output:

insert  into   emp_1 select  /*+ parallel */   * from   scott.emp

Plan hash value: 2873591275

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost 
(%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |          |       |       |     2 
(100)|          |        |      |            |
|   1 |  LOAD TABLE CONVENTIONAL |          |       |       |            
|          |        |      |            |
|   2 |   PX COORDINATOR         |          |       |       |            
|          |        |      |            |
|   3 |    PX SEND QC (RANDOM)   | :TQ10000 |    14 |   532 |     2   
(0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR    |          |    14 |   532 |     2   
(0)| 00:00:01 |  Q1,00 | PCWC |            |
|*  5 |      TABLE ACCESS FULL   | EMP      |    14 |   532 |     2   
(0)| 00:00:01 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

    1 - SEL$1
    5 - SEL$1 / EMP@SEL$1

Outline Data
-------------

   /*+
       BEGIN_OUTLINE_DATA
       IGNORE_OPTIM_EMBEDDED_HINTS
       OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
       DB_VERSION('11.2.0.3')
       ALL_ROWS
       NO_PARALLEL
       OUTLINE_LEAF(@"SEL$1")
       OUTLINE_LEAF(@"INS$1")
       FULL(@"INS$1" "EMP_1"@"INS$1")
       FULL(@"SEL$1" "EMP"@"SEL$1")
       END_OUTLINE_DATA
   */

Predicate Information (identified by operation id):
---------------------------------------------------

    5 - access(:Z>=:Z AND :Z<=:Z)

[...]

Note
-----
    - automatic DOP: skipped because of IO calibrate statistics are missing


I believe here the insert in the table emp_1 is in serial mode (note the 
no_parallel in the outline section).
This is the plan with a parallel hint for emp_1 (I see no difference in 
the plan, the outline data section is different though):

insert /*+ parallel(4) */ into   emp_1 select  /*+ parallel(4) */   *
from   scott.emp

Plan hash value: 2873591275

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost 
(%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |          |       |       |     2 
(100)|          |        |      |            |
|   1 |  LOAD TABLE CONVENTIONAL |          |       |       |            
|          |        |      |            |
|   2 |   PX COORDINATOR         |          |       |       |            
|          |        |      |            |
|   3 |    PX SEND QC (RANDOM)   | :TQ10000 |    14 |   532 |     2   
(0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR    |          |    14 |   532 |     2   
(0)| 00:00:01 |  Q1,00 | PCWC |            |
|*  5 |      TABLE ACCESS FULL   | EMP      |    14 |   532 |     2   
(0)| 00:00:01 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

    1 - SEL$1
    5 - SEL$1 / EMP@SEL$1

Outline Data
-------------

   /*+
       BEGIN_OUTLINE_DATA
       IGNORE_OPTIM_EMBEDDED_HINTS
       OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
       DB_VERSION('11.2.0.3')
       ALL_ROWS
       SHARED(4)
       OUTLINE_LEAF(@"SEL$1")
       OUTLINE_LEAF(@"INS$1")
       FULL(@"INS$1" "EMP_1"@"INS$1")
       FULL(@"SEL$1" "EMP"@"SEL$1")
       END_OUTLINE_DATA
   */

Predicate Information (identified by operation id):
---------------------------------------------------

    5 - access(:Z>=:Z AND :Z<=:Z)

[...]

Note
-----
    - Degree of Parallelism is 4 because of hint


Perhaps there is some other way to determine which tables are accessed 
in parallel (using _px_trace etc.).


Dimitre

On 01/11/2011 21:32, Bobak, Mark wrote:
> Hi Dimitre,
>
> 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@xxxxxxxxx]
> Sent: Tuesday, November 01, 2011 4:06 PM
> To: Bobak, Mark
> Cc: oracle-l@xxxxxxxxxxxxx
> 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 
>> On Behalf Of Radoulov, Dimitre
>> Sent: Monday, October 31, 2011 7:51 PM
>> To: oracle-l@xxxxxxxxxxxxx
>> 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
>>>
>>
>> --
>> //www.freelists.org/webpage/oracle-l
>>
>>
>>
>>
>
>

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

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 11-2011