Re: Slow connection to Oracle 9i

From: Tim Bunce (Tim.Bunce_at_pobox.com)
Date: 10/18/04 

Date: Mon, 18 Oct 2004 09:31:31 +0100
To: Paul Appleby <007@pathcom.com>

On Sun, Oct 17, 2004 at 09:06:56PM -0400, Paul Appleby wrote:
> I have a simple Perl 5.6 test script that uses DBI and DBD::Oracle to connect to a local Oracle 9i database table and retrieve the data in the three small fields of its only two records.
>
> Why is the connection time so long and how can I shorten it?
> It is 3 to 4 times longer than retrieving data from a MySQL database.

Only 3 to 4 times longer than retrieving data from a MySQL?

You're lucky, it's often longer! :)

> This is the connection string:
> $dbh = DBI->connect("dbi:Oracle:$dbname", $user, $passwd) or die("OOPS: $DBI::errstr");
>
> It takes 2.9342188835144 seconds to connect to the database.
> It takes 0.0100140571594238 seconds to retrieve the data and print it.
>
> It takes almost 1 second longer using this connection string:
> $dbh = DBI->connect("dbi:Oracle:host=$location;sid=$dbname", $user, $passwd);

I'd guess the difference is that the second is forcing a network connection
(even if host is localhost).

> I also used "d:DProf" and ran the test script, and then "dprofpp -u" to analyze the resulting "tmon.out" file:
> %Time ExclSec CumulS #Calls sec/call Csec/c Name
> 21.6 0.090 0.090 1 0.0900 0.0900 DBD::Oracle::db::_login
> 21.6 0.090 0.159 1 0.0899 0.1592 DBD::Oracle::dr::load_dbnames
> 21.6 0.090 0.159 6 0.0149 0.0265 main::BEGIN

DBD::Oracle::dr::load_dbnames is only called by data_sources()
so don't call data_sources() unless you really need to.

Oracle is widely known to be slow to connect to. The usual approach is
to try to stay connected rather than keep reconnecting. Oracle MTS may
help but has its own set of problems.

Tim.

Relevant Pages

  • Re: Cache Issues
    ... I ended up putting trace stuff everywhere and found the offending statement: ... I was opening a connection to the database independent of getting the data ... loaded from the DB and not from the cache. ... > finished retrieving data from the cache.... ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Cant connect to Oracle 9i when connected to DB2
    ... > (without retrieving data from either, ... I get a core dump when I try to open the Oracle ... > connection. ...
    (perl.dbi.users)
  • There is already an open DataReader associated with this Connection which must be closed first, AGAI
    ... I'm retrieving Data from Paradox database using, ... Insert retrieved values from two sql statement into Access Table ... already an open DataReader associated with this Connection which must be ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: General ODBC error AS400
    ... here is a great website for connection issues ... I am retrieving data from AS/400 database to a worksheet ... General ODBC error " ... Must be a problem with my connection string, ...
    (microsoft.public.excel.programming)
  • Re: poor performance on first query
    ... > I've got an ASP site querying an Access database to get photo gallery ... If it's the connection time that's the problem, ... This email account is my spam trap so I ...
    (microsoft.public.inetserver.asp.db)