APPEND_VALUES and SYS_DL_CURSOR Hints with HCC

The APPEND_VALUES hint was introduced in 11.2 to allow direct path inserts with variables using the VALUES clause. i.e.

INSERT INTO XYZ (COL1, COL2) VALUES (:A, :B);

The feature was designed to allow bulk inserting via arrays of 100′s or 1000′s of records in a single insert statement. Prior to 11.2, there was no documented way to do an direct path insert other than with the APPEND hint which only worked on inserts that used the SELECT clause. i.e.

INSERT INTO XYZ SELECT * from ZYX;

There was however an undocumented hint (SYS_DL_CURSOR) which did a bulk insert of sorts. (not a full append) You may have seen this hint if you use Informatica. I was recently asked about the use of Informatica with Hybrid Columnar Compression (HCC) on Exadata. Which prompted a little research on these two methods of loading data and whether they were compatible with HCC or not. So first off, here’s a test with the APPEND_VALUES clause (using my check_row_comp.sql script):

 
KSO@dbm1> !cat bulk_insert1.sql
 
CREATE TABLE t1
(c1    NUMBER,
 c2    NUMBER,
 c3    VARCHAR2(50),
 c4    VARCHAR2(50),
 c5    DATE,
 c6    DATE)
compress for query high;
 
DECLARE
  -- Define a collection
  TYPE t1_tbl_type IS TABLE OF t1%ROWTYPE;
  t1_tbl    t1_tbl_type := t1_tbl_type();
BEGIN
 
  -- Populate the collection
  FOR i IN 1..32000 LOOP
    t1_tbl.EXTEND;
    t1_tbl(i).c1 := i;
    t1_tbl(i).c2 := i*i;
    t1_tbl(i).c3 := 'i=' || TO_CHAR(i);
    t1_tbl(i).c4 := 'i*i=' || TO_CHAR(i*i);
    t1_tbl(i).c5 := SYSDATE;
    t1_tbl(i).c6 := SYSDATE;
  END LOOP;
 
  -- Bulk Insert the collection into table T1
  FORALL i IN 1..t1_tbl.COUNT
    INSERT /*+ append_values */ INTO t1
      (c1,
       c2,
       c3,
       c4,
       c5,
       c6)
    VALUES
      (t1_tbl(i).c1,
       t1_tbl(i).c2,
       t1_tbl(i).c3,
       t1_tbl(i).c4,
       t1_tbl(i).c5,
       t1_tbl(i).c6);
 
END;
/
 
KSO@dbm1> @bulk_insert1
 
Table created.
 
 
PL/SQL procedure successfully completed.
 
KSO@dbm1> select count(*) from t1;
select count(*) from t1
                     *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
 
 
KSO@dbm1> -- correct behavior for direct path insert
KSO@dbm1> commit;
 
Commit complete.
 
KSO@dbm1> select rowid from t1 where rownum < 10;
 
ROWID
------------------
AAARlqAAIAAB/krAAA
AAARlqAAIAAB/krAAB
AAARlqAAIAAB/krAAC
AAARlqAAIAAB/krAAD
AAARlqAAIAAB/krAAE
AAARlqAAIAAB/krAAF
AAARlqAAIAAB/krAAG
AAARlqAAIAAB/krAAH
AAARlqAAIAAB/krAAI
 
9 rows selected.
 
KSO@dbm1> @check_row_comp.sql
Enter value for owner: KSO
Enter value for table_namr: T1
Enter value for rowid: AAARlqAAIAAB/krAAH
 
OLD_ROWID            COMPRESSION_TYPE
-------------------- -------------------------
8.522539.7           HCC Query High

So the APPEND_VALUES hint behaves as expected, does a proper append and applies HCC. There are a couple of corner cases worth mentioning. First, small inserts (a single row for example) will not trigger the HCC processing. See the following example.

 
KSO@dbm1> insert /*+ append_values */ into t1 (c1) values (-1);
 
1 row created.
 
