Some days ago i came across with a pl/sql code where they used (for some reason) an Oracle hint called SYS_DL_CURSOR. I got it when i was looking into V$SQL and i was not familiar with this, maybe the only thing i (“think”) know about it is that in some conditions it performs a direct path insert, just like append hint. Hint itself seems to be not official documented by Oracle and i figured out that some aplications (Hi Informatica PowerCenter) uses it for some direct path insert ETL.
This is not by any means a “standard” way to do direct path inserts but if it works good, we need to get into it. So i decided to take a more deep look inside this hint to make sure that i understand what it really does.
A good way to verify if your direct path insert got right is try to query the table segment before your COMMIT or ROLLBACK operation. Let’s create a table and try a simple direct path insert:
SQL> create table t1_dpi(n1 number, n2 number, CONSTRAINT pk_n UNIQUE(n1));
SQL> insert /*+ append_values */ into t1_dpi VALUES(1,1);
SQL> insert /*+ append_values */ into t1_dpi VALUES(2,2);
SQL> select * from t1_dpi where rownum < 1;
select * from t1_dpi where rownum < 1
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
As you can easily see, we got an ORA-12838 after querying segment when doing a direct path. This happens because the transaction made an attempt to read (or modify) statements on a table and this is not allowed in direct loads. It will prevent data inconsistency [see this].
In this way you ensure that you will use an direct path insert using the common hint /*+ append_values */ (or /*+ append */) however you can use some 10046 trace:
SQL> alter session set events='10046 trace name context forever, level 12';
Session altered.
insert /*+ append_values */ into lcmarques.t1_dpi VALUES(2,2)
END OF STMT
PARSE #3:c=1000,e=926,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=3581094869,tim=1324125386379660
WAIT #3: nam='direct path write' ela= 37 file number=4 first dba=10908 block cnt=1 obj#=-1 tim=1324125386380206
So as everybody expected append_values hint works as advertised :). Now let’s try SYS_DL_CURSOR
SQL> insert /*+ SYS_DL_CURSOR */ into t1_dpi values (8,8);
1 row created.
SQL> select * from t1_dpi where rownum < 1;
no rows selected
It seems that we can query the table (no ORA-12838), so probably the HINT is not make direct path insert and Oracle is ignoring it. Let’s check explain plan and 10046 trace to make sure:
--------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------+-----------------------------------+
| 0 | INSERT STATEMENT | | | | 1 | |
| 1 | LOAD TABLE CONVENTIONAL | | | | | |
--------------------------------------------+-----------------------------------+
insert /*+ SYS_DL_CURSOR */ into t1_dpi values (8,8)
END OF STMT
PARSE #3:c=1000,e=931,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1324126029360833
EXEC #3:c=0,e=169,p=0,cr=1,cu=7,mis=0,r=1,dep=0,og=1,plh=0,tim=1324126029361070
STAT #3 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL (cr=1 pr=0 pw=0 time=0 us)'
No luck here! Next option is now use a 10053 to unsure that CBO is not ignoring silently SYS_DL_CURSOR hint. Dumping Hints section shows even invalid/malformed hints here and for some invalid hints it shows err=1 or used=0. On our case used=0 shows that for some reason this hint is not beeing used:
SQL> alter session set events='10053 trace name context forever, level 1';
Dumping Hints
=============
atom_hint=(@=0x8cd666f0 err=0 resol=0 used=0 token=914 org=1 lvl=1 txt=SYS_DL_CURSOR ())
====================== END SQL Statement Dump ======================
Another idea might be trying to use sqlldr (i googled some cases) to generate SYS_DL_CURSOR hint for direct path loading, but no luck here too:
[oracle@localhost scripts]$ sqlldr lcmarques/lcmarques@testSID control=loader.ctl log=logfile.log direct=true;
Load completed - logical record count 9.
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB
) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB)
NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1") FROM (SELECT /*+
NO_PARALLEL("T1_DPI") FULL("T1_DPI") NO_PARALLEL_INDEX("T1_DPI") */ :"SYS_B_2"
AS C1, :"SYS_B_3" AS C2 FROM "T1_DPI" "T1_DPI") SAMPLESUB
Last thing was to check in v$sql_hint for this hint:
SQL> select class from v$sql_hint where class like 'SYS_DL%';
CLASS
----------------------------------------------------------------
SYS_DL_CURSOR
Conclusion is simple, i can’t make it. For some reason Oracle CBO is ignoring SYS_DL_CURSOR. Maybe this is valid under certain circunstances that i really don’t know or is already depreceated for direct path inserts or 11g doesn’t really like it.
credit: https://lcmarques.wordpress.com/2011/12/17/sys_dl_cursor-i-cant-make-it/