Re: Does primary key structure impact UPDATE performance?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 17 May 2004 13:56:51 +0100
Message-ID: <00a701c43c0e$7007bae0$7102a8c0@Primary>

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/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated May 1st

Original Message ----- From: "Thomas Jeff" <jeff.thomas_at_thomson.net> To: <oracle-l_at_freelists.org> Sent: Monday, May 17, 2004 12:28 PM 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='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=1

EXEC
#1:c=1720000,e=1691529,p=20,cr=2371,cu=1,mis=0,r=1,dep=0,og=4,tim=105920 2213148331

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.

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:53:55 CDT