Re: Overhead to consistent gets?

  • From: Mladen Gogala <gogala@xxxxxxxxxxxxx>
  • To: jonathan@xxxxxxxxxxx
  • Date: Thu, 12 Jan 2006 10:20:45 -0500

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 
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 
described? As I don't have access to the source code, I cannot tell precisely, 
100% of certainty, but based on my experience, the proccess goes something like
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 
   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 
   instance has modified the block. It will require communication with the GES 
   using the IPC primitives (MSG system calls). MSG system calls are frequently 
   using device driver which brings us to the OS overhead (context switch, 
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 
   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


Other related posts: