Re: performance problem : tkprof show many fetches with high elapsed time

> Anyone knows why this
> very simple update takes 868 seconds to complete for 12
>
> updates of each time one record since ID is unique ?

Benny,

You have a large difference between 'elapsed time' (852.74) and 'cpu time' (54.04) for that statement. This time is spent waiting for something. You can only work out what that something is by looking at the wait events by using waits=y in tkprof. What database version is this..? Only versions of tkprof higher than 9i can display these wait events.

I will take a guess that most of the wait time is spent reading blocks from disk because you are doing a large amount of physical I/O (82,456 reads). But this is a dangerous assumption. Only by looking at the waits will you be 100% sure where the time is going.

By the way - are you 100% sure about uniqueness... if ID is unique you should be updating only 1 row but you are actually updating over 6000 rows. I think you need to take another look at the database design.

Cheers

Matt Received on Thu Jan 04 2007 - 02:21:53 CST