SYS_DL_CURSOR – I can't make it


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)
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 | |

insert /*+ SYS_DL_CURSOR */ into t1_dpi values (8,8)
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 ideia 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.

) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(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%';



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.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s