Strange performance problem

Could someone help me with the following very strange performance
problem? I believe that there is a strange bug in either DBI or
DBD::Oracle.

This appeared shortly after upgrading PERL from 5.6.1 to 5.8.5, DBI
from 1.19 to 1.48, DBD::Oracle from 1.08 to 1.16, and Oracle from 8.1.7
to 9.2.

We use one query to get information about the filter conditions for a
report, and a second query to retrieve the report data, using the
filter conditions collected from the first query. The SQL is complex
enough that it would be too difficult to combine them into one query
using a join.

During the second query, when we use $dbh->prepare($sql) with one bind
parameter and $sth->execute($value), each execute() takes about 1.5
minutes. However, if I remove the bind parameter by putting it directly
into the SQL (so that prepare is called for every iteration of the loop
- i.e. what you're not supposed to do), then each execute() takes about
2 seconds.

The key to the problem is that $value is returned by a $sth->fetchrow()
from a previous query. If I modify the PERL program to hardcode $value,
than $sth->execute($value) returns in 2 seconds. I have inspected the
two values (hardcoded vs. returned from fetchrow) using pack() to
convert them to hex, and the strings are in fact identical.

In short, it has something to do with the fact that the memory for
$value was allocated within fetchrow() from a previous query.

If I run the query through SQLPLUS, then it runs in 2 seconds.

I have found out how to work around this problem, but our application
is quite large and we could be suffering from this in other places, so
it would be better to get the problem addressed centrally than to try
to find everywhere in our code that a workaround might be needed.

Can someone help me out with this?

Thanks,
Scott

.

Relevant Pages

  • RE: How to perform JOINs with DBI
    ... How to perform JOINs with DBI ... What does the query do in SQL*Plus? ... intended recipient, please be aware that any disclosure, copying, distribution ... sender of the delivery error by replying to this message, or notify us by ...
    (perl.dbi.users)
  • Re: Order by statement stopped working but not in all cases
    ... If what you say is true and none of you SW (Perl, DBI, DBD, Oracle) has ... by statement in the query, it again returned results though not sorted. ... The apps have not been changed in the past week. ...
    (perl.dbi.users)
  • RE: binding multiple values and retain blanks
    ... Join this table to the main table in your query. ... When i do like above, trailing spaces are lost, as DBI treats default ... So i have to bind one value at a time and specify type as CHAR in order ...
    (perl.dbi.users)
  • Re: Error on DBI
    ... DBI never falsely reports errors. ... Copy and paste the SQL statement from rpt_preview.plx into an SQL query tool, and use the username and password from rpt_preview.plx to login. ...
    (perl.dbi.users)
  • RE: Sarge problems with MySQL and DBI / DBD::mysql
    ... >> the procedures for upgrading the database. ... >> with the mysql client. ... > error message which claims the query produced a DBI error, ...
    (Debian-User)