KSO@dbm1> select count(*) from t1 where c1 = -1;
select count(*) from t1 where c1 = -1
                     *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
 
 
KSO@dbm1> commit;
 
Commit complete.
 
KSO@dbm1> select rowid from t1 where c1 = -1;
 
ROWID
------------------
AAARlqAAIAAEfDLAAA
 
KSO@dbm1> @check_row_comp.sql
Enter value for owner: KSO
Enter value for table_namr: T1
Enter value for rowid: AAARlqAAIAAEfDLAAA
 
OLD_ROWID            COMPRESSION_TYPE
-------------------- -------------------------
8.1175755.0          No Compression

So a single row insert was done in direct path mode, but HCC was not applied due to the small size of the insert.

The second corner case is that objects owned by SYS (and stored in the SYSTEM tablespace) appear to be ineligible for HCC processing. See the example below which shows the same test but when T1 is owned by SYS. It also shows an alter table move does not compress the rows when the object is owned by SYS and stored in SYSTEM. Note: there could be something else that is preventing the compression but objects in SYSTEM that are not owned by SYS and objects that are owned by SYS but not stored in SYSTEM both seem to behave correctly. As Tanel says, “the rabbit hole always gets deeper”. (I’ll leave those tests as an exercise for the reader rather than clutter up this post) Here’s the simple case of creating a table in the SYSTEM tablespace that is owned by SYS.

SYS@dbm1> @bulk_insert1
 
Table created.
 
 
PL/SQL procedure successfully completed.
 
SYS@dbm1> select count(*) from t1;
select count(*) from t1
                     *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
 
 
SYS@dbm1> commit;
 
Commit complete.
 
SYS@dbm1> select rowid from t1 where rownum < 10;
 
ROWID
------------------
AAARltAABAAAUnRAAA
AAARltAABAAAUnRAAB
AAARltAABAAAUnRAAC
AAARltAABAAAUnRAAD
AAARltAABAAAUnRAAE
AAARltAABAAAUnRAAF
AAARltAABAAAUnRAAG
AAARltAABAAAUnRAAH
AAARltAABAAAUnRAAI
 
9 rows selected.
 
SYS@dbm1> @check_row_comp.sql
Enter value for owner: SYS
Enter value for table_namr: T1
Enter value for rowid: AAARltAABAAAUnRAAG
 
OLD_ROWID            COMPRESSION_TYPE
-------------------- -------------------------
1.84433.6            No Compression
 
SYS@dbm1> @table_size
Enter value for owner: SYS
Enter value for table_name: T1
Enter value for type: 
Enter value for tablespace_name: 
 
OWNER                SEGMENT_NAME                   TYPE               TOTALSIZE_MEGS TABLESPACE_NAME
-------------------- ------------------------------ ------------------ -------------- ------------------------------
SYS                  T1                             TABLE                         2.0 SYSTEM
                                                                       --------------
sum                                                                               2.0
 
SYS@dbm1> alter table sys.t1 move compress for archive high;
 
Table altered.
 
SYS@dbm1> @table_size
Enter value for owner: SYS
Enter value for table_name: T1
Enter value for type: 
Enter value for tablespace_name: 
 
OWNER                SEGMENT_NAME                   TYPE               TOTALSIZE_MEGS TABLESPACE_NAME
-------------------- ------------------------------ ------------------ -------------- ------------------------------
SYS                  T1                             TABLE                         2.0 SYSTEM
                                                                       --------------
sum                                                                               2.0
 
SYS@dbm1> select rowid from t1 where rownum < 10;
 
ROWID
------------------
AAARluAABAAAXNRAAA
AAARluAABAAAXNRAAB
AAARluAABAAAXNRAAC
AAARluAABAAAXNRAAD
AAARluAABAAAXNRAAE
AAARluAABAAAXNRAAF
AAARluAABAAAXNRAAG
AAARluAABAAAXNRAAH
AAARluAABAAAXNRAAI
 
9 rows selected.
 
