This discussion is archived
7 Replies Latest reply: Jan 14, 2009 10:04 AM by Randolf Geist RSS

SYS_DL_CURSOR  vs APPEND

639346 Newbie
Currently Being Moderated
Any details on hint SYS_DL_CURSOR ? i.e. what it does and what it’s for?

I see this hint in INSERT statements generated by informatica with bulk load enabled.. I guess it may be using SQL*load or OCI direct path API internally..
INSERT /*+ SYS_DL_CURSOR */ INTO TRAN
("CKEY","LNUM","ACTNBR") VALUES
(NULL,NULL,NULL)
Are these inserts "Direct level insert"as hints Imply? Why do I see NULL in the VALUES clause even though data is there for every row? Is it same as APPEND hint? Oracle documents says INSERT… VALUES can not be DIRECT level INSERTs. So little puzzled.

Thanks
Max.
  • 1. Re: SYS_DL_CURSOR  vs APPEND
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    max71 wrote:
    Any details on hint SYS_DL_CURSOR ? i.e. what it does and what it’s for?

    I see this hint in INSERT statements generated by informatica with bulk load enabled.. I guess it may be using SQL*load or OCI direct path API internally..

    Are these inserts "Direct level insert"as hints Imply? Why do I see NULL in the VALUES clause even though data is there for every row? Is it same as APPEND hint? Oracle documents says INSERT… VALUES can not be DIRECT level INSERTs. So little puzzled.
    Max.,

    I think you're right, that's what is exposed via V$SQL when using the Direct-Path loading API (http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28395/oci13obn.htm#i433129). If you're running SQL*Loader in direct mode you can find similar statements in V$SQL.

    The data is streamed in via the API, therefore the NULLs are merely placeholders, since the actual processing bypasses the SQL engine, so don't confuse this with "single-row" direct-path inserts. You need to use either the APPEND mode of INSERT INTO ... SELECT or the direct-path loading API to do so.

    Regards,
    Randolf

    Oracle related stuff blog:
    http://oracle-randolf.blogspot.com/

    SQLTools++ for Oracle (Open source Oracle GUI for Windows):
    http://www.sqltools-plusplus.org:7676/
    http://sourceforge.net/projects/sqlt-pp/
  • 2. Re: SYS_DL_CURSOR  vs APPEND
    639346 Newbie
    Currently Being Moderated
    Thanks Randolf.

    Are APPEND hint and SYS_DL_CURSOR same? i.e does both perform Direct level Inserts?

    So Since it's DL insert, I should be able to use COMPRESSION at table level. Right?


    TA.
    Max.
  • 3. Re: SYS_DL_CURSOR  vs APPEND
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    max71 wrote:
    Are APPEND hint and SYS_DL_CURSOR same? i.e does both perform Direct level Inserts?
    Max.,

    yes, both perform direct-path inserts.
    So Since it's DL insert, I should be able to use COMPRESSION at table level. Right?
    Yes, that's my understanding. If you're in pre-11g this restriction applies (only CTAS or direct-path inserts via APPEND/PARALLEL or SQL*Loader allow compression), whereas in 11g you get compression even in case of conventional DML:

    http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/schema.htm#sthref734

    http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/schema.htm#CNCPT1133

    Regards,
    Randolf

    Oracle related stuff blog:
    http://oracle-randolf.blogspot.com/

    SQLTools++ for Oracle (Open source Oracle GUI for Windows):
    http://www.sqltools-plusplus.org:7676/
    http://sourceforge.net/projects/sqlt-pp/
  • 4. Re: SYS_DL_CURSOR  vs APPEND
    639346 Newbie
    Currently Being Moderated
    Thanks again Randolf,

    Shouldn't Direct Path Insert be quicker? In my case , I get almost same performance ( Elapsed time ). I haven't done the 10046 trace yet but couple of runs with Conventional Insert and DL insert shows the same performance..

    When does DL Insert gives better performance..?

    -Max.
  • 5. Re: SYS_DL_CURSOR  vs APPEND
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    max71 wrote:
    Shouldn't Direct Path Insert be quicker? In my case , I get almost same performance ( Elapsed time ). I haven't done the 10046 trace yet but couple of runs with Conventional Insert and DL insert shows the same performance..

    When does DL Insert gives better performance..?
    Max.,

    usually direct-path inserts should be quicker since they simply perform less work. Direct-path inserts don't generate undo (and therefore no redo for the undo). Furthermore your can run it in parallel and combine it with the NOLOGGING attribute of the segment so that even redo generation is minimized. In addition it writes "clean" blocks that don't require "delayed block cleanout" which influences processes that attempt to access the data afterwards

    Of course, nothing is for free, so the direct-path insert has some restrictions and downsides: It locks the whole segment exclusively, so no concurrent DML is possible. Furthermore it's quite restricted, the most common restrictions can be found here and here.

    Are you sure that your test run was actually performing a direct-path insert? If any of the restrictions are violated (e.g. enabled referential constraints) it silently falls back to conventional insert.

    You can simply check if it was a direct-path insert by attempting to query the segment written to, e.g. a simple "select count(*) from <segment> where rownum <= 1" will fail with "ORA-12838: cannot read/modify an object after modifying it in parallel" if it was a direct-path insert.

    If you have a lot of usable indexes created on the segment being inserted into, the index maintenance might also take some time. Although the direct-path insert uses an optimized index maintenance approach, the index maintenance itself still generates undo and redo and therefore is not necessarily much faster than that of conventional inserts.

    Make sure that your test runs were using a sufficient large data set, otherwise the differences will be negligible anyway.

    Regards,
    Randolf

    Oracle related stuff blog:
    http://oracle-randolf.blogspot.com/

    SQLTools++ for Oracle (Open source Oracle GUI for Windows):
    http://www.sqltools-plusplus.org:7676/
    http://sourceforge.net/projects/sqlt-pp/
  • 6. Re: SYS_DL_CURSOR  vs APPEND
    639346 Newbie
    Currently Being Moderated
    Randolf,
    You can simply check if it was a direct-path insert by attempting to query the segment written to, e.g. a simple "select count(*) from &lt;segment&gt; where rownum &lt;= 1" will fail with "ORA-12838: cannot read/modify an object after modifying it in parallel" if it was a direct-path insert.
    Since this is being issued from Vendor app., I don't have control to run the select after the fact... Is there any other way to confirm if DL is taking place...

    One more thing I have observed was the locks the sessions have acquired and I did not see any exclusive locks. Also I see TEMP segments being used which led me to believe that it's using DL load. I was able to run two concurrent jobs which inserts into two different partitions of the same table. see below.
                                        Lock     Mode      Mode
    ORAUSER    SID OSUSER   SHADO PAREN Type     Held      Requested Blk? Sta Command    Object Held/Requested
    -------- ----- -------- ----- ----- -------- --------- --------- ---- --- ---------- ------------------------------------------
    APP1      2116 informa  25840 63122 DML(TM)  Share     None      Glob INA INSERT     APP1.T_CA2
                                        Temp Seg Exclusive None      Glob INA INSERT     SYS.C_TS#
              2119 informa  79922 11470 Temp Seg Exclusive None      Glob INA INSERT     SYS.C_TS#
                                        DML(TM)  Share     None      Glob INA INSERT     APP1.T_CA2
    Also tables does not have indexes , constraints , triggers etc..

    I'm inserting about 3+M rows (approx. 1GB of data ). Should it be sufficient to see the difference?

    Thanks.
  • 7. Re: SYS_DL_CURSOR  vs APPEND
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    max71 wrote:
    Since this is being issued from Vendor app., I don't have control to run the select after the fact... Is there any other way to confirm if DL is taking place...

    Also tables does not have indexes , constraints , triggers etc..

    I'm inserting about 3+M rows (approx. 1GB of data ). Should it be sufficient to see the difference?
    You can monitor V$TRANSACTION while the insert takes place. The corresponding entry can be identified via V$SESSION.TADDR = V$TRANSACTION.ADDR. If there is no index to maintain, the USED_UBLK and USED_UREC (Undo blocks and records) should stay at 1 if this a direct-path insert, and increase if it is a conventional insert.

    Regards,
    Randolf

    Oracle related stuff blog:
    http://oracle-randolf.blogspot.com/

    SQLTools++ for Oracle (Open source Oracle GUI for Windows):
    http://www.sqltools-plusplus.org:7676/
    http://sourceforge.net/projects/sqlt-pp/

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points