Skip to Main Content
  • Questions
  • db_16k_cache_size, db_block_size parameter setting

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Raghava.

Asked: June 24, 2020 - 9:21 pm UTC

Answered by: Connor McDonald - Last updated: July 01, 2020 - 1:58 am UTC

Category: Database Administration - Version: Oracle 18c

Viewed 100+ times

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

and you rated our response

  (2 ratings)

Reviews

Clarification Tablespace BLOCK_SIZE

June 29, 2020 - 7:28 am UTC

Reviewer: Raghava Karanam from Hyderabad, India

Thanks Connor!!

Yeah that's my mistake!!!.. about saying "setting db_16k_cache_size = 65% of sga_target" but the values you have shown have db_16k_cache_size = 0.

I think I have put my question not so precisely. Just tried to be more clear.. there are two things as I understood from oracle docs. ( https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/DB_nK_CACHE_SIZE.html#GUID-025ECEB2-E535-49D2-9721-268E3925FE09 )
1. if db_block_size=16k then do not set db_16k_block_size (even if I try oracle does not allow me)
2. if tablespace block size =16k then do not set db_16k_block_size this parameter to zero

My database configuration is like, db_block_size=16k and tablespace block size=16k. so now my confusion is
do I have to populate db_16k_block_size because I have tablepsace blocksize=16k or just leave db_16k_block_size because I have already set db_block_size=16k?
or do I have to set any one among db_block_size and tablespace block size..?

Raghava K
Connor McDonald

Followup  

June 30, 2020 - 12:50 am UTC

The only time you need to set "db_XXk_block_size " is when BOTH the following are true:

a) the "XX" is not the same as your current default block size
b) you have a tablespace with a block size of XX

So for example, in your case, you will never need to set db_16k_block_size. However, if someone wanted to attach/create an 8k blocksize tablespace, then you would need to set db_8k_block_size because 8k is not your default block size for the database.

Thanks

June 30, 2020 - 1:05 am UTC

Reviewer: Raghava K from Hyderabad, India

Understood!!!... Thanks a lot Connor!!!
Connor McDonald

Followup  

July 01, 2020 - 1:58 am UTC

glad we could help

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database