Re: buffer cache structure in SGA

From: Mladen Gogala <>
Date: Tue, 3 Apr 2018 03:07:18 -0400
Message-ID: <>

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 being practically unusable. Situation has improved greatly in 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:


Property Description

Parameter type         

Big integer


|DB_CACHE_SIZE =| |integer| |[K | M | G]|

Default value         

If |SGA_TARGET| is set: If the parameter is not specified, then the default is |0| (internally determined by the Oracle Database). If the parameter is specified, then the user-specified value indicates a minimum value for the memory pool.

If |SGA_TARGET| is not set, then the default is either 48 MB or 4 MB * number of CPUs, whichever is greater


|ALTER SYSTEM| Modifiable in a PDB         




|DB_CACHE_SIZE| specifies the size of the |DEFAULT| buffer pool for buffers with the primary block size (the block size defined by the |DB_BLOCK_SIZE| initialization parameter).

The value must be at least |4M * number of cpus| (smaller values are automatically rounded up to this value). A user-specified value larger than this is rounded up to the nearest granule size. A value of zero is illegal because it is needed for the |DEFAULT| memory pool of the primary block size, which is the block size for the |SYSTEM| tablespace.


This parameter is optional for pluggable databases (PDBs). When this parameter is set for a PDB, it specifies the guaranteed buffer cache size for the PDB.

To be able to use Resource Manager in a CDB to control the amount of memory each PDB can use:


    The |NONCDB_COMPATIBLE| initialization parameter must be set to     |FALSE| at the CDB level (in the root of the CDB).


    The |MEMORY_TARGET| initialization parameter must not be set at the     CDB level.


    If the |SGA_TARGET| initialization parameter is not set, but the     |DB_CACHE_SIZE| initialization parameter is set at the CDB level,     then the following requirements must be met:


        The value of |DB_CACHE_SIZE| set in a PDB must be less than or
        equal to 50% of the |DB_CACHE_SIZE| value at the CDB level.


        The sum of the |DB_CACHE_SIZE| values across all the PDBs in the
        CDB must be less than or equal to 50% of the |DB_CACHE_SIZE|
        value at the CDB level.

    When you set |DB_CACHE_SIZE| in a PDB to a value that does not meet     these requirements, you receive an error. If these requirements are     violated after the PDB’s parameter is set (for example, if the     |DB_CACHE_SIZE| value is changed at the CDB level, Oracle will     adjust the PDB’s value to meet these requirements.

If |SGA_TARGET| is set at the CDB level, these requirements must be met:


    The values of |DB_CACHE_SIZE| plus |SHARED_POOL_SIZE| in a PDB must     be less than or equal to 50% of the PDB’s |SGA_TARGET| value.


    The values of |DB_CACHE_SIZE| plus |SHARED_POOL_SIZE| in a PDB must     be less than or equal to 50% of the |SGA_TARGET| value at the CDB level.


    The sum of |DB_CACHE_SIZE| plus |SHARED_POOL_SIZE| across all the     PDBs in a CDB must be less than or equal to 50% of the |SGA_TARGET|     value at the CDB level.

If any of these three requirements above are not met, you will receive an error.

See Also:


    Oracle Database Performance Tuning Guide     <>     and Oracle Database Administrator's Guide     <>     for more information on setting this parameter


    Oracle Database Administrator’s Guide     <>     for more information about the initialization parameters that     control the memory usage of PDBs

Of course, that means the buffer cache is partitioned among the PDB's. However, DBWR and LGWR processes are still running at the instance level, so there are some changes in buffer management. Pins and latches must be global.

On 04/02/2018 08:07 PM, Orlando L wrote:
> Great paper. Learned about the hash table, hash buckets, chains and
> buffer headers. I hope the structure is still applicable in 12c, as
> Mladen has said.
> Anyway, based on the paper, I started searching on ways to reduce
> logical IOs. Any pointers on that are welcome too.
> On Mon, Apr 2, 2018 at 10:14 AM, Cary Millsap
> < <>> wrote:
> <>
> Cary Millsap
> Method R Corporation
> Author of /Optimizing Oracle Performance
> <>/ and /The Method R Guide to Mastering
> Oracle Trace Data, 2nd edition <>/
> On Sat, Mar 31, 2018 at 12:57 PM, Orlando L <
> <>> wrote:
> HI
> I am reading a book on internals. It talks about Hash latch,
> hash bucket, hash chain, buffer header etc and contention for
> those things. I somewhat understand what a latch is, but do
> not know what a 'bucket', 'chain', are.  Is there a paper that
> explains them in detail at a basic level. thanks.
> Orlando.

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

Received on Tue Apr 03 2018 - 09:07:18 CEST

Original text of this message