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

  • From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
  • To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 31 Oct 2011 15:09:25 -0400

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")
(PARTITION "DATASTAR_XML_DATA_01"  VALUES LESS THAN ('D002') SEGMENT CREATION 
IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
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" ,
PARTITION "DATASTAR_XML_DATA_02"  VALUES LESS THAN ('D003') SEGMENT CREATION 
IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
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" ,
PARTITION "DATASTAR_XML_DATA_03"  VALUES LESS THAN ('D004') SEGMENT CREATION 
IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
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" ,
PARTITION "DATASTAR_XML_DATA_04"  VALUES LESS THAN ('D005') SEGMENT CREATION 
IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
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" ,
PARTITION "DATASTAR_XML_DATA_05"  VALUES LESS THAN ('D006') SEGMENT CREATION 
IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645y more
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE 
DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "MSTMFG_DATA" ,
PARTITION "DATASTAR_XML_DATA_06"  VALUES LESS THAN ('D007') SEGMENT CREATION 
IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
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" ,
PARTITION "DATASTAR_XML_DATA_07"  VALUES LESS THAN ('D008') SEGMENT CREATION 
IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
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" ,
PARTITION "DATASTAR_XML_DATA_08"  VALUES LESS THAN ('D009') SEGMENT CREATION 
IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
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" ,
PARTITION "DATASTAR_XML_DATA_09"  VALUES LESS THAN ('D010') SEGMENT CREATION 
IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
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" ,
PARTITION "DATASTAR_XML_DATA_10"  VALUES LESS THAN ('D011') SEGMENT CREATION 
IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
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" ,
PARTITION "DATASTAR_XML_DATA_11"  VALUES LESS THAN ('D012') SEGMENT CREATION 
IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
..<many more 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

Other related posts:

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