Thanks for the question, Raghava.
Asked: June 24, 2020 - 9:21 pm UTC
Last updated: July 01, 2020 - 1:58 am UTC
Version: Oracle 18c
Viewed 10K+ times! This question is 
You Asked
This is about the db_block_size, db_16k_cache_size parameters. Oracle document says,
Statement 1: DB_nK_CACHE_SIZE (where n = 2, 4, 8, 16, 32) specifies the size of the cache for the nK buffers. You can set this parameter only when DB_BLOCK_SIZE has a value other than nK. For example, if DB_BLOCK_SIZE=4096, then it is illegal to specify the parameter DB_4K_CACHE_SIZE (because the size for the 4 KB block cache is already specified by DB_CACHE_SIZE).
Statement 2: Do not set this parameter to zero if there are any online tablespaces with an nK block size.
In our case, we are setting db_block_size to 16K and setting db_16k_cache_size = 65% of sga_target as we have tablespaces of 16k block size.
Isn’t this like, violating statement 1 (setting db_16k_cache_size when db_block_size is 16k, which is illegal) while fulfilling statement 2 (setting db_16k_cache_size to non-zero as we have tablespaces of 16k block size). I am trying to understand this because these parameters effect performance. Please explain.
Here is how the things looks in my Database
select tablespace_name, block_size from dba_tablespaces;
TABLESPACE_NAME BLOCK_SIZE
------------------------------ ----------
SYSTEM 16384
SYSAUX 16384
UNDOTBS1 16384
TEMP 16384
USERS 16384
COREDB 16384
PORTALDB 16384
LOCDB 16384
LOC_INDX 16384
RTA 16384
CEDB 16384
MASTERDB 16384
AGGREGATE_INDX 16384
AGGREGATE_DB 16384
OPENIDDB 16384
15 rows selected.
show parameter db_block
NAME TYPE VALUE
----------------- ------- -------
db_block_buffers integer 0
db_block_checking string FALSE
db_block_checksum string TYPICAL
db_block_size integer 16384
show parameter cache_size
NAME TYPE VALUE
------------------------ ----------- -----
client_result_cache_size big integer 0
data_transfer_cache_size big integer 0
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_size big integer 0
db_flash_cache_size big integer 0
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
and Connor said...
Can you run this for us please in SQL Plus and paste the results in
select tablespace_name, block_size from dba_tablespaces;
show parameter db_block
show parameter cache_size
==================
You said "setting db_16k_cache_size = 65% of sga_target" but the values you have shown have db_16k_cache_size = 0.
But yes, if your database block size is 16k, then the only cache size you should set (if at all) is db_cache_size, and you only need to set that if you want to set a minimum size for the cache as a subset of the overall sga_target.
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment