RE: Does primary key structure impact UPDATE performance?

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Mon, 17 May 2004 08:46:51 -0400
Message-ID: <42BBD772AC30EA428B057864E203C999274152@MSGBOSCLF2WIN.DMN1.FMR.COM>

What is the chain_cnt stats on the table?

Waleed

-----Original Message-----
From: Thomas Jeff [mailto:jeff.thomas_at_thomson.net] Sent: Monday, May 17, 2004 7:28 AM
To: oracle-l_at_freelists.org
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_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=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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] 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_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 - 07:44:06 CDT