Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Samby.

Asked: November 06, 2015 - 6:11 am UTC

Last updated: August 20, 2018 - 4:03 am UTC

Version: 11

Viewed 10K+ times! This question is

You Asked

Hi tom,

While inserting record in a table when we use append hints, it going for direct path load but when use parallel hints it is going for conventional path load.Is it correct?

While insert use should always use Append hints not parallel hints for mass load.

We have already enable parallel DML.

CREATE TABLE C ( X INT );

INSERT /*+ APPEND */ INTO C SELECT 1 FROM DUAL ;
1 rows inserted.


select * from c;

cannot read/modify an object after modifying it in parallel
12838. 00000 - "cannot read/modify an object after modifying it in parallel"
*Cause: Within the same transaction, an attempt was made to add read or
modification statements on a table after it had been modified in parallel
or with direct load. This is not permitted.

Means Direct path load.



Rollback;

insert /*+ PARALLEL */ into C SELECT 1 FROM DUAL ;

select * from c
output : 1;

Even when i checked explain plan as well,with append hints it show direct path load. With parallel hints it showing Conventioan path.

Could you please let us know is it correct ??

1 rows inserted.


and Connor said...

You need to using realistic sizes. Oracle will see 'dual' and most probably not bother with parallel for such a tiny table. Here's an example showing that parallel will automatically infer direct load.

SQL> create table t1
  2  as
  3  select * from dba_objects where 1=0;

Table created.

SQL> create table t2
  2  as
  3  select * from dba_objects;

Table created.

SQL> exec dbms_stats.gather_table_stats(null, 't2')

PL/SQL procedure successfully completed.

SQL> alter session enable parallel dml;

Session altered.

SQL> select value from
  2           v$mystat s
  3         , v$statname n
  4   where
  5           n.name = 'DML statements parallelized'
  6   and     s.statistic# = n.statistic#;

     VALUE
----------
         0

SQL> insert /*+  parallel(t1) */ into t1 select * from t2;

94344 rows created.

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dnt4980j25ng1, child number 0
-------------------------------------
insert /*+  parallel(t1) */ into t1 select * from t2

Plan hash value: 809577413

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                   |          |       |       |   441 (100)|          |        |      |            |
|   1 |  PX COORDINATOR                    |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)              | :TQ10001 | 94344 |    10M|   441   (1)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT (HYBRID TSM/HWMB)|          |       |       |            |          |  Q1,01 | PCWP |            |
|   4 |     OPTIMIZER STATISTICS GATHERING |          | 94344 |    10M|   441   (1)| 00:00:01 |  Q1,01 | PCWP |            |
|   5 |      PX RECEIVE                    |          | 94344 |    10M|   441   (1)| 00:00:01 |  Q1,01 | PCWP |            |
|   6 |       PX SEND ROUND-ROBIN          | :TQ10000 | 94344 |    10M|   441   (1)| 00:00:01 |  Q1,00 | S->P | RND-ROBIN  |
|   7 |        PX SELECTOR                 |          |       |       |            |          |  Q1,00 | SCWC |            |
|   8 |         TABLE ACCESS FULL          | T2       | 94344 |    10M|   441   (1)| 00:00:01 |  Q1,00 | SCWP |            |
----------------------------------------------------------------------------------------------------------------------------

Note
-----
   - Degree of Parallelism is 8 because of table property


SQL> select value from
  2           v$mystat s
  3         , v$statname n
  4   where
  5           n.name = 'DML statements parallelized'
  6   and     s.statistic# = n.statistic#;

     VALUE
----------
         1

SQL> select count(*) from t1;
select count(*) from t1
                     *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


SQL>
SQL>


