not able to set db_16k_cache_size
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: not able to set db_16k_cache_size

Hybrid View

  1. #1
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    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

    Code:
    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

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    How much of RAM do you have on your system?

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    plz post db cache, shared pool, redo buffer and sga max size parameters

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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"

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Hi,

    my parameters are:

    Code:
    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

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    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

  9. #9
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Code:
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width