RE: Does primary key structure impact UPDATE performance?
- From: "Khedr, Waleed" <Waleed.Khedr@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 17 May 2004 09:36:15 -0400
Are the updated columns indexed?
Waleed
-----Original Message-----
From: Thomas Jeff [mailto:jeff.thomas@xxxxxxxxxxx]
Sent: Monday, May 17, 2004 9:29 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Does primary key structure impact UPDATE performance?
Thanks Jonathan. However, the datatypes are correct, i.e. dty=3D12 =
date,
and dty=3D1
varchar2 for the subsequent columns:
WHERE SRC_EFF_FROM_DT =3D :66 AND KEY_ID =3D :67 AND SOURCE_ID =3D :68
bind 65: dty=3D12 mxl=3D07(07) mal=3D00 scl=3D00 pre=3D00 oacflg=3D03 =
oacfl2=3D0
size=3D0 offset=3D8
bfp=3D11036b238 bln=3D07 avl=3D07 flg=3D01
value=3D"1/1/1899 0:0:0"
bind 66: dty=3D1 mxl=3D128(80) mal=3D00 scl=3D00 pre=3D00 oacflg=3D03 =
oacfl2=3D10
size=3D0 offset=3D16
bfp=3D11036b240 bln=3D128 avl=3D28 flg=3D01
value=3D"0004021396~0004021396~RG~500"
bind 67: dty=3D1 mxl=3D32(30) mal=3D00 scl=3D00 pre=3D00 oacflg=3D03 =
oacfl2=3D10
size=3D0 offset=3D144
bfp=3D11036b2c0 bln=3D32 avl=3D05 flg=3D01
value=3D"SAPR3" =20
Waleed: No chaining. Thought of that early on.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx
<mailto:oracle-l-bounce@xxxxxxxxxxxxx> ] On Behalf Of Jonathan Lewis
Sent: Monday, May 17, 2004 7:57 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Does primary key structure impact UPDATE performance?
Judging by the number of table blocks you read
to update a single row, it looks as if your index
is not engineered to meet the needs of the update,
and is currently doing something along the lines of:
check 250,000 rows in the index leaf blocks,
and eliminate 249,950 of them, then check 50
rows against the table to find the right one.
If you think the code looks as if it is supplying
all the columns of the index, check the types of
the incoming bind variables to make sure they
match the types of the columns. You may be
losing index precision because of a coercion
problem.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk <http://www.jlcomp.demon.co.uk>=20
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
<http://www.jlcomp.demon.co.uk/faq/ind_faq.html>=20
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html
<http://www.jlcomp.demon.co.uk/seminar.html>=20
Optimising Oracle Seminar - schedule updated May 1st
----- Original Message -----
From: "Thomas Jeff" <jeff.thomas@xxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Monday, May 17, 2004 12:28 PM
Subject: FW: Does primary key structure impact UPDATE performance?
See if this is easier to read.
-----Original Message-----
From: Thomas Jeff
Sent: Monday, May 17, 2004 6:25 AM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: RE: Does primary key structure impact UPDATE performance?
Yes, that's one row at a time. There are no other indexes other then
the PK
constraint. TA sample from the trace (following the binds) is like
this:
WAIT #1: nam=3D'db file sequential read' ela=3D 146 p1=3D12 p2=3D1831 =
p3=3D1 WAIT
#1: nam=3D'db file sequential read' ela=3D 136 p1=3D11 p2=3D27 p3=3D1 =
WAIT #1:
nam=3D'db file sequential read' ela=3D 165 p1=3D49 p2=3D207213 p3=3D1 =
WAIT #1:
nam=3D'db file sequential read' ela=3D 17462 p1=3D54 p2=3D20918 p3=3D1 =
WAIT #1:
nam=3D'db file sequential read' ela=3D 147 p1=3D54 p2=3D21512 p3=3D1 =
WAIT #1:
nam=3D'db file sequential read' ela=3D 196 p1=3D12 p2=3D5854 p3=3D1 WAIT =
#1:
nam=3D'db file sequential read' ela=3D 142 p1=3D54 p2=3D20726 p3=3D1 =
WAIT #1:
nam=3D'db file sequential read' ela=3D 142 p1=3D54 p2=3D21526 p3=3D1 =
WAIT #1:
nam=3D'db file sequential read' ela=3D 147 p1=3D12 p2=3D5901 p3=3D1 WAIT =
#1:
nam=3D'db file sequential read' ela=3D 140 p1=3D12 p2=3D5911 p3=3D1 WAIT =
#1:
nam=3D'db file sequential read' ela=3D 153 p1=3D11 p2=3D326 p3=3D1 WAIT =
#1:
nam=3D'db file sequential read' ela=3D 148 p1=3D54 p2=3D20944 p3=3D1 =
WAIT #1:
nam=3D'db file sequential read' ela=3D 143 p1=3D54 p2=3D21969 p3=3D1 =
WAIT #1:
nam=3D'db file sequential read' ela=3D 151 p1=3D54 p2=3D20789 p3=3D1 =
WAIT #1:
nam=3D'db file sequential read' ela=3D 144 p1=3D54 p2=3D20755 p3=3D1 =
WAIT #1:
nam=3D'db file sequential read' ela=3D 143 p1=3D12 p2=3D6052 p3=3D1 WAIT =
#1:
nam=3D'db file sequential read' ela=3D 137 p1=3D11 p2=3D415 p3=3D1 WAIT =
#1:
nam=3D'db file sequential read' ela=3D 137 p1=3D54 p2=3D425 p3=3D1 WAIT =
#1:
nam=3D'db file sequential read' ela=3D 124 p1=3D11 p2=3D426 p3=3D1 WAIT =
#1:
nam=3D'db file sequential read' ela=3D 174 p1=3D54 p2=3D18965 p3=3D1 =
EXEC
#1:c=3D1720000,e=3D1691529,p=3D20,cr=3D2371,cu=3D1,mis=3D0,r=3D1,dep=3D0,=
og=3D4,tim=3D105920
2213148331 =20
If I'm reading this correctly, elapsed time was 1.7 seconds, required 20
PIO's and
2372 LIO's to execute this UPDATE, which updated one row. p1=3D11/12 =
is
the index, p1=3D54
is the table. The e, and cu/cr values are typical.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
<http://www.orafaq.com>=20
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put
'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
<//www.freelists.org/archives/oracle-l/>=20
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
<//www.freelists.org/help/fom-serve/cache/1.html>=20
-----------------------------------------------------------------=20
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
