DBI & DBD::Oracle -- Executing a statement handle across a database link

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I'm having some serious performance issues with a query that joins two tables across a database link in Oracle. When I run the query via SQLPLUS, or I run it in the form $dbhandle->selectall_arrayref($statement), it returns in milliseconds. But when I prepare the statement and bind parameters to it, the $sth->execute() or $sth->execute(@parameters) takes more than 25 seconds to return. The number of rows produced by the join is trivial, although (admittedly) the number of rows in the joined table is large. Any suggestions?

my $statement = 'select a.foo, b.bar from local a,
 remote@my_database_link b where a.baz = b.baz (+) 
 and a.parm1 = 1 and b.parm2 = 2';
my $ary_ref = $dbhandle->selectall_arrayref($statement); # this execut
+es in less than a second

$statement = 'select a.foo, b.bar from local a, 
 remote@my_database_link b where a.baz = b.baz (+) 
 and a.parm1 = ? and b.parm2 = ?';
my $sth = $dbhandle->prepare($statement);
my $rv = $sth->execute(1,2); # this takes 25+ seconds to execute
$ary_ref = $sth->fetchall_arrayref(); # this takes milliseconds
[download]

Comment on DBI & DBD::Oracle -- Executing a statement handle across a database link Download Code

Replies are listed 'Best First'.
Re: DBI & DBD::Oracle -- Executing a statement handle across a database link by mje (Curate) on Jan 07, 2010 at 09:17 UTC
I've had a similar issue before myself and I'd guess an index is not being used. By default DBD::Oracle binds all parameters as strings and this can lead to Oracle deciding that since the parameter is a string and that does not match an integer then the index will not be used. Remove your parameters and just do the entire SQL as a string and see if that improves it. If it does, you have probably hit this problem. Then you need to look at ora_type => ora_xxx and SQLCS_IMPLICIT. Search for my posting on the dbi-users list in the last 4 years where I had the same issue.	[reply]
Re^2: DBI & DBD::Oracle -- Executing a statement handle across a database link by Anonymous Monk on Jan 07, 2010 at 18:11 UTC
Thank you. I'll take a look at applying this recommendation for numeric parameters: `$sth->bind_param(1, $parm1, { ora_csform => SQLCS_IMPLICIT });` Unfortunately, the problem has 'magically' resolved itself, without any (known) tinkering with Oracle or my code. I guess I'll have to wait until the problem resurfaces before I can test this out.	[reply] [d/l]