SYS@dbm1> @check_row_comp.sql
Enter value for owner: SYS
Enter value for table_namr: T1
Enter value for rowid: AAARluAABAAAXNRAAG
 
OLD_ROWID            COMPRESSION_TYPE
-------------------- -------------------------
1.95057.6            No Compression

So again, in this case, it is clearly a direct path insert, but HCC is not applied.

So what about the older SYS_DL_CURSOR hint. Well the short story is it doesn’t do a real direct path load, so it doesn’t work with HCC. Here’s a quick demonstration (back in a regular user account).

KSO@dbm1> !cat bulk_insert2.sql
CREATE TABLE t1
(c1    NUMBER,
 c2    NUMBER,
 c3    VARCHAR2(50),
 c4    VARCHAR2(50),
 c5    DATE,
 c6    DATE)
compress for query high;
 
DECLARE
  -- Define a collection
  TYPE t1_tbl_type IS TABLE OF t1%ROWTYPE;
  t1_tbl    t1_tbl_type := t1_tbl_type();
BEGIN
 
  -- Populate the collection
  FOR i IN 1..32000 LOOP
    t1_tbl.EXTEND;
    t1_tbl(i).c1 := i;
    t1_tbl(i).c2 := i*i;
    t1_tbl(i).c3 := 'i=' || TO_CHAR(i);
    t1_tbl(i).c4 := 'i*i=' || TO_CHAR(i*i);
    t1_tbl(i).c5 := SYSDATE;
    t1_tbl(i).c6 := SYSDATE;
  END LOOP;
 
  -- Bulk Insert the collection into table T1
  FORALL i IN 1..t1_tbl.COUNT
    INSERT /*+ sys_dl_cursor */ INTO t1
      (c1,
       c2,
       c3,
       c4,
       c5,
       c6)
    VALUES
      (t1_tbl(i).c1,
       t1_tbl(i).c2,
       t1_tbl(i).c3,
       t1_tbl(i).c4,
       t1_tbl(i).c5,
       t1_tbl(i).c6);
 
END;
/
 
KSO@dbm1> drop table t1;
 
Table dropped.
 
KSO@dbm1> @bulk_insert2
 
Table created.
 
 
PL/SQL procedure successfully completed.
 
KSO@dbm1> select count(*) from t1;
 
  COUNT(*)
----------
     32000
 
KSO@dbm1> -- Not a good sign 
KSO@dbm1> -- direct path should not allow a select without a commit or rollback
KSO@dbm1> -- let's check anyway
KSO@dbm1> select rowid from t1 where rownum < 10;
 
ROWID
------------------
AAARlzAAIAAB/krAAA
AAARlzAAIAAB/krAAB
AAARlzAAIAAB/krAAC
AAARlzAAIAAB/krAAD
AAARlzAAIAAB/krAAE
AAARlzAAIAAB/krAAF
AAARlzAAIAAB/krAAG
AAARlzAAIAAB/krAAH
AAARlzAAIAAB/krAAI
 
9 rows selected.
 
KSO@dbm1> @check_row_comp.sql
Enter value for owner: KSO
Enter value for table_namr: T1
Enter value for rowid: AAARlzAAIAAB/krAAG
 
OLD_ROWID            COMPRESSION_TYPE
-------------------- -------------------------
8.522539.6           No Compression

So no love with SYS_DL_CURSOR. Of course you can still do the more normal, load, alter table move, exchange partition type processing. As always, please let me know if you any questions or comments.

