Re: Does primary key structure impact UPDATE performance?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 17 May 2004 21:37:17 +0100
Message-ID: <010201c43c4e$be54a330$7102a8c0@Primary>

If the excess CPU were to do with undo being applied, all the undo blocks visited would also be recorded under the "query" count for the fetch.

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: "Tanel Põder" <tanel.poder.003_at_mail.ee> To: <oracle-l_at_freelists.org> Sent: Monday, May 17, 2004 4:47 PM Subject: Re: Does primary key structure impact UPDATE performance?

Assuming, that you're running on decent CPUs, it seems strange that 2371 consistent reads and 1 current read require 1,72 seconds of CPU time (even in the very unlikely event when CPU usage between every physical IO would heve been overaccounted due quantization error).

This PL/SQL loop, does it open a cursor and keep it open for a long time? That way several rollback segment block gets might be required to satisfy a consistent read, that could explain high CPU usage per LIO in extreme cases. (But it wouldn't explain why there is such a difference between two tables w. different indexes).

Maybe query definitions + execution plans would give some more clues...

Tanel.

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 - 15:34:24 CDT