RE: Slow Performance When Using DBI, otherwise Not
- From: Ron.Reidy@xxxxxxxxxxxxxxxxxx (Ron Reidy)
- Date: Thu, 23 Feb 2006 12:59:10 -0700
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.
.
- Follow-Ups:
- Re: Slow Performance When Using DBI, otherwise Not
- From: Rhugga Harper
- Re: Slow Performance When Using DBI, otherwise Not
- Prev by Date: Using Kerberos enabled connections with Sybase
- 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
|