5 Comments

  1. Richard says:

    Hi, I’ve been working with our developers who want to use the BULK load in Informatica.
    But we don’t give full access to the INFOR user, like DBA.
    I’ve been looking for the privilege that is used to grant access to the SYS_DL_CURSOR hint because right now our INFOR user gets a ORA-01031: insufficient privileges when he tries to run the workflow.
    Do you know what privilege is required to allow the use of the HINT?
    Thanks, Richard

  2. osborne says:

    Hi Richard,

    I don’t believe there are any privileges required to use hints (at least I am not aware of any). Maybe you can point me to some information on that. Here’s the output of oerr on the 1031 error:

    
    SYS@dbm1> !oerr ora 1031
    01031, 00000, "insufficient privileges"
    // *Cause: An attempt was made to change the current username or password
    //         without the appropriate privilege. This error also occurs if
    //         attempting to install a database without the necessary operating
    //         system privileges.
    //         When Trusted Oracle is configure in DBMS MAC, this error may occur
    //         if the user was granted the necessary privilege at a higher label
    //         than the current login.
    // *Action: Ask the database administrator to perform the operation or grant
    //          the required privileges.
    //          For Trusted Oracle users getting this error although granted the
    //          the appropriate privilege at a higher label, ask the database
    //          administrator to regrant the privilege at the appropriate label.
    

    Looks like the workflow may try to change users or something.

    Kerry

  3. Hi Kerry,

    Excellent findings in the post. This is something which has always remained in state of confusion.

    Would be interested in knowing your standpoint on when to use SYS_DL_CURSOR in place of APPEND hint. Is it something to address table locking or parallel thread execution? What could be the significant or exclusive benefits of using SYS_DL_CURSOR?

    Thanks – Saurabh

  4. osborne says:

    Hi Saurabh,

    APPEND showed up in version 8.1, SYS_DL_CURSOR showed up in 9.1 and APPEND_VALUES didn’t show up until 11.2 (probably because of Exadata I’m guessing). SYS_DL_CURSOR was designed to allow “direct path like” inserts using a values clause – but it doesn’t behave exactly like a direct path insert (you’ll notice from the post that it does not require a commit before doing any further access to the object). The older APPEND hint does not support a values clause on the insert so you have to do an insert as select to use it. So the advantage of the SYS_DL_CURSOR when it was introduced (and until 11.2) was that you could do array inserts using the values clause. It has been used by Informatica for a long time when the user chooses the bulk loading option. The APPEND_VALUES hint actually does a true direct path insert (above the high water mark) and also correctly handles HCC processing on Exadata. So to my way of thinking, SYS_DL_CURSOR provides no benefit if you are running 11.2 or higher, but may be of use if you are on an older version.

    Kerry

  5. Kasey Parker says:

    Hi Kerry –

    I know this post is a few months old now and not sure if there has been any more follow-up. I came across it doing some other research around HCC. I have some experience, at a previous employer, with the SYS_DL_CURSOR hint and HCC on Exadata. This hint is used by the OCI Direct-Path loading API (http://docs.oracle.com/cd/E11882_01/appdev.112/e10646/oci13obn.htm#LNOCI120). This API is used by different ETL and other tools, including SQL Loader, to do direct path loading. My experience with this was with SAP’s Data Services ETL tool – and I believe Informatica uses the same API.

    When using this API, the processing actually bypasses the SQL engine to do the direct path load. When running loads with the API V$SQL shows the use of the hint and actually uses NULLs a placeholders for the values. However, this is all just giving a representation of what is happening for the purposes of monitoring in V$SQL – not what really is happening, since the SQL engine is not used at all. The data does get loaded in direct path – and HCC compression does work – if going through the API. We validated this in our environments with using the bulk load option with Data Services ETL, and the data was HCC compressed. But I don’t believe the hint, as your tests show above, is an alternative for the APPEND or APPEND_VALUES hints; and if using it in SQL directly it won’t work and doesn’t direct path load at all. I think this is why the hint is undocumented.

    So, I think the important thing here is to not get mixed up between using the direct path load API to do direct path loading, which shows the SYS_DL_CURSOR hint in V$SQL, and the use of the SYS_DL_CURSOR hint in a SQL statement. The API will still direct path load and compress with HCC, if enabled on the table. I haven’t done any testing with Informatica on this specifically, but I believe Informatica uses the same API for its bulk load processing. If it does, then its bulk loading should also work with HCC.

    Thanks,
    Kasey

Leave a Reply