Insert/Update performance issues with Oracle 10gR2 + DBI + DBD::Oracle
Insert/Update performance issues with Oracle 10gR2 + DBI + DBD::Oracle
am 07.11.2006 03:47:25 von SNoronha
------_=_NextPart_001_01C70217.0E72C6E5
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
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 =3D 2 WHERE jobid =3D 11576242
=20
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=20
=20
The code is essentially as follows:
=20
my $sql =3D "SELECT ...";
my $sth =3D $dbh->prepare( $sql ) || die "Preparing $sql\n";
$sth->execute || die "Died: $sql\n";
=20
A DBI Trace around the $sth->execute where the 5s is spent shows the
foll:
=20
=20
DBI 1.53-nothread default trace level set to 0x0/10 (pid 24811)
>> prepare DISPATCH (DBI::db=3DHASH(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=3DHASH(0x9ca599c)~0x9ca84cc
'UPDATE job_descr SET phase_id =3D 2, status_id =3D 10039
WHERE jobid =3D 11576242')
New DBI::st (for DBD::Oracle::st, =
parent=3DDBI::db=3DHASH(0x9ca84cc),
id=3D)
=
dbih_setup_handle(DBI::st=3DHASH(0x9ca85f8)=3D>DBI::st=3DHAS H(0x9ca8a10),=
DBD::Oracle::st, 9ca8604, Null!)
dbih_make_com(DBI::db=3DHASH(0x9ca84cc), 9ca8790, DBD::Oracle::st,
216, 0) thr#0
dbih_setup_attrib(DBI::st=3DHASH(0x9ca8a10), Err,
DBI::db=3DHASH(0x9ca84cc)) SCALAR(0x9abf0f4) (already defined)
dbih_setup_attrib(DBI::st=3DHASH(0x9ca8a10), State,
DBI::db=3DHASH(0x9ca84cc)) SCALAR(0x9abf154) (already defined)
dbih_setup_attrib(DBI::st=3DHASH(0x9ca8a10), Errstr,
DBI::db=3DHASH(0x9ca84cc)) SCALAR(0x9abf124) (already defined)
dbih_setup_attrib(DBI::st=3DHASH(0x9ca8a10), TraceLevel,
DBI::db=3DHASH(0x9ca84cc)) 0 (already defined)
dbih_setup_attrib(DBI::st=3DHASH(0x9ca8a10), FetchHashKeyName,
DBI::db=3DHASH(0x9ca84cc)) 'NAME' (already defined)
dbih_setup_attrib(DBI::st=3DHASH(0x9ca8a10), HandleSetErr,
DBI::db=3DHASH(0x9ca84cc)) undef (not defined)
dbih_setup_attrib(DBI::st=3DHASH(0x9ca8a10), HandleError,
DBI::db=3DHASH(0x9ca84cc)) undef (not defined)
OCIHandleAlloc(9cc5208,9d05d80,OCI_HTYPE_STMT,0,0)=3DSUCCESS
OCIStmtPrepare(9ce426c,9cdbf90,'UPDATE job_descr SET phase_id =
=3D
2, status_id =3D 10039
WHERE jobid =3D 11576242',91,1,0)=3DSUCCESS
=
OCIAttrGet(9ce426c,OCI_HTYPE_STMT,9d05d84,0,24,9cdbf90)=3DSU CCESS
dbd_st_prepare'd sql UPDATE (pl1, auto_lob1, check_sql1)
dbd_describe skipped for UPDATE
<- prepare=3D DBI::st=3DHASH(0x9ca85f8) at test.pl line 30 via at
test.pl line 20
=20
=20
Before execute
>> execute DISPATCH (DBI::st=3DHASH(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=3DHASH(0x9ca85f8)~0x9ca8a10)
dbd_st_execute UPDATE (out0, lob0)...=20
HERE IS WHERE THE CONNECTION SEEMS TO BLOCK
OCIStmtExecute(9cdbf1c,9ce426c,9cdbf90,1,0,0,0,32)=3DSUCCESS
=
OCIAttrGet(9ce426c,OCI_HTYPE_STMT,bfffa7b8,0,9,9cdbf90)=3DSU CCESS
=
OCIAttrGet(9ce426c,OCI_HTYPE_STMT,bfffa7b6,0,10,9cdbf90)=3DS UCCESS
dbd_st_execute UPDATE returned (SUCCESS, rpc1, fn5, out0)
<- execute=3D 1 at test.pl line 33 via at test.pl line 20
After execute
=20
=20
>> DESTROY DISPATCH (DBI::st=3DHASH(0x9ca85f8) rc1/1 @1 g0 ima4
pid#24811) at test.pl line 20 via at test.pl line 20
<> DESTROY(DBI::st=3DHASH(0x9ca85f8)) ignored for outer handle =
(inner
DBI::st=3DHASH(0x9ca8a10) has ref cnt 1)
>> DESTROY DISPATCH (DBI::st=3DHASH(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=3DHASH(0x9ca8a10)~INNER)
dbd_st_destroy
OCIHandleFree(9ce426c,OCI_HTYPE_STMT)=3DSUCCESS
<- DESTROY=3D 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=3DHASH(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.
=20
ELAPSED: 5.100781
=20
=20
Any help will be much appreciated.
=20
------_=_NextPart_001_01C70217.0E72C6E5--
RE: Insert/Update performance issues with Oracle 10gR2 + DBI + DBD::Oracle
am 07.11.2006 17:23:02 von Ron.Reidy
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:SNoronha@Elance.com]=20
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 =3D 2 WHERE jobid =3D 11576242
=20
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=20
=20
The code is essentially as follows:
=20
my $sql =3D "SELECT ...";
my $sth =3D $dbh->prepare( $sql ) || die "Preparing $sql\n";
$sth->execute || die "Died: $sql\n";
=20
A DBI Trace around the $sth->execute where the 5s is spent shows the
foll:
=20
=20
DBI 1.53-nothread default trace level set to 0x0/10 (pid 24811)
>> prepare DISPATCH (DBI::db=3DHASH(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=3DHASH(0x9ca599c)~0x9ca84cc
'UPDATE job_descr SET phase_id =3D 2, status_id =3D 10039
WHERE jobid =3D 11576242')
New DBI::st (for DBD::Oracle::st, =
parent=3DDBI::db=3DHASH(0x9ca84cc),
id=3D)
=
dbih_setup_handle(DBI::st=3DHASH(0x9ca85f8)=3D>DBI::st=3DHAS H(0x9ca8a10),=
DBD::Oracle::st, 9ca8604, Null!)
dbih_make_com(DBI::db=3DHASH(0x9ca84cc), 9ca8790, DBD::Oracle::st,
216, 0) thr#0
dbih_setup_attrib(DBI::st=3DHASH(0x9ca8a10), Err,
DBI::db=3DHASH(0x9ca84cc)) SCALAR(0x9abf0f4) (already defined)
dbih_setup_attrib(DBI::st=3DHASH(0x9ca8a10), State,
DBI::db=3DHASH(0x9ca84cc)) SCALAR(0x9abf154) (already defined)
dbih_setup_attrib(DBI::st=3DHASH(0x9ca8a10), Errstr,
DBI::db=3DHASH(0x9ca84cc)) SCALAR(0x9abf124) (already defined)
dbih_setup_attrib(DBI::st=3DHASH(0x9ca8a10), TraceLevel,
DBI::db=3DHASH(0x9ca84cc)) 0 (already defined)
dbih_setup_attrib(DBI::st=3DHASH(0x9ca8a10), FetchHashKeyName,
DBI::db=3DHASH(0x9ca84cc)) 'NAME' (already defined)
dbih_setup_attrib(DBI::st=3DHASH(0x9ca8a10), HandleSetErr,
DBI::db=3DHASH(0x9ca84cc)) undef (not defined)
dbih_setup_attrib(DBI::st=3DHASH(0x9ca8a10), HandleError,
DBI::db=3DHASH(0x9ca84cc)) undef (not defined)
OCIHandleAlloc(9cc5208,9d05d80,OCI_HTYPE_STMT,0,0)=3DSUCCESS
OCIStmtPrepare(9ce426c,9cdbf90,'UPDATE job_descr SET phase_id =
=3D
2, status_id =3D 10039
WHERE jobid =3D 11576242',91,1,0)=3DSUCCESS
=
OCIAttrGet(9ce426c,OCI_HTYPE_STMT,9d05d84,0,24,9cdbf90)=3DSU CCESS
dbd_st_prepare'd sql UPDATE (pl1, auto_lob1, check_sql1)
dbd_describe skipped for UPDATE
<- prepare=3D DBI::st=3DHASH(0x9ca85f8) at test.pl line 30 via at
test.pl line 20
=20
=20
Before execute
>> execute DISPATCH (DBI::st=3DHASH(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=3DHASH(0x9ca85f8)~0x9ca8a10)
dbd_st_execute UPDATE (out0, lob0)...=20
HERE IS WHERE THE CONNECTION SEEMS TO BLOCK
OCIStmtExecute(9cdbf1c,9ce426c,9cdbf90,1,0,0,0,32)=3DSUCCESS
=
OCIAttrGet(9ce426c,OCI_HTYPE_STMT,bfffa7b8,0,9,9cdbf90)=3DSU CCESS
=
OCIAttrGet(9ce426c,OCI_HTYPE_STMT,bfffa7b6,0,10,9cdbf90)=3DS UCCESS
dbd_st_execute UPDATE returned (SUCCESS, rpc1, fn5, out0)
<- execute=3D 1 at test.pl line 33 via at test.pl line 20
After execute
=20
=20
>> DESTROY DISPATCH (DBI::st=3DHASH(0x9ca85f8) rc1/1 @1 g0 ima4
pid#24811) at test.pl line 20 via at test.pl line 20
<> DESTROY(DBI::st=3DHASH(0x9ca85f8)) ignored for outer handle =
(inner
DBI::st=3DHASH(0x9ca8a10) has ref cnt 1)
>> DESTROY DISPATCH (DBI::st=3DHASH(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=3DHASH(0x9ca8a10)~INNER)
dbd_st_destroy
OCIHandleFree(9ce426c,OCI_HTYPE_STMT)=3DSUCCESS
<- DESTROY=3D 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=3DHASH(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.
=20
ELAPSED: 5.100781
=20
=20
Any help will be much appreciated.
=20
This electronic message transmission is a PRIVATE communication which =
contains
information which may be confidential or privileged. The information is =
intended=20
to be for the use of the individual or entity named above. If you are =
not the=20
intended recipient, please be aware that any disclosure, copying, =
distribution=20
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.
Re: Insert/Update performance issues with Oracle 10gR2 + DBI + DBD::Oracle
am 08.11.2006 15:22:51 von scoles
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"" wrote in message
news:7209E76DACFED9469D4F5169F9880C7A28356C@mail01bldr.array bp.com...
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:SNoronha@Elance.com]
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(0x9c a8a10),
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)=SUCC ESS
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)=SUCC ESS
OCIAttrGet(9ce426c,OCI_HTYPE_STMT,bfffa7b6,0,10,9cdbf90)=SUC CESS
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.