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]
>
>
>
>
