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.
