Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Does primary key structure impact UPDATE performance?

RE: Does primary key structure impact UPDATE performance?

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Mon, 17 May 2004 10:18:18 -0400
Message-ID: <42BBD772AC30EA428B057864E203C999274156@MSGBOSCLF2WIN.DMN1.FMR.COM> 





This should be found easily by getting the execution plan out of the =
trace file (the STATS lines) and see if there is a UNIQUE SCAN or =
something else.



Waleed



-----Original Message-----


From: Mercadante, Thomas F [mailto:thomas.mercadante_at_labor.state.ny.us]
Sent: Monday, May 17, 2004 10:10 AM


To: 'oracle-l_at_freelists.org'


Subject: RE: Does primary key structure impact UPDATE performance?




Jeff,



I havn't followed the complete thread, so please forgive me if this has =
been


asked before.  You "where" clause has a date column included in it.  Is =
the


bind variable also a "Date" data type or a char string?  If it is a char
string, you may be having data conversion issues and thus missing out on
using the PK index.



I'm sure you've already checked on this, but I thought I would pass a =
simple


observation along.



Hope this helps.



Tom Mercadante



-----Original Message-----


From: Khedr, Waleed [mailto:Waleed.Khedr_at_FMR.COM]=20
Sent: Monday, May 17, 2004 10:04 AM


To: oracle-l_at_freelists.org


Subject: RE: Does primary key structure impact UPDATE performance?




I meant if the columns you are updating are part of any indexes =3D =
(including


the PK)?



Regards,



Waleed



-----Original Message-----


From: Thomas Jeff [mailto:jeff.thomas_at_thomson.net]
Sent: Monday, May 17, 2004 9:56 AM


To: oracle-l_at_freelists.org


Subject: 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=3D3D20 a


date:  value=3D3D3D"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=3D3D3D3D12 =3D


=3D3D


=3D3D3D


date,


and dty=3D3D3D3D1


varchar2 for the subsequent columns:


 WHERE SRC_EFF_FROM_DT =3D3D3D3D :66 AND KEY_ID =3D3D3D3D :67 AND =

SOURCE_ID =3D



=3D3D =3D3D3D3D :68




 bind 65: dty=3D3D3D3D12 mxl=3D3D3D3D07(07) mal=3D3D3D3D00 =
scl=3D3D3D3D00 =3D


pre=3D3D3D3D00 =3D3D oacflg=3D3D3D3D03 =3D3D3D oacfl2=3D3D3D3D0 =
size=3D3D3D3D0


offset=3D3D3D3D8


   bfp=3D3D3D3D11036b238 bln=3D3D3D3D07 avl=3D3D3D3D07 flg=3D3D3D3D01
   value=3D3D3D3D"1/1/1899 0:0:0"


 bind 66: dty=3D3D3D3D1 mxl=3D3D3D3D128(80) mal=3D3D3D3D00 =
scl=3D3D3D3D00 =3D


pre=3D3D3D3D00 =3D3D oacflg=3D3D3D3D03 =3D3D3D oacfl2=3D3D3D3D10 =
size=3D3D3D3D0


offset=3D3D3D3D16


   bfp=3D3D3D3D11036b240 bln=3D3D3D3D128 avl=3D3D3D3D28 flg=3D3D3D3D01
   value=3D3D3D3D"0004021396~0004021396~RG~500"
 bind 67: dty=3D3D3D3D1 mxl=3D3D3D3D32(30) mal=3D3D3D3D00 scl=3D3D3D3D00 =
=3D


pre=3D3D3D3D00 =3D3D oacflg=3D3D3D3D03 =3D3D3D oacfl2=3D3D3D3D10 =
size=3D3D3D3D0


offset=3D3D3D3D144


   bfp=3D3D3D3D11036b2c0 bln=3D3D3D3D32 avl=3D3D3D3D05 flg=3D3D3D3D01

   value=3D3D3D3D"SAPR3"                                 =3D3D3D20







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 Lewis


Sent: Monday, May 17, 2004 7:57 AM


To: oracle-l_at_freelists.org


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>=3D3D3D20



http://www.jlcomp.demon.co.uk/faq/ind_faq.html
<http://www.jlcomp.demon.co.uk/faq/ind_faq.html>=3D3D3D20
The Co-operative Oracle Users' FAQ



http://www.jlcomp.demon.co.uk/seminar.html
<http://www.jlcomp.demon.co.uk/seminar.html>=3D3D3D20
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=3D3D3D3D'db file sequential read' ela=3D3D3D3D 146 =
p1=3D3D3D3D12 =3D


=3D3D p2=3D3D3D3D1831 =3D3D3D p3=3D3D3D3D1 WAIT


#1: nam=3D3D3D3D'db file sequential read' ela=3D3D3D3D 136 p1=3D3D3D3D11 =

=3D


p2=3D3D3D3D27 =3D3D p3=3D3D3D3D1 =3D3D3D WAIT #1: nam=3D3D3D3D'db file =
sequential


read' ela=3D3D3D3D 165 p1=3D3D3D3D49 =3D =3D3D p2=3D3D3D3D207213 =
p3=3D3D3D3D1 =3D3D3D WAIT


