not able to set db_16k_cache_size

[Sameer]

Hi,

I need to create a tablespace with non standard block size, (16K)

But when I try to set db_16k_cache_size parameter, I get following
error. I tried smaller values also, but still the same error...

Which all parameters I need to consider while using non standard block size..

I am using O9iR2 WinXP 512MB RAM

db_cache_size 25165824
db_block_size 8192

SQL> alter system set db_16k_cache_size=12M;
alter system set db_16k_cache_size=12M
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00384: Insufficient memory to grow cache

SQL> show sga

Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
SQL>

Thanks

Sameer

[sambavan]

How much of RAM do you have on your system?

Sam

[pando]

plz post db cache, shared pool, redo buffer and sga max size parameters

[slimdave]

What Oracle is telling you is that it will not go and grab another 12M to enable the db_16k_cache_size to be created. You must shrink your other memory allocations by 12M first, then "alter system set db_16k_cache_size=12M"

[jmodic]

Just a small remark to the following slimdave's observation:

Originally posted by slimdave
What Oracle is telling you is that it will not go and grab another 12M to enable the db_16k_cache_size to be created.

Actualy, although you specified you want to set that 16K cache to 12M, Oracle will want to set it to 16M, because it must round it to the neraest "granule" multiplicator size. Since your total SGA is larger than 128M, the granule size is 16M, which is a smallest amount that you can set a particular cache part to.

Appart from that small detail, what slimdave suggested is totaly correct and valid.

[Sameer]

Hi,

my parameters are:

db_cache_size 25165824
shared_pool_reserved_size 2516582
shared_pool_size 50331648
sga_max_size 135338868
log_buffer 524288

I have 512MB RAM

Does this mean I have to reduce my shared_pool size by 12M? or overall reduce all memory related parameters by 12M?

Please help..

Thanks

Sameer

[jmodic]

YES and NO.

YES, you have to reduce one of the SGA components to make room for your new 16k buffer cache, by reducing shared pool or your current default buffer cache.

And NO, reducing it by 12M will not be enough. You'll have to reduce it by at least 16M. Check my previous reply for the reasons.

[Sameer]

Actualy, although you specified you want to set that 16K cache to 12M, Oracle will want to set it to 16M, because it must round it to the neraest "granule" multiplicator size. Since your total SGA is larger than 128M, the granule size is 16M, which is a smallest amount that you can set a particular cache part to.

Thanks for the reply...

It rounds off to 16M because of my SGA is larger than 128M or it always rounds to the neartest "granule" multiplicator size.

Sameer

[Sameer]

SQL> show sga

Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             100663296 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
SQL> show parameter db_cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
db_cache_size                        big integer 20971520
SQL> show parameter shared_pool_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
shared_pool_size                     big integer 33554432

SQL> show parameter large

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
large_pool_size                      big integer 8388608
SQL> show parameter db_16K

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
db_16k_cache_size                    big integer 12582912
SQL>

I worked recently on the problem and it worked...

Thanks