On 6/7/05 4:06 AM, "Tim Bunce" <[EMAIL PROTECTED]> wrote:
> You've not quoted version numbers for: oracle client, oracle server,
> DBI, or DBD::Oracle.
I am deeply ashamed. It was late in the evening and my frustration
got the better of me...
Client: OS: Solaris 9 current recommended patch level,
Perl: 5.8.6, DBI 1.46, DBD::Oracle 1.15
Oracle Libraries: 8.1.7.0.0
Server: OS: 6.5.17m
Oracle: 7.3.4.0.0 (IRIX Enhancements)
PL/SQL Release: 2.3.4.0.0
( Yes, it's a hideously old legacy server. It's a long story... )
> Some old DBD::Oracle versions did have a problem setting the row cache.
Where "old" is defined as... ? Looking at the dbd changes 1.15->1.16
and DBI 1.46->1.48 I see mention of some row cache edge case fixes...
> Enabling trace level 3 (or higher) will show the auto row cache size
> used (grep for dbd_describe). Setting $dbh->{RowCacheSize} before the
> prepare() will alter the row cache size...
I tried playing with RowCacheSize to no avail - it didn't seem to make
any difference regardless of the setting. If I query after setting I get
the value back that I set, which I take to mean it is being accepted.
The summary of what I saw on a "read system call trace" is that sqlplus
completed in 13sec, performing approximately 1800 reads of about 500 bytes
per read.
The DBD application did 27000 reads of about 100 bytes per read...
so the DBD code ended up transferring about three times the amount
of data and a read for every row. Took about 150sec.
Would the Row Cache setting be expected to change this behavior?
Am I just out in the weeds because the server is Oracle 7 vintage?
the 8.1.7 sqlplus client seems to be able to make it work...
So I'd appreciate any assistance or suggestions as to how to improve
the performance of this beast.
Tim
DBI::db=HASH(0x664efc) trace level set to 0x0/5 (DBI @ 0x0/0) in DBI
1.46-nothread (pid 16525)
-> STORE for DBD::Oracle::db (DBI::db=HASH(0x664efc)~INNER
'RowCacheSize' 5000)
<- STORE= 1 at ./testcase line 516
-> prepare for DBD::Oracle::db (DBI::db=HASH(0x13b748)~0x664efc 'select
e.EMP_NO,e.FIRST,e.MIDDLE,e.LAST from ANSR.EMP_TERM e')
dbih_setup_handle(DBI::st=HASH(0x664ff8)=>DBI::st=HASH(0x6650b8),
DBD::Oracle::st, 665004, Null!)
dbih_make_com(DBI::db=HASH(0x664efc), 665618, DBD::Oracle::st, 208, 0)
thr#0
dbih_setup_attrib(DBI::st=HASH(0x6650b8), Err, DBI::db=HASH(0x664efc))
SCALAR(0x556b34) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x6650b8), State, DBI::db=HASH(0x664efc))
SCALAR(0x556b94) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x6650b8), Errstr,
DBI::db=HASH(0x664efc)) SCALAR(0x556b64) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x6650b8), TraceLevel,
DBI::db=HASH(0x664efc)) 5 (already defined)
dbih_setup_attrib(DBI::st=HASH(0x6650b8), FetchHashKeyName,
DBI::db=HASH(0x664efc)) 'NAME' (already defined)
dbih_setup_attrib(DBI::st=HASH(0x6650b8), HandleSetErr,
DBI::db=HASH(0x664efc)) undef (not defined)
dbih_setup_attrib(DBI::st=HASH(0x6650b8), HandleError,
DBI::db=HASH(0x664efc)) undef (not defined)
dbd_st_prepare'd sql SELECT (pl1, al1, cs1)
dbd_describe SELECT (EXPLICIT, lb 80)...
fbh 1: 'EMP_NO' NULLable, otype 2-> 5, dbsize 22/134, p6.s0
fbh 2: 'FIRST' NO null , otype 1-> 5, dbsize 25/26, p25.s0
fbh 3: 'MIDDLE' NULLable, otype 1-> 5, dbsize 1/2, p1.s0
fbh 4: 'LAST' NO null , otype 1-> 5, dbsize 25/26, p25.s0
dbd_describe'd 4 columns (row bytes: 73 max, 29 est avg, cache: 5000)
<- prepare= DBI::st=HASH(0x664ff8) at ./testit line 564
Tim
--
Tim Kirby
---
