Striving for Optimal Performance
  • Blog
    • Archive
    • Categories
    • Search
  • Troubleshooting Oracle Performance
    • Description
    • Structure
    • Table of Contents
    • Forewords
    • Reviews
    • Downloadable Files
    • Addenda and Errata
    • First Edition
  • Public Appearances
    • Past Public Appearances
  • Contact
  • About

Hints for Direct-path Insert Statements

23 October 2009 13 Comments Written by Christian Antognini

Up to Oracle Database 10g Release 2, direct-path inserts are supported only by INSERT INTO … SELECT … statements (including multitable inserts), MERGE statements (for the part inserting data), and applications using the OCI direct-path interface (for example, the SQL*Loader utility). At the statement level two methods are available to specify that a direct-path insert has to be used:

  • Specify the APPEND hint in the SQL statement
  • Execute the SQL statement (actually, at least the INSERT part) in parallel

Let’s have a look to an example. Notice that:

  • The APPEND hint is used to execute a direct-path insert.
  • The APPEND hint does not work with a “regular” INSERT statement that uses the VALUES clause.
  • To check whether the direct-path insert is performed, the modified table is queried without committing (or rolling back) the transaction. As a result, after a direct-path insert the database engine raises an ORA-12838.
SQL> SELECT * FROM v$version WHERE rownum = 1;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

SQL> CREATE TABLE t (n NUMBER);

SQL> INSERT /*+ append */ INTO t SELECT 1 FROM dual;

SQL> SELECT * FROM t;
SELECT * FROM t
              *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

SQL> COMMIT;

SQL> INSERT /*+ append */ INTO t VALUES (2);

SQL> SELECT * FROM t;

         N
----------
         1
         2

Strangely enough, at least for me, in Oracle Database 11g Release 1 the behavior of the APPEND hint has changed. In fact, it is accepted also for a “regular” INSERT statement that uses the VALUES clause. Let’s run the same test as before to illustrate the new behavior.

SQL> SELECT * FROM v$version WHERE rownum = 1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

SQL> CREATE TABLE t (n NUMBER);

SQL> INSERT /*+ append */ INTO t SELECT 1 FROM dual;

SQL> SELECT * FROM t;
SELECT * FROM t
              *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

SQL> COMMIT;

SQL> INSERT /*+ append */ INTO t VALUES (1);

SQL> SELECT * FROM t;
SELECT * FROM t
              *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

Even more strange, in Oracle Database 11g Release 2 the behavior of the APPEND hint was reverted to the pre-11g one! But, since the feature is really useful in some situations, a new hint called APPEND_VALUES is available. The following example illustrates the new behavior.

SQL> SELECT * FROM v$version WHERE rownum = 1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

SQL> CREATE TABLE t (n NUMBER);

SQL> INSERT /*+ append */ INTO t SELECT 1 FROM dual;

SQL> SELECT * FROM t;
SELECT * FROM t
              *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

SQL> COMMIT;

SQL> INSERT /*+ append */ INTO t VALUES (2);

SQL> SELECT * FROM t;

         N
----------
         1
         2

SQL> COMMIT;

SQL> INSERT /*+ append_values */ INTO t VALUES (3);

SQL> SELECT * FROM t;
SELECT * FROM t
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
10gR1, 10gR2, 11gR1, 11gR2, 9iR2, Direct Path
Interpreting Execution Plans
Zero-Size Unusable Indexes and the Query Optimizer

13 Comments

