3

I feel like I must be doing something wrong with my SQL connections. I've generally ignored it, but I'm finally going to try and get to the bottom of this.

my $dsn = "DBI:Oracle:host=$host;sid=$sid";

my $dbh = DBI->connect($dsn,$dbuser,$dbpass,{ RaiseError => 1,AutoCommit => 0 }) or die("Unable to connect: $DBI::errstr");

It takes 60 seconds any time I run DBI->connect. Rather large queries might take a bit of time, network latency may induce some delay, but all of that is on top of the 60 seconds it takes to connect. I can literally run DBI->connect and then immediately close it without actually running a query, and it will take 60 seconds. I can query this database using different tools with no issue, but any time I run a DBI->connect against it, it takes 60 seconds. Most of my queries come back with a time of 61.2 seconds, or 63.4 seconds, or 65.3 seconds. No matter what, it's 60 seconds from the time the script hits DBI->connect to the time it hits the next line. I've even timestamped it on the line before and the line after. the only thing between the timestamps is DBI->connect.

What am I doing wrong?

Improve this question
4
  • 3
    Turn on tracing in DBI to see what's going on behind the scenes.
    – ThisSuitIsBlackNot
    Mar 11, 2014 at 19:01
  • 1
    Is this specific to Perl DBI, or do you experience the same delays with other Oracle clients (e.g. sqlplus) on the same machine?
    – Andrew Medico
    Mar 11, 2014 at 19:16
  • Specific to Perl DBI.
    – ND Geek
    Mar 11, 2014 at 20:08
  • 1
    @ThisSuitIsBlackNot thanks for that! I wasn't familiar with the DBI tracing before. After poking around in the trace, and thanks to Dave Hayes getting my brain thinking about networking, I discovered that if you don't specify the port, DBI/DBD::Oracle tries 1526 first and 1521 second. My target server appears to only be listening on 1521. It was trying 1526, timing out after 60 seconds, and then immediately connecting on 1521.
    – ND Geek
    Mar 11, 2014 at 20:12

2 Answers 2

Reset to default
6

Thanks to @ThisSuitIsBlackNot for pointing me to the tracing functionaility in DBI, I was able to track this down to an error with not specifying port. I knew that I should be pointing at 1521 when setting up other connections in other programs, but DBI didn't require port to function, it was just 60 seconds of waiting for it to give results. DBI assumes 1526 to be the target port first unless told otherwise, so when targeting a server running on 1521, it was timing out after 60 seconds before trying 1521.

Original:

DBI -> port 1526 -> no response -> wait 60s -> no response -> fail -> port 1521 -> success

Specifying port:

DBI -> port 1521 -> success

Improve this answer
1

Without knowing much about your setup, I can't say for sure. However, one quick thing you can check is your DNS resolution of $host.

Outside of perl and oracle, try to do a DNS lookup of the database host from the same machine and see how long that takes.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.