Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Mar 11 at 19:43

This question came from our site for professional and enthusiast programmers.

3  
Turn on tracing in DBI to see what's going on behind the scenes. –  ThisSuitIsBlackNot Mar 11 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 at 19:16
    
Specific to Perl DBI. –  ND Geek Mar 11 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 at 20:12

2 Answers 2

up vote 6 down vote accepted

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

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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