| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Does primary key structure impact UPDATE performance?
Nope, no extra indexes other then the PK columns.
Question: Looking at one of the bind variable values which maps to=20 a date: value=3D"1/1/1899 0:0:0"
0:0:0 is not a valid time. How would this statement succeed then?
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Khedr, Waleed
Sent: Monday, May 17, 2004 8:36 AM
To: oracle-l_at_freelists.org
Subject: RE: Does primary key structure impact UPDATE performance?
Are the updated columns indexed?
Waleed
-----Original Message-----
From: Thomas Jeff [mailto:jeff.thomas_at_thomson.net]
Sent: Monday, May 17, 2004 9:29 AM
To: oracle-l_at_freelists.org
Subject: RE: Does primary key structure impact UPDATE performance?
Thanks Jonathan. However, the datatypes are correct, i.e. dty=3D3D12 =
=3D
date,
and dty=3D3D1
varchar2 for the subsequent columns:
WHERE SRC_EFF_FROM_DT =3D3D :66 AND KEY_ID =3D3D :67 AND SOURCE_ID =
=3D3D :68
bind 65: dty=3D3D12 mxl=3D3D07(07) mal=3D3D00 scl=3D3D00 pre=3D3D00 =
oacflg=3D3D03 =3D oacfl2=3D3D0 size=3D3D0 offset=3D3D8 bfp=3D3D11036b238 bln=3D3D07 avl=3D3D07 flg=3D3D01value=3D3D"1/1/1899 0:0:0"
oacflg=3D3D03 =3D oacfl2=3D3D10 size=3D3D0 offset=3D3D16 bfp=3D3D11036b240 bln=3D3D128 avl=3D3D28 flg=3D3D01value=3D3D"0004021396~0004021396~RG~500" bind 67: dty=3D3D1 mxl=3D3D32(30) mal=3D3D00 scl=3D3D00 pre=3D3D00 =
oacflg=3D3D03 =3D oacfl2=3D3D10 size=3D3D0 offset=3D3D144 bfp=3D3D11036b2c0 bln=3D3D32 avl=3D3D05 flg=3D3D01 value=3D3D"SAPR3" =3D20
Waleed: No chaining. Thought of that early on.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org <mailto:oracle-l-bounce_at_freelists.org> ] On Behalf Of Jonathan LewisSent: Monday, May 17, 2004 7:57 AM
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>=3D20
http://www.jlcomp.demon.co.uk/faq/ind_faq.html <http://www.jlcomp.demon.co.uk/faq/ind_faq.html>=3D20 The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html <http://www.jlcomp.demon.co.uk/seminar.html>=3D20 Optimising Oracle Seminar - schedule updated May 1st
See if this is easier to read.
-----Original Message-----
From: Thomas Jeff
Sent: Monday, May 17, 2004 6:25 AM
To: 'oracle-l_at_freelists.org'
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=3D3D'db file sequential read' ela=3D3D 146 p1=3D3D12 =
p2=3D3D1831 =3D
p3=3D3D1 WAIT
#1: nam=3D3D'db file sequential read' ela=3D3D 136 p1=3D3D11 p2=3D3D27 =
p3=3D3D1 =3D
WAIT #1: nam=3D3D'db file sequential read' ela=3D3D 165 p1=3D3D49 =
p2=3D3D207213 p3=3D3D1 =3D WAIT #1: nam=3D3D'db file sequential read' ela=3D3D 17462 = p1=3D3D54 p2=3D3D20918 p3=3D3D1 =3D WAIT #1: nam=3D3D'db file sequential read' =ela=3D3D 147
p2=3D3D21526 p3=3D3D1 =3D WAIT #1: nam=3D3D'db file sequential read' ela=3D3D 147 = p1=3D3D12 p2=3D3D5901 p3=3D3D1 WAIT =3D
p2=3D3D21969 p3=3D3D1 =3D WAIT #1: nam=3D3D'db file sequential read' ela=3D3D 151 = p1=3D3D54 p2=3D3D20789 p3=3D3D1 =3D WAIT #1: nam=3D3D'db file sequential read' =ela=3D3D 144
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=3D3D11/12 =
=3D
is
the index, p1=3D3D54
is the table. The e, and cu/cr values are typical.
-- Archives are at http://www.freelists.org/archives/oracle-l/ <http://www.freelists.org/archives/oracle-l/>=3D20 FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html <http://www.freelists.org/help/fom-serve/cache/1.html>=3D20 -----------------------------------------------------------------=3D20 ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://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_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://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_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Mon May 17 2004 - 08:52:47 CDT
 |
 |