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