Can't get INSERT /*+ APPEND */ to indicate direct load in execution plan.....
Date: Mon, 31 Oct 2011 15:09:25 -0400
Message-ID: <6AFC12B9BFCDEA45B7274C534738067F7B6E3C01_at_AAPQMAILBX02V.proque.st>
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
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Oct 31 2011 - 14:09:25 CDT