3 Pings/Trackbacks

  1. Surachart Opun Surachart Opun
    23 October 2009    

    Thank You for good post about direct-path inserts.

    Reply
  2. Alberto Dell'Era Alberto Dell'Era
    25 October 2009    

    Ciao Chris,

    very interesting – it seems to be useful when the arguments to the VALUES clause are actually arrays according to the docs
    APPEND_VALUES Hint
    In fact I can see no reason to insert-append a single row …

    Reply
  3. Timur Akhmadeev Timur Akhmadeev
    26 October 2009    

    Hi Chris,

    see Note 842374.1 for explanation of this behavior.

    Reply
  4. Blogroll Report 16/10/2009-23/10/2009 « Coskan’s Approach to Oracle Blogroll Report 16/10/2009-23/10/2009 « Coskan’s Approach to Oracle
    27 October 2009    

    […] Christian Antognini-Hints for Direct-path Insert Statements […]

  5. Raja Raja
    3 November 2009    

    The exceptions mentioned here for INSERT and APPEND are very useful.

    Please clarify my doubts :
    1. How about using APPEND in other DML Statements like DELETE, UPDATE, MERGE ?
    2. Does PARALLELISM get enabled by default, when APPEND hint is used ?

    Reply
  6. Christian Antognini Christian Antognini
    10 November 2009    

    Hi Raja

    > How about using APPEND in other DML Statements like DELETE, UPDATE, MERGE ?

    The APPEND hint is only supported for INSERT and MERGE statements. With MERGE statements it is specified as follows:

    MERGE /*+ append */ INTO …

    > Does PARALLELISM get enabled by default, when APPEND hint is used ?

    No. But a parallel insert automatically use direct-path to write data into the blocks…

    Cheers,
    Chris

    Reply
  7. Houri Mohamed Houri Mohamed
    3 December 2009    

    Hi Christian,

    Does the dml logging error during insert /*+ append */ select is
    supported by the new oracle release? Particulary for unique and
    primary keys?

    Mohamed

    Reply
  8. Christian Antognini Christian Antognini
    11 December 2009    

    Hi Mohamed

    DML error logging is not yet supported with direct inserts.

    Here is an example:

    SQL> SELECT * FROM v$version WHERE rownum = 1;
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    
    SQL> CREATE TABLE t (n NUMBER, pad VARCHAR2(100), CONSTRAINT t_n_uk UNIQUE (n));
    
    SQL> INSERT INTO t VALUES (1,'bla');
    
    SQL> exec dbms_errlog.create_error_log ('t')
    
    SQL> INSERT /*+ append_values */ INTO t VALUES (1,'bla') LOG ERRORS;
    INSERT /*+ append_values */ INTO t VALUES (1,'bla') LOG ERRORS
    *
    ERROR at line 1:
    ORA-00001: unique constraint (CHA.T_N_UK) violated
    
    SQL> SELECT ora_err_mesg$ FROM err$_t;
    
    no rows selected
    
    SQL> INSERT INTO t VALUES (1,'bla') LOG ERRORS;
    INSERT INTO t VALUES (1,'bla') LOG ERRORS
    *
    ERROR at line 1:
    ORA-00001: unique constraint (CHA.T_N_UK) violated
    
    SQL> SELECT ora_err_mesg$ FROM err$_t;
    
    ORA_ERR_MESG$
    --------------------------------------------------------------------------------
    ORA-00001: unique constraint (CHA.T_N_UK) violated

    Cheers,
    Chris

    Reply
  9. Houri Mohamed Houri Mohamed
    11 December 2009    

    Thanks Christian,
    Always we learn something new.
    Today is : in 11g we can use direct path insert with insert/values using the hint
    /*+ append_values */
    Regards

    Reply
  10. Kerry Osborne Kerry Osborne
    15 December 2009    

    Hi Christian,

    Are you familiar with SYS_DL_CURSOR hint? Do you know if the APPEND_VALUES hint is functionally the same.

    Kerry

    Reply
  11. Christian Antognini Christian Antognini
    17 December 2009    

    Hi Kerry

    Sorry, I never did tests using the SYS_DL_CURSOR hint.

    Cheers,
    Chris

    Reply
  12. Book Review: Troubleshooting Oracle Performance (Part 2) « Charles Hooper's Oracle Notes Book Review: Troubleshooting Oracle Performance (Part 2) « Charles Hooper's Oracle Notes
    8 November 2011    

    […] statements containing a VALUES clause.  Since the time when the book was published, the author created a blog article that demonstrates how to use direct path insert for SQL statements that have a VALUES clause.  […]

  13. Oracle global temp tabs or nologging/append – saved redo in numbers | Bitbach's Blog Oracle global temp tabs or nologging/append – saved redo in numbers | Bitbach's Blog
    21 April 2016    

    […] Lesson 1: You don’t draw gain or loss in performance, whatever constellation being. Lesson 2: There’s always redo, point, at least for the system side operations. Lesson 3: The nolog approach wins, saves 2/2 of the redo, most obviously by selecting truncate (no space reuse for small or medium size tabs) over delete. Whoever knows why the temp approach spends so much redo in the delete step… Lesson 4: Interesting to learn btw, what it takes to see nolog inserts are really direct path: https://antognini.ch/2009/10/hints-for-direct-path-insert-statements. […]

  1. Blogroll Report 16/10/2009-23/10/2009 « Coskan’s Approach to Oracle on 27 October 2009 at 00:55
  2. Book Review: Troubleshooting Oracle Performance (Part 2) « Charles Hooper's Oracle Notes on 8 November 2011 at 15:32
  3. Oracle global temp tabs or nologging/append – saved redo in numbers | Bitbach's Blog on 21 April 2016 at 13:30

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.