Ilya, That url gives me a page not found error, or did, until just now Couldn't all day yesterday. The URL at lists.perl.org was different, and didn't work either.. Not that I can get to that, either, right now.
-----Original Message----- From: Sterin, Ilya [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 03, 2002 12:23 PM To: 'Tim Bunce '; Beadles, John-Thomas [RICH1:2795:EXCH] Cc: '[EMAIL PROTECTED] ' Subject: RE: Performance problem with DBI, DBD-Oracle8 Why can't you access the DBI FAQ? www.xmlproj.com/cgi/fom.cgi Ilya -----Original Message----- From: Tim Bunce To: John-Thomas Beadles Cc: [EMAIL PROTECTED] Sent: 10/3/02 3:31 AM Subject: Re: Performance problem with DBI, DBD-Oracle8 Make sure you're using the latest DBD::Oracle. Send a trace(8) log - just the part from prepare thru execute. Tim. On Wed, Oct 02, 2002 at 05:45:01PM -0500, John-Thomas Beadles wrote: > Help! I'm having a severe performance problem with a Perl program talking > to a remote Oracle database. I've read the DBI & DBM perldocs, the O'Reilly > book and can't access the DBI faq. > > To characterize the problem, I've created a small, transportable program > that will run on my PC and on the UNIX workstation where the program has to > run. The program completes in about 20 seconds on my PC, and about 20 > minutes on the UNIX workstation. The program pulls approx. 38k records from > a 1.2M record table. On both, the query appears to prepare and execute > promptly. The problem appears to be fetching the data to the client. I did > a trace that shows the issue. It seems to just take a long time to do all > these fetches to get the results. Is there anything configurable in DBI > that could affect this? The UNIX server is a multi-processor machine, low > CPU utilization, though the disk gets a good workout. > > DBI 1.14-nothread dispatch trace level set to 2 > -> prepare for DBD::Oracle::db (DBI::db=HASH(0x400faf14)~0x400faf44 > 'SELECT target, to_char(timestamp,'YYYY-MM-DD HH:MI:SS') FINISH FROM > reports_generated WHERE data_date = to_date('20020929','YYYYMMDD') ') > <- prepare= DBI::st=HASH(0x400fec5c) at rpts_gen_2.txt line 26. > > Wed Oct 2 17:06:15 2002 - SQL Prepared > > -> execute for DBD::Oracle::st (DBI::st=HASH(0x400fec5c)~0x400faeb4) > dbd_st_execute SELECT (out0, lob0)... > dbd_st_execute SELECT returned (SUCCESS, rpc0, fn4, out0) > fbh 1: 'TARGET' NO null , otype 1-> 5, dbsize 20/21, p20.s0 > fbh 2: 'FINISH' NO null , otype 1-> 5, dbsize 19/20, p19.s0 > <- execute= '0E0' at rpts_gen_2.txt line 30. > -> fetchall_arrayref in DBD::_::st for DBD::Oracle::st > (DBI::st=HASH(0x400fec5c)~0x400faeb4) > 2 -> fetch for DBD::Oracle::st (DBI::st=HASH(0x400faeb4)~INNER) > 2 <- fetch= [ 'XXXXXXXXX1' '2002-09-23 11:30:36' ] at DBI.pm line 1112. > 2 -> fetch for DBD::Oracle::st (DBI::st=HASH(0x400faeb4)~INNER) > > *snip* > > 2 <- fetch= [ 'YYYYYYYYY1' '2002-10-01 06:16:13' ] at DBI.pm line 1112. > 2 -> fetch for DBD::Oracle::st (DBI::st=HASH(0x400faeb4)~INNER) > 2 <- fetch= undef at DBI.pm line 1112. > <- fetchall_arrayref= [ ARRAY(0x400fed7c) ARRAY(0x400feda0) > ARRAY(0x400fedd0) ARRAY(0x400fee00) ARRAY(0x400fee30) ARRAY(0x400fee60) > ARRAY(0x400fee90) ARRAY(0x400feec0) ARRAY(0x400feef0) ARRAY(0x400fef20) > ARRAY(0x400fef50) ARRAY(0x400fef80) ARRAY(0x400fefb0) ARRAY(0x400fefe0) > ARRAY(0x40101034) ARRAY(0x40101064) ARRAY(0x40101094) ARRAY(0x401010c4) > ARRAY(0x401010f4) ARRAY(0x40101124) ARRAY(0x40101154) ARRAY(0x40101184) > ARRAY(0x401011b4) ARRAY(0x401011e4) ARRAY(0x40101214) ARRAY(0x40101244) > ARRAY(0x40101274) ARRAY(0x401012a4) ARRAY(0x401012d4) ARRAY(0x40101304) > ARRAY(0x40101334) ARRAY(0x40101364) ARRAY(0x40101394) ARRAY(0x401013c4) > ARRAY(0x401013f4) > > *snip* > > ARRAY(0x407da244) ARRAY(0x407da274) ARRAY(0x407da2a4) ARRAY(0x407da2d4) > ARRAY(0x407da304) ARRAY(0x407da334) ARRAY(0x407da364) ARRAY(0x407da394) > ARRAY(0x407da3c4) ARRAY(0x407da3f4) ARRAY(0x407db438) ARRAY(0x407db468) > ARRAY(0x407db498) ARRAY(0x407db4c8) ARRAY(0x407db4f8) ARRAY(0x407db528) > ARRAY(0x407db558) ARRAY(0x407db588) ARRAY(0x407db5b8) ARRAY(0x407db5e8) > ARRAY(0x407db618) ARRAY(0x407db648) ARRAY(0x407db678) ] at rpts_gen_2.txt > line 32. > > Wed Oct 2 17:34:45 2002 - Executed > > -> err in DBD::_::common for DBD::Oracle::st > (DBI::st=HASH(0x400fec5c)~0x400faeb4) > <- err= undef at rpts_gen_2.txt line 37. > -> err in DBD::_::common for DBD::Oracle::db > (DBI::db=HASH(0x400faf14)~0x400faf44) > <- err= undef at rpts_gen_2.txt line 38. > -> errstr in DBD::_::common for DBD::Oracle::db > (DBI::db=HASH(0x400faf14)~0x400faf44) > <- errstr= undef at rpts_gen_2.txt line 39. > > Wed Oct 2 17:34:45 2002 - Returned 38726 records > > -> finish for DBD::Oracle::st (DBI::st=HASH(0x400fec5c)~0x400faeb4) > <- finish= 1 at rpts_gen_2.txt line 60. > > Wed Oct 2 17:34:45 2002 - sth released > > -> disconnect for DBD::Oracle::db (DBI::db=HASH(0x400faf14)~0x400faf44) > <- disconnect= 1 at rpts_gen_2.txt line 65. > -- DBI::END > -> disconnect_all for DBD::Oracle::dr > (DBI::dr=HASH(0x400b75b8)~0x400faf5c) > <- disconnect_all= '' at DBI.pm line 450. > -> DESTROY for DBD::Oracle::st (DBI::st=HASH(0x400faeb4)~INNER) > <- DESTROY= undef during global destruction. > -> DESTROY in DBD::_::common for DBD::Oracle::dr > (DBI::dr=HASH(0x400faf5c)~INNER) > <- DESTROY= undef during global destruction. > -> DESTROY for DBD::Oracle::db (DBI::db=HASH(0x400faf44)~INNER) > <- DESTROY= undef during global destruction. > > > > > John T. Beadles > Wireless Network Engineering - Engineering Tools & Processes - Nortel > Networks > 972-685-8713 phone, ESN 445-8713 > 972-684-3626 fax > [EMAIL PROTECTED] > > > >