DBD::Oracle bind params and IO usage

[tj_thompson]

Hello monks,

I have a question regarding what's going on in the background of the DBD::Oracle driver when I'm pulling data. This is likely as much an Oracle question as a Perl question, but I'm not sure if it's Perl doing something funny or Oracle and I like you guys so here I am :)

I have a fairly complex SQL query that's returning this error:

ORA-02395: exceeded call limit on IO usage

Currently, I execute this query like this:

my $sql = <<'END';
select the_data
from my_table
where filter1 = ?
and filter2 = ?
END

# dbh is my database handle, already connected here
my $sth = $dbh->prepare($sql);

$sth->execute( $bind_val_1, $bind_val_2 );

# this line throws my error
my $data = $sth->fetchall_arrayref;

fetchall_arrayref is being used due to the very high latency to the database causing per row processing to be 30-100x slower than a full fetch followed by processing. However, same error is thrown even with a loop and fetchrow_arrayref as well.

The part that I find interesting, is that if I change the way I pull my data to this it no longer throws the error:

my $sql = <<'END';
select the_data
from my_table
where filter1 = $bind_val_1
and filter2 = $bind_val_2
END

# dbh is my database handle, already connected here
my $sth = $dbh->prepare($sql);

$sth->execute;

# This line now completes successfully
my $data = $sth->fetchall_arrayref;

The implication is that bind params are somehow costing more internal IO operations than directly inserting the values into the sql you wish to execute.

So the question: Why does binding your values with placeholders actually create additional DB IO for your query when it's executed/fetched and how expensive is param binding from an IO perspective? I don't know of a way to benchmark IO without being an admin of the db or having certain DB privileges that I don't have, so it's been difficult to get a handle on this.