Multiple BUFFER subcaches

Multiple BUFFER subcaches: What is the Total BUFFER CACHE Size?
===============================================================

How do we know that different buffer caches are initialized?
————————————————————

1. V$BUFFER_POOL is the view that displays the entire buffer cache structure:

SQL> select id,name,block_size, current_size,buffers from v$buffer_pool;

ID NAME BLOCK_SIZE CURRENT_SIZE BUFFERS
———- ——————– ———- ———— ———-
1 KEEP 8192 16 2006
2 RECYCLE 8192 16 2006
3 DEFAULT 8192 64 8024
4 DEFAULT 2048 32 15140
7 DEFAULT 16384 16 1013

This view reflects that:

* the default standard block size for the database is 8K (ID=3)
* the standard block size buffer cache is currently 64M (including 8024
blocks of 8K)
* the KEEP and RECYCLE buffer pools are initialized (ID 1 and 2 of the same
standard block size)
* non-standard block size buffer caches are initialized for
=> 2K block size (ID 4) at 32M
=> 16K block size (ID 7) at 16M
* non-standard block size buffer caches are not initialized yet for block
sizes of 4K (ID 5) and 32K (ID 8)

Since the standard block size is 8K, it is no use to initialize the ID 6
which would be redundant with the ID 3.

2. Now other non-standard block size buffer caches are initialized and the
standard block size buffer cache is shrunk:

SQL> alter system set db_cache_size=32m;
System altered.

SQL> alter system set db_4k_cache_size=2m;
System altered.

SQL> alter system set db_32K_cache_size=16m;
alter system set db_32K_cache_size=16m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00382: 32768 not a valid block size, valid range [2048..16384]

Not all operating systems allow a 32K block size. Please refer to:
Note 138242.1 ORA-328 Creating 32K Blocksize Tablespace, Related Cache
Not Set

SQL> select id,name,block_size, current_size,buffers from v$buffer_pool;

ID NAME BLOCK_SIZE CURRENT_SIZE BUFFERS
———- ——————– ———- ———— ———-
1 KEEP 8192 16 2006
2 RECYCLE 8192 16 2006
3 DEFAULT 8192 32 4012
4 DEFAULT 2048 32 15140
5 DEFAULT 4096 16 3934
7 DEFAULT 16384 16 1013

Note that the size specified for the initialization of the 2K block size
buffer cache has been rounded up to the granule boundary which is 16M in
this case because the SAG_MAX_SIZE is above 128M.

3. The total size for the buffer cache is the sum of the sub-caches:

Total BUFFER CACHE size = DB_CACHE_SIZE
+
DB_KEEP_CACHE_SIZE
+
DB_RECYCLE_CACHE_SIZE
+
DB_2K_CACHE_SIZE
+
DB_4K_CACHE_SIZE
+
DB_8K_CACHE_SIZE
+
DB_16K_CACHE_SIZE
+
DB_32K_CACHE_SIZE

Or
= DB_CACHE_SIZE
+
DB_KEEP_CACHE_SIZE
+
DB_RECYCLE_CACHE_SIZE
+
non-standard block size buffer caches

SQL> select sum(current_size), sum(buffers) from v$buffer_pool;

SUM(CURRENT_SIZE) SUM(BUFFERS)
—————– ————
128 28111

The total buffer cache is 128M.

Be aware that these 28111 blocks are blocks of different sizes.

References:
===========

Note 138242.1 ORA-328 Creating 32K Blocksize Tablespace, Related Cache Not Set

Search Words:
=============

ORA-2097 ORA-382

.

Bookmarks Admin Profile Feedback Sign Out Help

Copyright © 2006, Oracle. All rights reserved. Legal Notices and Terms of Use | Privacy Statement

Sobre Alexandre Pires

ORACLE OCS Goldengate Specialist, OCE RAC 10g R2, OCP 12C, 11g, 10g , 9i e 8i - Mais de 25 anos de experiência na área de TI. Participei de projetos na G&P alocado na TOK STOK, EDINFOR alocado na TV CIDADE "NET", 3CON Alocado no PÃO DE AÇUCAR, DISCOVER alocado na VIVO, BANCO IBI e TIVIT, SPC BRASIL, UOLDIVEO alocado no CARREFOUR e atualmente na ORACLE ACS atendendo os seguintes projetos: VIVO, CLARO, TIM, CIELO, CAIXA SEGUROS, MAPFRE, PORTO SEGURO, SULAMERICA, BRADESCO SEGUROS, BANCO BRADESCO, BASA, SANTANDER, CNJ, TSE, ELETROPAULO, EDP, SKY, NATURA, ODEBRESHT, NISSEI, SICREDI, CELEPAR, TAM, TIVIT, IBM, SMILES, CELEPAR, SERPRO,OKI,BANCO PAN, etc
Esse post foi publicado em BUFFERS, PERFORMANCE, PLSQL SCRIPTS. Bookmark o link permanente.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s