No such thing as a small change | |
PerlMonks |
DBD::Oracle bind params and IO usageby tj_thompson (Monk) |
on Feb 09, 2013 at 00:01 UTC ( [id://1017908]=perlquestion: print w/replies, xml ) | Need Help?? |
tj_thompson has asked for the wisdom of the Perl Monks concerning the following question: 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 usageCurrently, 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.
Back to
Seekers of Perl Wisdom
|
|