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]
