|P is for Practical|
DBD::Oracle bind params and IO usageby tj_thompson (Monk)
|on Feb 09, 2013 at 00:01 UTC||Need Help??|
tj_thompson has asked for the
wisdom of the Perl Monks concerning the following question:
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:
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:
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.