Rating

  (14 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Hi...

Samby, November 06, 2015 - 10:40 am UTC

Hi , I have run the same scripts but i am getting the count after parallel insert.

create table a
AS
select * from all_objects where 1 = 2 ;

create table b
AS
select * from all_objects;

alter session enable parallel dml;


insert /*+ parallel(t1) */ into a select * from b;
69,161 rows inserted.

SELECT COUNT(*) FROM a;

Output :
COUNT(*)
69161

Oracle version : Exadata.




Connor McDonald
November 06, 2015 - 11:14 am UTC

What do you get for an explain plan for the:

insert /*+ parallel(t1) */ into a select * from b;

Explain Plan

Samby, November 06, 2015 - 11:22 am UTC

Hi , I don't have access of V$session, so not able to get the exact plan

But by using SQl developer tool i got below plan :

OPERATION OBJECT_NAME OPTIONS COST


INSERT STATEMENT
92


LOAD TABLE CONVENTIONAL
A


TABLE ACCESS
B STORAGE FULL 92


Connor McDonald
November 07, 2015 - 5:19 am UTC

Load conventional definitely means non direct.

I would try with a larger size. Exadata costings may be different.

But you should be able to run my script (and omit the v$mystat etc) query and get a full execution plan

A reader, November 08, 2015 - 1:04 pm UTC

Hi,

Samby the last parallel hint in you query is wrong.
You have to hint with a not t1.

Graetings

Explain plan

Samby, November 13, 2015 - 8:48 am UTC

Hello,

Please find the explain plan :

EXPLAIN PLAN FOR
insert /*+ parallel(a) */ into a select * from b;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);



Plan hash value: 1911541843

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 69161 | 6618K| 92 (2)| 00:00:03 |
| 1 | LOAD TABLE CONVENTIONAL | A | | | | |
| 2 | TABLE ACCESS STORAGE FULL| B | 69161 | 6618K| 92 (2)| 00:00:03 |
Connor McDonald
November 13, 2015 - 12:19 pm UTC

Check out this blog

http://www.centroid.com/knowledgebase/blog/exadata-smart-scan-and-parallel-query

You can see that parallel query is definitely possible, even with storage access.

Try a bigger set of rows, or something is wrong in the setup here in terms of parallel settings, or session enablement.

Indexes and PKs and FKs and direct path

A reader, November 13, 2015 - 3:01 pm UTC

If a Table is being inserted using direct path.

Will the direct path fail ( not be used )

1) Because it has FKs ( Just like Truncate fails )

2) Will it fail( or not be used) because of indexes on stand alone columns including the PKs ?


2a)If it works what is the affect on the indexes since all data is written above HWM. Will it have gaps, holes ?
Chris Saxon
November 14, 2015 - 12:22 am UTC

Check the notes in the execution plan..

SQL> create table PAR ( p int primary key );

Table created.

SQL> create table CHD ( c int primary key, p int references PAR(p));

Table created.

SQL>
SQL> insert into PAR values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> explain plan for
  2  insert /*+ APPEND */ into CHD
  3  select rownum, 1
  4  from
  5   ( select 1 from dual connect by level <= 1000 ),
  6   ( select 1 from dual connect by level <= 1000 );

SQL> SELECT * from table(dbms_xplan.display())


PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
Plan hash value: 4088680292

----------------------------------------------------------------------------------
| Id  | Operation                         | Name | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                  |      |     1 |     4   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL          | CHD  |       |            |          |
|   2 |   COUNT                           |      |       |            |          |
|   3 |    MERGE JOIN CARTESIAN           |      |     1 |     4   (0)| 00:00:01 |
|   4 |     VIEW                          |      |     1 |     2   (0)| 00:00:01 |
|*  5 |      CONNECT BY WITHOUT FILTERING |      |       |            |          |
|   6 |       FAST DUAL                   |      |     1 |     2   (0)| 00:00:01 |
|   7 |     BUFFER SORT                   |      |     1 |     4   (0)| 00:00:01 |
|   8 |      VIEW                         |      |     1 |     2   (0)| 00:00:01 |
|*  9 |       CONNECT BY WITHOUT FILTERING|      |       |            |          |
|  10 |        FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------

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

   5 - filter(LEVEL<=1000)
   9 - filter(LEVEL<=1000)

Note
-----
   - Direct Load disabled because parent referential constraints are present

27 rows selected.

SQL>


No FK's will disable it.

If there is no FK, then you'll see the LOAD AS SELECT

