RE: Does primary key structure impact UPDATE performance?

From: Thomas Jeff <jeff.thomas_at_thomson.net>
Date: Mon, 17 May 2004 16:24:10 -0500
Message-ID: <358728A276824E419580403633AABFD0021F619B@INDYSMAIL03.am.thmulti.com>

Thanks for the replies. That makes some sense. We have AUM in this = database, and
I did notice that v$undostat was showing some out of space counts.

This is a purchased module for Informatica, and I don't have access to = the the code,
but my understanding is that the core logic is basically a merge = operation, Informatica
pre-caches the data and then loops and decides whether to insert or = update a row in
the table. =20

-----Original Message-----

From: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis Sent: Monday, May 17, 2004 3:37 PM
To: oracle-l_at_freelists.org
Subject: Re: Does primary key structure impact UPDATE performance?

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 -----=20 From: "Tanel P=F5der" <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


-----------------------------------------------------------------

----------------------------------------------------------------

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 - 16:21:34 CDT