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
> 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.
>
>