SQL> drop table PAR cascade constraints purge;

Table dropped.

(then repeat above)

-----------------------------------------------------------------------------------
| Id  | Operation                          | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                   |      |     1 |     4   (0)| 00:00:01 |
|   1 |  LOAD AS SELECT                    | CHD  |       |            |          |
|   2 |   OPTIMIZER STATISTICS GATHERING   |      |     1 |     4   (0)| 00:00:01 |
|   3 |    COUNT                           |      |       |            |          |
|   4 |     MERGE JOIN CARTESIAN           |      |     1 |     4   (0)| 00:00:01 |
|   5 |      VIEW                          |      |     1 |     2   (0)| 00:00:01 |
|*  6 |       CONNECT BY WITHOUT FILTERING |      |       |            |          |
|   7 |        FAST DUAL                   |      |     1 |     2   (0)| 00:00:01 |
|   8 |      BUFFER SORT                   |      |     1 |     4   (0)| 00:00:01 |
|   9 |       VIEW                         |      |     1 |     2   (0)| 00:00:01 |
|* 10 |        CONNECT BY WITHOUT FILTERING|      |       |            |          |
|  11 |         FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------


and then if I actually run the code

SQL> insert /*+ APPEND */ into CHD
  2  select rownum, 1
  3  from
  4   ( select 1 from dual connect by level <= 1000 ),
  5   ( select 1 from dual connect by level <= 1000 );

1000000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select index_name, status
  2  from user_indexes
  3  where table_name = 'CHD';

INDEX_NAME                     STATUS
------------------------------ --------
SYS_C0010605                   VALID


you can see the index remains valid. (Obviously the direct path load will run *best* with no indexes, but it still works with them there)

any clue about database version here ?

Rajeshwaran, Jeyabal, November 15, 2015 - 1:29 pm UTC

Connor,

I dont see this note appearing in 11g, but seem to be available in 12c (12.1.0.2)

Note
-----
   - Direct Load disabled because parent referential constraints are present


It would be really helpful, if you could provide some clue about the database version on which you were running your scripts.

since this question was raised on 11g database, I was assuming you are using 11g database to run the scripts, but that seems to be not.

Parallel DML works with even a single row

Hemant K Chiale, January 14, 2016 - 3:15 pm UTC

I doubt if the original question had properly enabled parallel dml.
Parallel DML works with even a single row.

[oracle@localhost ~]$ sqlplus hemant/hemant

SQL*Plus: Release 11.2.0.2.0 Production on Thu Jan 14 23:08:53 2016

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from dual;

D
-
X

SQL> desc dual            
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DUMMY                                              VARCHAR2(1)

SQL> create table hkc_t_d (dummy varchar2(1));

Table created.

SQL> insert /*+ PARALLEL */ into hkc_t_d select * from dual
  2  /

1 row created.

SQL> select * from hkc_t_d;

D
-
X

SQL> truncate table hkc_t_d;

Table truncated.

SQL> alter session enable parallel dml;

Session altered.

SQL> insert /*+ PARALLEL */ into hkc_t_d select * from dual
  2  /

1 row created.

SQL> select * from hkc_t_d;
select * from hkc_t_d
              *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


SQL>


So it doesn't matter how many rows you've inserted.

SQL> set pages600
SQL> commit;

Commit complete.

SQL> explain plan for
  2  insert /*+ PARALLEL */ into hkc_t_d select * from dual;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3974846775

--------------------------------------------------------------------------------
---------------------------------

| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time
   |    TQ  |IN-OUT| PQ Distrib |

--------------------------------------------------------------------------------
---------------------------------

|   0 | INSERT STATEMENT        |          |     1 |     2 |     2   (0)| 00:00:
01 |        |      |            |

|   1 |  PX COORDINATOR         |          |       |       |            |
   |        |      |            |

|   2 |   PX SEND QC (RANDOM)   | :TQ10001 |     1 |     2 |     2   (0)| 00:00:
01 |  Q1,01 | P->S | QC (RAND)  |

