If I run the same perl script on the database server itself it runs instantly. (database server is red hat AS3 update 4). I wasn't sure how to check versions of DBI and DBD (I greped for version on every module under every DBI and DBD directory but found to many versions strings and didnt know which was which) but the DBI and DBD::Oracle versions on the database server were the latest release as of 8 months ago or so.
The shell script and perl script are both using the exact same database/user/schema/table. The database itself is idle with no other active sessions. Thanks On 2/24/06, John Scoles <[EMAIL PROTECTED]> wrote: > > 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: dbi-users@perl.org > 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: dbi-users@perl.org > 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. > > > >
