I would have to agree with Ron on this that is it someting in your Oracle that is going awry. Perhaps you are missing an index someplace. Looking at the code trace you included there is nothing in DBD::Oracle 1.18 that has changed in what you are hitting since 1.16. Can you try using DBD::Oracle 1.17 and see what results you are getting?
""Reidy, Ron"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] Have you performed a 10046 trace? What (if any) init parameters were changed prior to moving to 10g? Do you have up to date CBO stats on your tables and data dictionary? -- Ron Reidy Lead DBA Array BioPharma, Inc. -----Original Message----- From: Sanjay Noronha [mailto:[EMAIL PROTECTED] Sent: Monday, November 06, 2006 7:47 PM To: dbi-users@perl.org Subject: Insert/Update performance issues with Oracle 10gR2 + DBI + DBD::Oracle 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. This electronic message transmission is a PRIVATE communication which contains information which may be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. Please notify the sender of the delivery error by replying to this message, or notify us by telephone (877-633-2436, ext. 0), and then delete it from your system.
