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?

ND Geek
  • 201
  • 2
  • 9

2 Answers2

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

ND Geek
  • 201
  • 2
  • 9
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.