|   3 |    LOAD AS SELECT       | HKC_T_D  |       |       |            |
   |  Q1,01 | PCWP |            |

|   4 |     PX RECEIVE          |          |     1 |     2 |     2   (0)| 00:00:
01 |  Q1,01 | PCWP |            |

|   5 |      PX SEND ROUND-ROBIN| :TQ10000 |     1 |     2 |     2   (0)| 00:00:
01 |        | S->P | RND-ROBIN  |

|   6 |       TABLE ACCESS FULL | DUAL     |     1 |     2 |     2   (0)| 00:00:
01 |        |      |            |

--------------------------------------------------------------------------------
---------------------------------


Note
-----
   - automatic DOP: skipped because of IO calibrate statistics are missing

17 rows selected.

SQL>


The explain plan shows that it is a parallel insert and not a conventional load.
Chris Saxon
January 15, 2016 - 1:52 am UTC

Thanks for the input.

How to identify direct-path insert from the execution plan?

Mike, September 12, 2016 - 8:03 pm UTC

I have interpreted the following operations in an execution plan as designating direct-path inserts:
LOAD AS SELECT
DIRECT LOAD INTO (on a multi-table insert)

while the following indicate a conventional insert:
INSERT STATEMENT (without the above)
LOAD TABLE CONVENTIONAL
INTO (on a multi-table insert)

But the above demonstrates a direct-path insert (demonstrated by the ORA-12838) even though the operation is INSERT STATEMENT (without any of the above).

It seems my criteria is missing the pattern of parallel direct-path insert. What is the definitive sign for that?
Connor McDonald
September 13, 2016 - 2:08 am UTC

A parallel insert will be direct.

Recognizing parallel direct path insert from execution plan

Mike, September 13, 2016 - 5:34 pm UTC

So does a PX COORDINATOR directly feeding an INSERT indicate a parallel insert?
Chris Saxon
September 14, 2016 - 12:58 am UTC

Yes, and you would still see load-as-select for the direct part:

SQL> create table t as select * from dba_Objects;

Table created.

SQL> alter session enable parallel dml;

Session altered.

SQL> explain plan for insert /*+ parallel */ into t select * from t;

Explained.

SQL> @explain

---------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT      |          |   346K|    68M|    98   (0)| 00:00:02 |        |      |         |
|   1 |  PX COORDINATOR       |          |       |       |            |          |        |      |         |
|   2 |   PX SEND QC (RANDOM) | :TQ10000 |   346K|    68M|    98   (0)| 00:00:02 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT     | T        |       |       |            |          |  Q1,00 | PCWP |         |
|   4 |     PX BLOCK ITERATOR |          |   346K|    68M|    98   (0)| 00:00:02 |  Q1,00 | PCWC |         |
|   5 |      TABLE ACCESS FULL| T        |   346K|    68M|    98   (0)| 00:00:02 |  Q1,00 | PCWP |         |
---------------------------------------------------------------------------------------------------------------


Why am I not getting a direct-path insert?

Mike, September 14, 2016 - 10:35 am UTC

Hmmm, there is no LOAD AS SELECT operation in my plan.
And, if I alter the one table that is PARALLEL to be NOPARALLEL, the parallel operations go away and I still see no operations suggesting a direct-path insert.

So I am trying to figure out why we are not getting a direct-path insert.... reviewing http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9014.htm#i2163698 for the restrictions (this is on 10.2.0.5 - sigh)

- The statement does specify "INSERT /*+ APPEND */"
- The target table is not index-organized
- The target table is not part of a cluster
- The target table has no object-type columns (only NVARCHAR2, NUMBER, DATE, and VARCHAR2)
- There are no triggers on the target table
- The target table has 5 disabled referential-integrity constraints on it. I tried dropping these but still no luck.
- The target table is not replicated (I presume this means no materialized views use it)
- The transaction is not distributed

I have collected a 10053 trace and I see no indication that direct-path is considered. (I also looked at a 10053 trace for a insert that successfully used direct-path, and I saw nothing in that trace either - so it seems that evaluation of direct-path eligibility is outside the scope of 10053.)

