| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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='db file sequential read' ela= 146 p1=12 p2=1831 p3=1 WAIT #1: nam='db file sequential read' ela= 136 p1=11 p2=27 p3=1 WAIT #1: nam='db file sequential read' ela= 165 p1=49 p2=207213 p3=1 WAIT #1: nam='db file sequential read' ela= 17462 p1=54 p2=20918 p3=1 WAIT #1: nam='db file sequential read' ela= 147 p1=54 p2=21512 p3=1 WAIT #1: nam='db file sequential read' ela= 196 p1=12 p2=5854 p3=1 WAIT #1: nam='db file sequential read' ela= 142 p1=54 p2=20726 p3=1 WAIT #1: nam='db file sequential read' ela= 142 p1=54 p2=21526 p3=1 WAIT #1: nam='db file sequential read' ela= 147 p1=12 p2=5901 p3=1 WAIT #1: nam='db file sequential read' ela= 140 p1=12 p2=5911 p3=1 WAIT #1: nam='db file sequential read' ela= 153 p1=11 p2=326 p3=1 WAIT #1: nam='db file sequential read' ela= 148 p1=54 p2=20944 p3=1 WAIT #1: nam='db file sequential read' ela= 143 p1=54 p2=21969 p3=1 WAIT #1: nam='db file sequential read' ela= 151 p1=54 p2=20789 p3=1 WAIT #1: nam='db file sequential read' ela= 144 p1=54 p2=20755 p3=1 WAIT #1: nam='db file sequential read' ela= 143 p1=12 p2=6052 p3=1 WAIT #1: nam='db file sequential read' ela= 137 p1=11 p2=415 p3=1 WAIT #1: nam='db file sequential read' ela= 137 p1=54 p2=425 p3=1 WAIT #1: nam='db file sequential read' ela= 124 p1=11 p2=426 p3=1 WAIT #1: nam='db file sequential read' ela= 174 p1=54 p2=18965 p3=1EXEC
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=11/12 is
the index, p1=54
is the table. The e, and cu/cr values are typical.
Contrast to the better performing table:
WAIT #3: nam='db file sequential read' ela= 163 p1=54 p2=20472 p3=1
WAIT #3: nam='db file sequential read' ela= 153 p1=49 p2=236069 p3=1
EXEC
#3:c=10000,e=7225,p=2,cr=2,cu=1,mis=0,r=1,dep=0,og=4,tim=105920221364228
1
-----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.
Is that one row that is taking 2 seconds?
If not, how many blocks are affected?
(indexes and table)
Jared
-- 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 - 06:25:38 CDT
 |
 |