Rhugga,
I have never seen this before. What happens if you step through this
with the debugger? Are there logon triggers firing for the user during
connect? Is your DB using dedicated connections, or MTS?
Also, I assume your shell script is connecting to the same DB, over the
same network, using the same username.
-----Original Message-----
From: Rhugga Harper [mailto:[EMAIL PROTECTED]
Sent: Thursday, February 23, 2006 2:09 PM
To: Reidy, Ron
Cc: [email protected]
Subject: Re: Slow Performance When Using DBI, otherwise Not
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 <[EMAIL PROTECTED]> 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
<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:[EMAIL PROTECTED]
Sent: Thursday, February 23, 2006 12:46 PM
To: [email protected]
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/[EMAIL PROTECTED] << 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
> 0
order 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.
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.
