Cached blocks and performance

Please bear with me if this is a stupid question - it is more of a learning exercise than a real question although the train of thought was prompted by a real situation.

I have a large table and I am performing a complex operation on a number of rows, where records are compared against one another and modified or ignored, explain plan shows a number of iterative loops. Performance for similar queries varies wildy even with no other users on the box. Can the randomness of the packing of rows in blocks be a performance factor.
e.g. If I have, say an average 8 rows per block, 80000 rows initially retrieved for update could be in 80000 different blocks or just 10000 blocks. If my block buffers allowed say 20000 cached blocks would the lucky situation where my random(ish) rows happen to be packed together perform noticably better than where they are widely spread.

The underlying question is I suppose, I am not clear whether, once the row has been retrieved from the block, does each new statement processed on the row have to return to the original block (or it's image in memory) or does Oracle have a mechanism for caching just the required rows from read blocks?

Thanks Received on Thu Aug 29 2002 - 10:54:55 CDT