Is it wrong to use direct path load hint in array binding?

Question

As a part of a migration task, I am trying to insert a large amount of data (>30M) into a table in a chunk of 1000/10000 rows using array binding by a .NET application (using ODP.NET); it works. I thought it could be faster if I use direct the path load hint /*+ APPEND_VALUES */. But whenever I try to do that I encounter an exception: ORA-38910:BATCH ERROR MODE is not supported for this operation. In the trace file (collected by tkprof, no sys) I find these:

...

********************************************************************************

insert /*+ APPEND_VALUES */ into MYTABLE(COL1, COL2)
values (:COL1, :COL2)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 122  

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  LOAD AS SELECT  (cr=0 pr=0 pw=0 time=0 us)
      0   BULK BINDS GET  (cr=0 pr=0 pw=0 time=0 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        3.14          3.20
  SQL*Net break/reset to client                   2        0.00          0.00
********************************************************************************

declare
  m_stmt  varchar2(512);
begin
   m_stmt:='delete from sdo_geor_ddl__table$$';
   EXECUTE IMMEDIATE m_stmt;
   EXCEPTION
     WHEN OTHERS THEN
       NULL;
end;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 57     (recursive depth: 1)
********************************************************************************

SQL ID: 3972rvxu3knn3
Plan Hash: 3007952250
delete from sdo_geor_ddl__table$$


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 57     (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  DELETE  SDO_GEOR_DDL__TABLE$$ (cr=0 pr=0 pw=0 time=0 us)
      0   TABLE ACCESS FULL SDO_GEOR_DDL__TABLE$$ (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)


... summary from here

The code I am using:

private void TestArrayBindWithHint()
{
    var data = new List<MyTableData>();

    // ...
    // data fetching + populating code here....
    // ...

    // Insert
    using(var connection = GetConnection())
    {
        var insertQuery = "insert /*+ APPEND_VALUES */ into MYTABLE(COL1, COL2) values (:COL1, :COL2)";
        using (var command = connection.CreateCommand())
        {
            command.CommandText = insertQuery;
            command.CommandType = CommandType.Text;
            command.BindByName = true;
            command.ArrayBindCount = data.Count;

            command.Parameters.Add(":COL1", OracleDbType.Int64, data.Select(d => d.COL1).ToArray(), ParameterDirection.Input);
            command.Parameters.Add(":COL2", OracleDbType.Byte, data.Select(d => d.COL2).ToArray(), ParameterDirection.Input);
            command.ExecuteNonQuery();
        }
    }
}

private ManagedDataAccess.Client.OracleConnection GetConnection()
{
    const string identifier = "MYAPP";
    var connection = new ManagedDataAccess.Client.OracleConnection(ConfigurationManager.AppSettings["connectionString"]);   
    connection.Open();
    connection.ClientId = identifier;
    ExecuteNonQuery(connection, string.Format(@"ALTER SESSION SET TRACEFILE_IDENTIFIER = ""{0}""", identifier));

    return connection;
}

So my question:

• Is it wrong to use direct path load hint in array binding? If no, how can I use it by ODP.NET?
• What is this delete from sdo_geor_ddl__table$$ DDL in the trace? Why is it being generated?

Answer 1

The /*+ APPEND_VALUES */ hint a quite new hint introduced in Oracle 11.2

Typically when you do bulk operations you use the FORALL ... SAVE EXCEPTIONS statement, see FORALL. Using optional SAVE EXCEPTIONS gives you access to SQL%BULK_EXCEPTIONS attributes where you can retrieve exceptions of single records.

When you use /*+ APPEND_VALUES */ hint then SQL%BULK_EXCEPTIONS attributes are not available, i.e. you cannot use SAVE EXCEPTIONS. Personally I would consider this as a bug in Oracle, perhaps it will be rectified in future releases.

Apparently the ODP.NET ExecuteNonQuery() method internally runs always in "Batch Error Mode", see Execution Modes of OCIStmtExecute(). This causes the error. I don't see any possibility to turn off the "Batch Error Mode" so you have to run your insert without /*+ APPEND_VALUES */ hint.