Any tips for diagnosing this?

Why am I not getting a direct-path insert?

Mike, September 14, 2016 - 6:52 pm UTC

I figured it out...
The diagnostic technique is: tear the whole thing down, start simple, and add things back in until the behavior changes.

So...
With only the table (no indexes or constraints) I got direct path
Adding an index - still direct path
Adding another index - still direct path
Adding the primary key - changed to conventional

Because (probably by accident) the index supporting the primary key had been defined as a non-unique index.

If I used a unique index to support the primary key, I got a direct path insert.
With the non-unique index, I only got direct-path if the PK constraint was disabled.
Chris Saxon
September 17, 2016 - 2:01 am UTC

nice detective work.

And thanks for sharing the findings so that others will benefit.

Direct path load on partitioned table single partition

A reader, July 16, 2018 - 5:59 pm UTC

Doc says "Enabled referential and check constraints on the table that the partition is a member of are not allowed."

Does this include Not null constraints?

Q2: does insert append into table for partition p
Lock the table ?
Connor McDonald
July 17, 2018 - 9:23 am UTC

not null is ok, and locking is at partition level if possible, 

--
-- session 1
--
SQL> create table t
  2  partition by list ( x )
  3  ( partition p1 values (1),
  4    partition p2 values (2)
  5  )
  6  as select 1 x, d.* from dba_objects d
  7  where 1=0;

Table created.

SQL>
SQL> alter table t modify x not null;

Table altered.

SQL> alter table t modify object_id not null;

Table altered.

SQL>
SQL> insert /*+ APPEND */ into t partition for ( 1 )
  2  select 1 x, d.* from dba_objects d
  3  where object_id is not null;

78477 rows created.

--
-- session 2
--
SQL> select * from t partition for ( 2 ) ;

no rows selected

SQL> insert /*+ APPEND */ into t partition for ( 2 )
  2  select 2 x, d.* from dba_objects d
  3  where object_id is not null;

78480 rows created.


Via java or hibernate

A reader, August 09, 2018 - 5:26 am UTC

Do you have an example where could one direct path ie within append_values in java mutithreads and pool connections but avoiding the ora-12838?


Connor McDonald
August 20, 2018 - 4:03 am UTC

Perhaps these are conflicting goals here.

A direct path operation locks the object impacted, so by definition, you can only have a single direct path operation in play at one time. I'm using the term "single" here loosely, because if you requested the operation to be done in parallel (at the database tier) then this works because the database is controlling the entire coordination.

But it would never work being issue from multiple threads from say Java, or multiple connections from a connection pool, because one of the threads would get the lock and the others would immediately fail.

on direct path load with PK supported by non-unique index

Rajeshwaran, Jeyabal, August 20, 2018 - 3:29 pm UTC

....
Because (probably by accident) the index supporting the primary key had been defined as a non-unique index.
....

atleast that got lifted in 11g and above.
demo@ORA11G> create table t1 as select * from all_objects where 1 = 0;

Table created.

demo@ORA11G> create table t2 as select * from all_objects;

Table created.

demo@ORA11G> exec dbms_stats.gather_table_stats(user,'T2');

PL/SQL procedure successfully completed.

demo@ORA11G> create index t1_idx1 on t1(data_object_id);

Index created.

demo@ORA11G> create index t1_idx2 on t1(created);

Index created.

demo@ORA11G> alter table t1 add constraint t1_pk
  2  primary key( object_id )
  3  using index ( create index t1_idx3 on t1(object_id) );

Table altered.

demo@ORA11G> alter session enable parallel dml;

Session altered.

demo@ORA11G> insert /*+ append */ into t1 select * from t2;

84681 rows created.

demo@ORA11G> select count(*) from t1;
select count(*) from t1
                     *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


demo@ORA11G> select index_name, uniqueness
  2  from user_indexes
  3  where table_name ='T1' ;

INDEX_NAME UNIQUENES
---------- ---------
T1_IDX3    NONUNIQUE
T1_IDX2    NONUNIQUE
T1_IDX1    NONUNIQUE

demo@ORA11G>

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library