Re: Overhead to consistent gets?

Notes in-line:

On 01/11/2006 10:16:34 PM, Jonathan Gennick wrote:
> A reader just asked me an interesting question, to which I can only
> answer "I don't know". But I'd sure like to know, so in the interests
> of gaining enlightnment here's the question:
>
> Is there likely to be a performance difference between the following
> two situations:
>
> a) Your query reads 1000 rows packed into 10 blocks. All blocks
> are already in the buffer cache (no physical reads). No other
> transaction has updated the blocks.
>
> b) Your query reads the same 1000 rows, but this time they are
> scattered over, say, 50 blocks. Again, all blocks are in cache. No
> other transaction has updated the blocks.
>
> Case b results in more consistent gets. But the same number of rows
> are returned in either case. This question seems to boil down to
> "what's the overhead to a consistent get?"

Jonathan, the question here is "what is overhead"? The overhead is the time spent by Oracle to maintain its own structures + the time spent by OS to maintain its own structures. So, what exactly happens during a consistent get in the situation described? As I don't have access to the source code, I cannot tell precisely, with 100% of certainty, but based on my experience, the proccess goes something like this:
1) Oracle calculates the hash value of the block and searches the SGA hash

   table for the place where the block is located. 2) Oracle checks the SCN of the block and compares it with the SCN of the current

   transaction. Here, I'll assume that this check will be OK and that no read    consistent version needs to be constructed. 3) If the instance is a part of RAC, check the directory and see whether any other

   instance has modified the block. It will require communication with the GES process    using the IPC primitives (MSG system calls). MSG system calls are frequently \ implemented

   using device driver which brings us to the OS overhead (context switch, scheduling) 4) If everything is OK, the block is paged in the address space of the requesting process.

   For this step I am not exactly sure when does it happen, but it has to happen at    some point. Logically, it would look as the last step, but my logic may be flawed.    Here, of course, I assume a soft fault. Hard fault would mean that a part of SGA was    swapped out.

All of this is an overhead of a consistent get and it is the simplest case. How much is it \ in
terms of microseconds, depends on many factors, but the overhead exists and is strictly \ larger
then zero. If your SQL does a gazillion of consistent gets, it will waste significant CPU \ power
and time to perform that.

>
> And, my own question that I'll is: do things change if another
> transaction has updated the blocks since your transaction began?
>
> Best regards,

Well, in that case the SCN part comes to play and the consistent version of the block must be constructed from the undo segments, which further increases the overhead.

-- 
Mladen Gogala
http://www.mgogala.com



--
http://www.freelists.org/webpage/oracle-l