Re: Can't get INSERT /*+ APPEND */ to indicate direct load in execution plan.....
Date: Tue, 01 Nov 2011 22:45:57 +0100
Message-ID: <4EB06895.7080801_at_gmail.com>
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_at_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(_at_"SEL$1")
OUTLINE_LEAF(_at_"INS$1")
FULL(_at_"INS$1" "EMP_1"@"INS$1")
FULL(_at_"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_at_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(_at_"SEL$1")
OUTLINE_LEAF(_at_"INS$1")
FULL(_at_"INS$1" "EMP_1"@"INS$1")
FULL(_at_"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_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-lReceived on Tue Nov 01 2011 - 16:45:57 CDT