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

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

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