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

  • From: jonathan@xxxxxxxxxxxxxxxxxx
  • To: Mark.Bobak@xxxxxxxxxxxx ,oracle-l@xxxxxxxxxxxxx
  • Date: Tue, 01 Nov 2011 05:56:26 +0000

Mark,

Is your primary key protected by a non-unique constraint ? That would disable 
direct path load.  Also, although you say you have no enabled FKs, the output 
you've shown does have an FK in ENABLE NOVALIDATE mode.

Regards
Jonathan Lewis


Mark.Bobak@xxxxxxxxxxxx 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 SECUREFIL=
> ES BINARY XML storage.
> 
> I have checked that there are no global indexes, no enabled check or refe=
> rential 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_CAC=
> HE 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 CREA=
> TION 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_CAC=
> HE DEFAULT CELL_FLASH_CACHE DEFAULT)
> TABLESPACE "MSTMFG_DATA" ,
> PARTITION "DATASTAR_XML_DATA_02"  VALUES LESS THAN ('D003') SEGMENT CREAT=
> ION 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_CAC=
> HE DEFAULT CELL_FLASH_CACHE DEFAULT)
> TABLESPACE "MSTMFG_DATA" ,
> PARTITION "DATASTAR_XML_DATA_03"  VALUES LESS THAN ('D004') SEGMENT CREAT=
> ION 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_CAC=
> HE DEFAULT CELL_FLASH_CACHE DEFAULT)
> TABLESPACE "MSTMFG_DATA" ,
> PARTITION "DATASTAR_XML_DATA_04"  VALUES LESS THAN ('D005') SEGMENT CREAT=
> ION 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_CAC=
> HE DEFAULT CELL_FLASH_CACHE DEFAULT)
> TABLESPACE "MSTMFG_DATA" ,
> PARTITION "DATASTAR_XML_DATA_05"  VALUES LESS THAN ('D006') SEGMENT CREAT=
> ION IMMEDIATE
> PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
> STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645y mo=
> re
> PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CAC=
> HE DEFAULT CELL_FLASH_CACHE DEFAULT)
> TABLESPACE "MSTMFG_DATA" ,
> PARTITION "DATASTAR_XML_DATA_06"  VALUES LESS THAN ('D007') SEGMENT CREAT=
> ION 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_CAC=
> HE DEFAULT CELL_FLASH_CACHE DEFAULT)
> TABLESPACE "MSTMFG_DATA" ,
> PARTITION "DATASTAR_XML_DATA_07"  VALUES LESS THAN ('D008') SEGMENT CREAT=
> ION 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_CAC=
> HE DEFAULT CELL_FLASH_CACHE DEFAULT)
> TABLESPACE "MSTMFG_DATA" ,
> PARTITION "DATASTAR_XML_DATA_08"  VALUES LESS THAN ('D009') SEGMENT CREAT=
> ION 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_CAC=
> HE DEFAULT CELL_FLASH_CACHE DEFAULT)
> TABLESPACE "MSTMFG_DATA" ,
> PARTITION "DATASTAR_XML_DATA_09"  VALUES LESS THAN ('D010') SEGMENT CREAT=
> ION 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_CAC=
> HE DEFAULT CELL_FLASH_CACHE DEFAULT)
> TABLESPACE "MSTMFG_DATA" ,
> PARTITION "DATASTAR_XML_DATA_10"  VALUES LESS THAN ('D011') SEGMENT CREAT=
> ION 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_CAC=
> HE DEFAULT CELL_FLASH_CACHE DEFAULT)
> TABLESPACE "MSTMFG_DATA" ,
> PARTITION "DATASTAR_XML_DATA_11"  VALUES LESS THAN ('D012') SEGMENT CREAT=
> ION 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
> 
> 
> 

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

Other related posts:

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