RE: Overhead to consistent gets?

I don't have current code but had access to it for 12 years ending at 9i so I'll pitch in...

>>>1) Oracle calculates the hash value of the block and
>>>searches the SGA hash
>>> table for the place where the block is located.

The routine is kcbget() (or one of his special purpose cousins). It does'm really "search" a hash *table* if you will. A hash table would be more of a "perfect hash" structure and to implement that, every possible hash value has to be known when the table is set up. That would mean knowing every possible database block address. Instead, it hashes to a bucket that has

similar hashed dbas chained off off it in a linked list. So it is more of a scan of the linked list looking for the right dba and right version of it.

The particulars of the structures under a get are not as important as remembering that before walking that chain, the process has to obtain the latch on the chain. The closer a system gets to processor saturation, the more troublesome latch gets become--presuming the chain is hot. While cache buffers chains latch thrashing may seem like a nebulous place to put blame for high processor utilization, trust me, it isn't. It can be, in fact, a very real problem with many many applications. Just look at statspack. Any sleeps on cache buffers chains is like cancer.

PS. ooooh, I said "trust me"....does that mean the "expert patrol" are going to come looking for me? :-)

>>>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
>>>
>>>
>>>

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