Re: buffer cache structure in SGA

Well, buffers are ordered in several structures at once: hash tables, like the one described by Kernighan & Ritchie in "The C Programming Language" and doubly linked lists. Doubly linked list is a linear structure, also known as "FIFO" or "First In, First Out" or queue, while hash table is a tabular structure, consisting of several linked lists, one per each distinct value of the hashing function. There are additional techniques for avoiding collisions (having more than one element in the list for any of the hash values) and hash function selection, but essentially that's it. Each buffer is a member of several structures with pointers for "next" and "prev".  Whenever any of the global SGA structures is being manipulated, its head must be locked, so that only the process which gets the lock can manipulate the structure. These  locks are known as pins, latches and mutexes and can wreak havoc on the performance. However, this is all going to change in 18c which will be the first Heuristically programmed ALgorithmic database, HAL for short, like HAL 9000.

On 04/03/2018 07:07 PM, Orlando L wrote:
> "It's probably worth including the point that the linked list is still
> there".
>
> Am I correct in assuming the linked list points to buffers in the hash
> table?
>
>
> On Tue, Apr 3, 2018 at 2:29 AM, Jonathan Lewis
> <jonathan_at_jlcomp.demon.co.uk <mailto:jonathan_at_jlcomp.demon.co.uk>> wrote:
>
>
> It's probably worth including the point that the linked list is
> still there - the effect of the touch count is that a buffer is
> not moved to the head of the list every time it is touched (which
> is why the latch activity became a threat), but it only moved when
> it reaches the tail of the list, and then only if it has been
> touched on the way down.
>
> (Obviously lots more detail I've omitted - but it's in the book)
>
> Regards
> Jonathan Lewis
>
> ________________________________________
> From: oracle-l-bounce_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>
> <oracle-l-bounce_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>> on behalf of Mladen Gogala
> <gogala.mladen_at_gmail.com <mailto:gogala.mladen_at_gmail.com>>
> Sent: 03 April 2018 08:07
> To: oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org>
> Subject: Re: buffer cache structure in SGA
>
> Well, not quite the same. In Oracle 8i, buffers were managed by a
> linked list. The oldest buffers were at  the tail of list, the
> most recently touched buffers were at the head of the list.  Since
> Oracle 9.2 buffers are managed by the "touch count".   Basically,
> when Oracle would need to free buffer in Oracle 8i, it would take
> certain number of buffers from the end of the linked list, save
> them and allocate them to processes that needed them. The problem
> was that to manipulate the linked list, Oracle would need to
> acquire the latch. And latches can be expensive. So, with touch
> counts, the organization was different, the need for latches was
> much smaller.
>
> In Oracle 11.2, the whole system of pins and latches was
> completely reorganized, which resulted in Oracle 11.2.0.1 being
> practically unusable. Situation has improved greatly in 11.2.0.4.
> Now, there are some additional changes in 12c, to prevent
> connections to different PDB's  from stealing buffers from each
> other. If you check db_cache_size parameter in Oracle 12.2, you
> will see that it's modifiable within PDB:
> --
> http://www.freelists.org/webpage/oracle-l
> <http://www.freelists.org/webpage/oracle-l>
>
>
>

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217


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