#1: nam=3D3D3D3D'db file sequential read' ela=3D3D3D3D =3D 17462 =3D3D =

p1=3D3D3D3D54


p2=3D3D3D3D20918 p3=3D3D3D3D1 =3D3D3D WAIT #1: nam=3D3D3D3D'db file =
sequential =3D


read' =3D3D ela=3D3D3D3D 147 p1=3D3D3D3D54 p2=3D3D3D3D21512 p3=3D3D3D3D1 =
=3D3D3D WAIT



#1: nam=3D3D3D3D'db file =3D sequential =3D3D read' ela=3D3D3D3D 196 =

p1=3D3D3D3D12


p2=3D3D3D3D5854 p3=3D3D3D3D1 WAIT =3D3D3D


#1:



nam=3D3D3D3D'db file sequential read' ela=3D3D3D3D 142 p1=3D3D3D3D54 =3D
p2=3D3D3D3D20726 =3D3D p3=3D3D3D3D1 =3D3D3D WAIT #1: nam=3D3D3D3D'db =
file sequential


read' ela=3D3D3D3D 142 p1=3D3D3D3D54 =3D =3D3D p2=3D3D3D3D21526 =
p3=3D3D3D3D1 =3D3D3D WAIT


#1: nam=3D3D3D3D'db file sequential read' ela=3D3D3D3D =3D 147 =3D3D =

p1=3D3D3D3D12


p2=3D3D3D3D5901 p3=3D3D3D3D1 WAIT =3D3D3D


#1:



nam=3D3D3D3D'db file sequential read' ela=3D3D3D3D 140 p1=3D3D3D3D12 =3D
p2=3D3D3D3D5911 =3D3D p3=3D3D3D3D1 WAIT =3D3D3D


#1:



nam=3D3D3D3D'db file sequential read' ela=3D3D3D3D 153 p1=3D3D3D3D11 =3D
p2=3D3D3D3D326 =3D3D p3=3D3D3D3D1 WAIT =3D3D3D


#1:



nam=3D3D3D3D'db file sequential read' ela=3D3D3D3D 148 p1=3D3D3D3D54 =3D
p2=3D3D3D3D20944 =3D3D p3=3D3D3D3D1 =3D3D3D WAIT #1: nam=3D3D3D3D'db =
file sequential


read' ela=3D3D3D3D 143 p1=3D3D3D3D54 =3D =3D3D p2=3D3D3D3D21969 =
p3=3D3D3D3D1 =3D3D3D WAIT


#1: nam=3D3D3D3D'db file sequential read' ela=3D3D3D3D =3D 151 =3D3D =

p1=3D3D3D3D54


p2=3D3D3D3D20789 p3=3D3D3D3D1 =3D3D3D WAIT #1: nam=3D3D3D3D'db file =
sequential =3D


read' =3D3D ela=3D3D3D3D 144 p1=3D3D3D3D54 p2=3D3D3D3D20755 p3=3D3D3D3D1 =
=3D3D3D WAIT



#1: nam=3D3D3D3D'db file =3D sequential =3D3D read' ela=3D3D3D3D 143 =

p1=3D3D3D3D12


p2=3D3D3D3D6052 p3=3D3D3D3D1 WAIT =3D3D3D


#1:



nam=3D3D3D3D'db file sequential read' ela=3D3D3D3D 137 p1=3D3D3D3D11 =3D
p2=3D3D3D3D415 =3D3D p3=3D3D3D3D1 WAIT =3D3D3D


#1:



nam=3D3D3D3D'db file sequential read' ela=3D3D3D3D 137 p1=3D3D3D3D54 =3D
p2=3D3D3D3D425 =3D3D p3=3D3D3D3D1 WAIT =3D3D3D


#1:



nam=3D3D3D3D'db file sequential read' ela=3D3D3D3D 124 p1=3D3D3D3D11 =3D
p2=3D3D3D3D426 =3D3D p3=3D3D3D3D1 WAIT =3D3D3D


#1:



nam=3D3D3D3D'db file sequential read' ela=3D3D3D3D 174 p1=3D3D3D3D54 =3D
p2=3D3D3D3D18965 =3D3D p3=3D3D3D3D1 =3D3D3D EXEC


#1:c=3D3D3D3D1720000,e=3D3D3D3D1691529,p=3D3D3D3D20,cr=3D3D3D3D2371,cu=3D=

3D3D3D1,mi=3D


s=3D3D3D3D0,r=3D3D3D=3D3D


3D1,dep=3D3D3D3D0


,=3D3D3D


og=3D3D3D3D4,tim=3D3D3D3D105920


2213148331 =3D3D3D20



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.   =3D
p1=3D3D3D3D11/12 =3D3D


=3D3D3D


is


the index, p1=3D3D3D3D54


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>=3D3D3D20




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/
<http://www.freelists.org/archives/oracle-l/>=3D3D3D20
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
<http://www.freelists.org/help/fom-serve/cache/1.html>=3D3D3D20
-----------------------------------------------------------------=3D3D3D2=
0


----------------------------------------------------------------
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
-----------------------------------------------------------------
----------------------------------------------------------------
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 - 09:16:41 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US