Please find below my tiny SQL script, perl sample script and also C++/otlv4 sample code.

In my environment the perl script for 10000 rows runs about 5secs for each insertion mode except the third one (testins3). testins3 calls execute_array method that uses Oracle OCI's array interface DBD::Oracle and runs in a split second. But this is a different business. The C++ code runs in a split second too. It also based on OCI calls. Of course, C++ code must be faster than its perl equivalent but not that extent. I also played with database handler parameters (see the perl code below) without any kind of success.

Now, my consideration is that the reason of this poor performance of the perl script is unnecessary and/or inappropriate OCI calls in DBD::Oracle. Unfortunately I am not an Oracle OCI interface expert. BTW, I have never seen such an ugly database API like OCI. I try to understand how otlv4 (http://otl.sourceforge.net/otl3_intro.htm) is implemented and compare it to oci8.c and dbdimp.c in module DBD::Oracle with no success until now. otlv4 does not call OCIBindDynamic at all and use OCI_DEFAULT mode instead of DATA_AT_EXEC.

There is a problem here:

my $dbh = DBI->connect("dbi:Oracle:$dbname", $user, $passwd,
        {
            AutoCommit => 0,
            RaiseError => 1,
            RowCacheSize => 10000,
            ora_array_chunk_size => 100000,
            #ora_check_sql => 0,
            #ora_verbose => 0xff,
            #ora_oci_success_warn => 1,
        }
);
[download]

To my understanding, AutoCommit => 0; doesn't mean much. The default action will be to "commit" each insert operation no matter what this says, if you do not start a transaction explicitly.

You need to start a transaction explicitly, insert huge amount of rows and then end the transaction explicitly. As I have suggested before, about 128K rows per commit will work fine.