Dose seem a little long even though DBD has to createsome objects ect. I will have a look at it today.
""Reidy, Ron"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] 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.
