RE: Does primary key structure impact UPDATE performance?

  • From: "Khedr, Waleed" <Waleed.Khedr@xxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 17 May 2004 08:46:51 -0400

What is the chain_cnt stats on the table?

Waleed

-----Original Message-----
From: Thomas Jeff [mailto:jeff.thomas@xxxxxxxxxxx]
Sent: Monday, May 17, 2004 7:28 AM
To: oracle-l@xxxxxxxxxxxxx
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.


Contrast to the better performing table:

WAIT #3: nam=3D'db file sequential read' ela=3D 163 p1=3D54 p2=3D20472 =
p3=3D1
WAIT #3: nam=3D'db file sequential read' ela=3D 153 p1=3D49 p2=3D236069 =
p3=3D1
EXEC
#3:c=3D10000,e=3D7225,p=3D2,cr=3D2,cu=3D1,mis=3D0,r=3D1,dep=3D0,og=3D4,ti=
m=3D105920221364228
1 =20








-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Jared Still
Sent: Monday, May 17, 2004 12:22 AM
To: Oracle-L Freelists
Subject: Re: Does primary key structure impact UPDATE performance?


On Sun, 2004-05-16 at 19:07, Thomas Jeff wrote:
> >From the 10046 traces, the performance of the update on the first
> >table
> is clearly far better then on the 2nd table.  Just one update on the
> 2nd table is taking an average of 2 seconds to complete. =20

Is that one row that is taking 2 seconds?

If not, how many blocks are affected?
(indexes and table)

Jared


----------------------------------------------------------------
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
-----------------------------------------------------------------
----------------------------------------------------------------
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: