patternMinor
Perl DBI takes 60 seconds to connect to an Oracle database, every single time
Viewed 0 times
connectperlsecondseverytimedatabasesingledbioracletakes
Problem
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.
It takes 60 seconds any time I run
What am I doing wrong?
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?
Solution
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
Original:
DBI -> port 1526 -> no response -> wait 60s -> no response -> fail -> port 1521 -> success
Specifying port:
DBI -> port 1521 -> success
Context
StackExchange Database Administrators Q#60688, answer score: 6
Revisions (0)
No revisions yet.