Re: Slow Performance When Using DBI, otherwise Not
- From: rhugga@xxxxxxxxx (Rhugga Harper)
- Date: Thu, 23 Feb 2006 15:09:18 -0600
I added some debug output and have determined that the latency occurs here,
during the call to connect:
print "DEBUG 1\n";
my $dbh = DBI->connect($ds, $dbuser, $dbpass);
if (!defined($dbh)) {
print "Error: main(): database connection failed: $DBI::errstr\n";
cleanup();
exit(-1);
}
my $query1 = "SELECT hostname from ithug.adc_ait_hosts where status > 0
order by hostname asc ";
print "DEBUG 2\n";
When running this script I immediately see "DEBUG 1", then I get a latency
of anywhere from 3 - 10 seconds, then I see "DEBUG 2" and my query output
almost immediately.
The shell script completes in under 1/2 second:
time ./ait_hosts.sh > c.1
real 0m0.375s
user 0m0.170s
sys 0m0.070s
Any ideas why the connect call would be lagging?
-CC
On 2/23/06, Reidy, Ron <Ron.Reidy@xxxxxxxxxxxxxxxxxx> wrote:
The prepare statement in DBI will prepare and then execute the
statement. This is, essentially tow PARSE calls against the dictionary
cache.
Look at using ora_check_sql
(http://search.cpan.org/~timb/DBD-Oracle-1.16/Oracle.pm#Prepare_postpone
d_till_execute) to eliminate this issue.
If the problem persists after this change, let us know where your waits
are occurring by using event 10046 and tkprof.
--
Ron Reidy
Lead DBA
Array BioPharma, Inc.
-----Original Message-----
From: Rhugga Harper [mailto:rhugga@xxxxxxxxx]
Sent: Thursday, February 23, 2006 12:46 PM
To: dbi-users@xxxxxxxx
Subject: Slow Performance When Using DBI, otherwise Not
I have a Solaris 8 host running perl 5.8 and using DBI version 1.50 and
DBD::Oracle version 1.16. The database is Oracle 10.2.0.1 and runs on a
different host.
If I run this query from a shell script, it completes in under 1 second,
however, using a perl script it takes 5-10 seconds.
Here is the shell script:
#!/bin/bash
ORACLE_HOME=/u01/app/oracle/product/10.2
ORACLE_SID=mysid SQLPLUS=/u01/app/oracle/product/10.2/bin/sqlplus
$SQLPLUS -s xxxxx/xxxxx@mysid << EOF
/ as sysdba
SELECT hostname from xxxxx.adc_ait_hosts where status > 0 order by
hostname asc; quit; EOF
Here is the perl code to do the same:
$ENV{ORACLE_HOME} = $_oracle_home;
$ENV{TWO_TASK} = $_oracle_two_task;
$ENV{TNS_ADMIN} = $_tns_admin;
my $ds = "dbi:Oracle:$_dbsid";
my $dbuser = $_dbuser;
my $dbpass = $_dbpass;
my @tapeservers;
my $dbh = DBI->connect($ds, $dbuser, $dbpass);
if (!defined($dbh)) {
print "Error: main(): database connection failed:
$DBI::errstr\n";
cleanup();
exit(-1);
}
my $query1 = "SELECT hostname from xxxxx.adc_ait_hosts where status
0order by hostname asc ";
my $sth1 = $dbh->prepare($query1) || die "Error: Unable to
prepare
query: $DBI::errstr\n";
$sth1->execute();
while ( my @row = $sth1->fetchrow_array)
{
if ($STRIP) {
my ($host, $subd, $domain, $sfx) = split(/\./, $row[0]);
print STDOUT "$host\n";
} else {
print STDOUT "$row[0]\n";
}
}
Can anyone point me in the right direction?
Thx,
CC
This electronic message transmission is a PRIVATE communication which
contains
information which may be confidential or privileged. The information is
intended
to be for the use of the individual or entity named above. If you are not
the
intended recipient, please be aware that any disclosure, copying,
distribution
or use of the contents of this information is prohibited. Please notify
the
sender of the delivery error by replying to this message, or notify us by
telephone (877-633-2436, ext. 0), and then delete it from your system.
- References:
- RE: Slow Performance When Using DBI, otherwise Not
- From: Ron Reidy
- RE: Slow Performance When Using DBI, otherwise Not
- Prev by Date: Re: Calling a PostgreSQL function via DBI
- Next by Date: Re: Calling a PostgreSQL function via DBI
- Previous by thread: RE: Slow Performance When Using DBI, otherwise Not
- Next by thread: RE: Slow Performance When Using DBI, otherwise Not
- Index(es):
Relevant Pages
|
