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

From: "Chitale, Hemant Krishnarao" <Hemant.Chitale@xxxxxx>
To: <Mark.Bobak@xxxxxxxxxxxx>
Date: Tue, 1 Nov 2011 10:34:42 +0800
> I have checked that there are no global indexes, no enabled check or 
> referential integrity constraints, and no triggers

There does appear to be a Referential Integrity constraint definition 
CONSTRAINT "CSXML_CIT_FK01" FOREIGN KEY ("CSXML_GOID") REFERENCES 
"MSTMFG"."CONTENT_ITEMS" ("CIT_ID") ENABLE NOVALIDATE
But it shouldn't matter.

(curiously, you also have the same column as the PK :
CONSTRAINT "CSXML_GOID_PK" PRIMARY KEY ("CSXML_GOID"))


My guess is that the XML column prevents direct path insert ?
XMLTYPE COLUMN "CSXML_DOC" STORE AS SECUREFILE BINARY XML


  
Hemant K Chitale 

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Bobak, Mark
Sent: Tuesday, November 01, 2011 3:09 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Can't get INSERT /*+ APPEND */ to indicate direct load in execution 
plan.....

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 have checked that there are no global indexes, no enabled check or 
referential integrity constraints, and no triggers.

The table being selected from and the table being inserted into have the same 
layout, partition count etc.


I imagine there's a limitation on direct load that I'm running into, but I 
can't see what it is.

Here's the DML of the table being loaded:
CREATE TABLE "TEST"
(       "CSXML_GOID" NUMBER(15,0) NOT NULL ENABLE,
"CSXML_DOC" "SYS"."XMLTYPE"  NOT NULL ENABLE,
"CSXML_DATASET" VARCHAR2(1) NOT NULL ENABLE,
"CSXML_PART_DATASET" VARCHAR2(15) NOT NULL ENABLE,
"CSXML_DATE_CREATED" TIMESTAMP (6) NOT NULL ENABLE,
"CSXML_USER_CREATED" VARCHAR2(30) NOT NULL ENABLE,
"CSXML_DATE_MODIFIED" TIMESTAMP (6),
"CSXML_USER_MODIFIED" VARCHAR2(30),
"CSXML_VERSION" VARCHAR2(10),
"CSXML_PUB_TITLE" VARCHAR2(4000),
CONSTRAINT "CSXML_GOID_PK" PRIMARY KEY ("CSXML_GOID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE 
DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "MSTMFG_DATA"  ENABLE,
CONSTRAINT "CSXML_CIT_FK01" FOREIGN KEY ("CSXML_GOID")
REFERENCES "MSTMFG"."CONTENT_ITEMS" ("CIT_ID") ENABLE NOVALIDATE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOLOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "MSTMFG_DATA"
XMLTYPE COLUMN "CSXML_DOC" STORE AS SECUREFILE BINARY XML (
ENABLE STORAGE IN ROW CHUNK 8192
NOCACHE NOLOGGING  NOCOMPRESS  KEEP_DUPLICATES
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ALLOW 
NONSCHEMA DISALLOW ANYSCHEMA
PARTITION BY RANGE ("CSXML_PART_DATASET")
..<ALL partition definitions omitted here>....

Script used to (attempt) direct load insert:
alter session enable parallel dml;
insert /*+ append parallel(a 8) */ into test a select /*+ parallel(b 8) */ * 
from cs_content_xml_data b where rownum <1000001;

Here's the execution plan from the statement above:
Plan hash value: 2146972292

---------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name                | Rows  | Bytes | Cost 
(%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |                     |  1000K|   141M|  6671K 
 (1)| 22:14:13 |       |       |        |      |            |
|   1 |  LOAD TABLE CONVENTIONAL | TEST                |       |       |        
    |          |       |       |        |      |            |
|*  2 |   COUNT STOPKEY          |                     |       |       |        
    |          |       |       |        |      |            |
|   3 |    PX COORDINATOR        |                     |       |       |        
    |          |       |       |        |      |            |
|   4 |     PX SEND QC (RANDOM)  | :TQ10000            |   860M|   118G|  6671K 
 (1)| 22:14:13 |       |       |  Q1,00 | P->S | QC (RAND)  |
|*  5 |      COUNT STOPKEY       |                     |       |       |        
    |          |       |       |  Q1,00 | PCWC |            |
|   6 |       PX BLOCK ITERATOR  |                     |   860M|   118G|  6671K 
 (1)| 22:14:13 |     1 |   183 |  Q1,00 | PCWC |            |
|   7 |        TABLE ACCESS FULL | CS_CONTENT_XML_DATA |   860M|   118G|  6671K 
 (1)| 22:14:13 |     1 |   183 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------------------------------

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

   2 - filter(ROWNUM<1000001)
   5 - filter(ROWNUM<1000001)

20 rows selected.


Anyone have any thoughts or ideas as to why direct load isn't working?

Thanks!

-Mark

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



This email and any attachments are confidential and may also be privileged.  If 
you are not the addressee, do not disclose, copy, circulate or in any other way 
use or rely on the information contained in this email or any attachments.  If 
received in error, notify the sender immediately and delete this email and any 
attachments from your system.  Emails cannot be guaranteed to be secure or 
error free as the message and any attachments could be intercepted, corrupted, 
lost, delayed, incomplete or amended.  Standard Chartered PLC and its 
subsidiaries do not accept liability for damage caused by this email or any 
attachments and may monitor email traffic.

Standard Chartered PLC is incorporated in England with limited liability under 
company number 966425 and has its registered office at 1 Aldermanbury Square, 
London, EC2V 7SB.

Standard Chartered Bank ("SCB") is incorporated in England with limited 
liability by Royal Charter 1853, under reference ZC18.  The Principal Office of 
SCB is situated in England at 1 Aldermanbury Square, London EC2V 7SB. In the 
United Kingdom, SCB is authorised and regulated by the Financial Services 
Authority under FSA register number 114276.

If you are receiving this email from SCB outside the UK, please click 
http://www.standardchartered.com/global/email_disclaimer.html to refer to the 
information on other jurisdictions.
--
//www.freelists.org/webpage/oracle-l
References:
- Can't get INSERT /*+ APPEND */ to indicate direct load in execution plan.....
  - From: Bobak, Mark
RE: Can't get INSERT /*+ APPEND */ to indicate direct load in execution plan.....

Other related posts:

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