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.
