We're upgrading from 8i to 10g (using materialized views and more).
We're having strange issues with simple inserts and updates.
Here's what we observed:
For the foll. query
UPDATE job_descr SET phase_id = 2 WHERE jobid = 11576242
1. Runs in a fraction of a second from Toad
2. Runs in a fraction of a second from sqlplus
3. Takes 5 seconds(!) using Oracle 10gR2, DBI 1.53, DBD::Oracle
1.18
The code is essentially as follows:
my $sql = "SELECT ...";
my $sth = $dbh->prepare( $sql ) || die "Preparing $sql\n";
$sth->execute || die "Died: $sql\n";
A DBI Trace around the $sth->execute where the 5s is spent shows the
foll:
DBI 1.53-nothread default trace level set to 0x0/10 (pid 24811)
>> prepare DISPATCH (DBI::db=HASH(0x9ca599c) rc1/2 @2 g0 ima2201
pid#24811) at test.pl line 30 via at test.pl line 20
-> prepare for DBD::Oracle::db (DBI::db=HASH(0x9ca599c)~0x9ca84cc
'UPDATE job_descr SET phase_id = 2, status_id = 10039
WHERE jobid = 11576242')
New DBI::st (for DBD::Oracle::st, parent=DBI::db=HASH(0x9ca84cc),
id=)
dbih_setup_handle(DBI::st=HASH(0x9ca85f8)=>DBI::st=HASH(0x9ca8a10),
DBD::Oracle::st, 9ca8604, Null!)
dbih_make_com(DBI::db=HASH(0x9ca84cc), 9ca8790, DBD::Oracle::st,
216, 0) thr#0
dbih_setup_attrib(DBI::st=HASH(0x9ca8a10), Err,
DBI::db=HASH(0x9ca84cc)) SCALAR(0x9abf0f4) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x9ca8a10), State,
DBI::db=HASH(0x9ca84cc)) SCALAR(0x9abf154) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x9ca8a10), Errstr,
DBI::db=HASH(0x9ca84cc)) SCALAR(0x9abf124) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x9ca8a10), TraceLevel,
DBI::db=HASH(0x9ca84cc)) 0 (already defined)
dbih_setup_attrib(DBI::st=HASH(0x9ca8a10), FetchHashKeyName,
DBI::db=HASH(0x9ca84cc)) 'NAME' (already defined)
dbih_setup_attrib(DBI::st=HASH(0x9ca8a10), HandleSetErr,
DBI::db=HASH(0x9ca84cc)) undef (not defined)
dbih_setup_attrib(DBI::st=HASH(0x9ca8a10), HandleError,
DBI::db=HASH(0x9ca84cc)) undef (not defined)
OCIHandleAlloc(9cc5208,9d05d80,OCI_HTYPE_STMT,0,0)=SUCCESS
OCIStmtPrepare(9ce426c,9cdbf90,'UPDATE job_descr SET phase_id =
2, status_id = 10039
WHERE jobid = 11576242',91,1,0)=SUCCESS
OCIAttrGet(9ce426c,OCI_HTYPE_STMT,9d05d84,0,24,9cdbf90)=SUCCESS
dbd_st_prepare'd sql UPDATE (pl1, auto_lob1, check_sql1)
dbd_describe skipped for UPDATE
<- prepare= DBI::st=HASH(0x9ca85f8) at test.pl line 30 via at
test.pl line 20
Before execute
>> execute DISPATCH (DBI::st=HASH(0x9ca85f8) rc1/1 @1 g0 ima1041
pid#24811) at test.pl line 33 via at test.pl line 20
-> execute for DBD::Oracle::st (DBI::st=HASH(0x9ca85f8)~0x9ca8a10)
dbd_st_execute UPDATE (out0, lob0)...
HERE IS WHERE THE CONNECTION SEEMS TO BLOCK
OCIStmtExecute(9cdbf1c,9ce426c,9cdbf90,1,0,0,0,32)=SUCCESS
OCIAttrGet(9ce426c,OCI_HTYPE_STMT,bfffa7b8,0,9,9cdbf90)=SUCCESS
OCIAttrGet(9ce426c,OCI_HTYPE_STMT,bfffa7b6,0,10,9cdbf90)=SUCCESS
dbd_st_execute UPDATE returned (SUCCESS, rpc1, fn5, out0)
<- execute= 1 at test.pl line 33 via at test.pl line 20
After execute
>> DESTROY DISPATCH (DBI::st=HASH(0x9ca85f8) rc1/1 @1 g0 ima4
pid#24811) at test.pl line 20 via at test.pl line 20
<> DESTROY(DBI::st=HASH(0x9ca85f8)) ignored for outer handle (inner
DBI::st=HASH(0x9ca8a10) has ref cnt 1)
>> DESTROY DISPATCH (DBI::st=HASH(0x9ca8a10) rc1/1 @1 g0 ima4
pid#24811) at test.pl line 20 via at test.pl line 20
-> DESTROY for DBD::Oracle::st (DBI::st=HASH(0x9ca8a10)~INNER)
dbd_st_destroy
OCIHandleFree(9ce426c,OCI_HTYPE_STMT)=SUCCESS
<- DESTROY= undef at test.pl line 20 via at test.pl line 20
DESTROY (dbih_clearcom) (sth 0x9ca8a10, com 0x9d05d00, imp
DBD::Oracle::st):
FLAGS 0x182591: COMSET Warn RaiseError PrintError PrintWarn
ShowErrorStatement LongTruncOk
PARENT DBI::db=HASH(0x9ca84cc)
KIDS 0 (0 Active)
IMP_DATA undef
LongReadLen 64000
NUM_OF_FIELDS -1
NUM_OF_PARAMS 0
dbih_clearcom 0x9ca8a10 (com 0x9d05d00, type 3) done.
ELAPSED: 5.100781
Any help will be much appreciated.
