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

  • From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
  • To: "jonathan@xxxxxxxxxxxxxxxxxx" <jonathan@xxxxxxxxxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 1 Nov 2011 02:20:28 -0400

Jonathan,

To be clear, I did explicitly disable the FK, but after I printed the DDL, so, 
sorry for that.

I do have it working now, but I'm not entirely sure why.  I moved my test from 
the preprod to the test environment, and at some point, I'll need to work out 
why it's not working in preprod.

As to your comment about the PK, I assume you were  asking if it was enforced 
by non-unique *index*.  And yes, it was, so, after disabling constraint, I had 
to mark the index as UNUSABLE.

But, even if I had *not* disabled the PK or invalidated the index, I think 
direct load *should* still work.  Performance would suffer, as index inserts 
would log, as they do on an ordinary insert, but, table load would still be 
direct load, would it not?

-Mark


-----Original Message-----
From: jonathan@xxxxxxxxxxxxxxxxxx [mailto:jonathan@xxxxxxxxxxxxxxxxxx] 
Sent: Tuesday, November 01, 2011 1:56 AM
To: Bobak, Mark; oracle-l@xxxxxxxxxxxxx
Subject: Re: Can't get INSERT /*+ APPEND */ to indicate direct load in 
execution plan.....


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