1 2 Previous Next 18 Replies Latest reply: Dec 28, 2013 7:07 AM by Aman.... RSS

    DB_nk_CACHE_SIZE in oracle 10g r2

    GTS (DBA)

      Good Morning everyone


      From official document ... Memory Architecture


      1) How the logic applied here ? i mean 32M  how it came ?


      Memory consumed by manually sized components reduces the amount of memory available for automatic adjustment.

      SGA_TARGET = 256M

      DB_8K_CACHE_SIZE = 32M 

      The instance has only 224 MB (256 - 32) and

      remaining to be distributed among the automatically sized components.

       

      Thanks in advance. !

        • 1. Re: DB_nk_CACHE_SIZE in oracle 10g r2
          JustinCave

          I'm not sure that I understand the question.

           

          Are you asking how someone decided that 32 M was an appropriate value for DB_8K_CACHE_SIZE?  If so, that's not something that the documentation you're linking to is concerned about, nor does it provide enough information about the specific system to be able to determine that.  The documentation you are linking to is attempting to explain the implication of setting that parameter, not telling you how to determine what to set the parameter to if you really want to set it.

           

          Justin

          • 2. Re: DB_nk_CACHE_SIZE in oracle 10g r2
            Aman....

            This is the doc link that I guess you should read,

            Memory Architecture

             

            Aman....

            • 3. Re: DB_nk_CACHE_SIZE in oracle 10g r2
              jgarry

              Also, simply asking the question means someone needs to point out that non-standard blocksizes have a specific use, transportable tablespaces.  A transportable tablespace would have come from another database with a different blocksize, which may or may have had ASMM set.  The actual tuning would depend on what this database does with the data, as well as it's other data. 

               

              Unfortunately, some people have advocated other uses without fully thinking it through. 

              • 4. Re: DB_nk_CACHE_SIZE in oracle 10g r2
                Hemant K Chitale

                The value of DB_8K_CACHE_SIZE is not auto-configured by Oracle.  It has to have been explicitly configured by the DBA.

                 

                 

                Hemant K Chitale

                • 5. Re: DB_nk_CACHE_SIZE in oracle 10g r2
                  GTS (DBA)

                  Hi aman ;

                   

                  I asked , could you please explain how it was calculated ?

                  i mean  non-standard block sizes (controlled by DB_nK_CACHE_SIZE, n = {2, 4, 8, 16, 32})

                   

                  if so , DB_8K_CACHE_SIZE = 32M 

                  what's the logic behind to calculate 32M

                  • 6. Re: DB_nk_CACHE_SIZE in oracle 10g r2
                    Suntrupth

                    Hi,

                     

                    I believe you have raised concern as shown in http://docs.oracle.com/cd/B19306_01/server.102/b14220/memory.htm

                     

                     

                    DB_8K_CACHE_SIZE = 32M , 32M is a value that they have opted just to explain the concept. You can set it to any value as per your requirement.

                     

                    Regards,

                    Suntrupth

                    • 7. Re: DB_nk_CACHE_SIZE in oracle 10g r2
                      Aman....

                      GTS (DBA) wrote:

                       

                      Hi aman ;

                       

                      I asked , could you please explain how it was calculated ?

                      i mean  non-standard block sizes (controlled by DB_nK_CACHE_SIZE, n = {2, 4, 8, 16, 32})

                       

                      if so , DB_8K_CACHE_SIZE = 32M 

                      what's the logic behind to calculate 32M

                      There is no calculation for the memory sizing, set it to  whatever value you wish to. The non-default caches  are going to work for non-default block sized tablespaces. Here the important thing that you need to remember is that out of the 5 block sizes , only one size would be the default for database . So for example, in 11.2, the default block size is 8KB so leaving that, you can set cache for 4 other block sizes.

                       

                      HTH

                      Aman....

                      • 8. Re: DB_nk_CACHE_SIZE in oracle 10g r2
                        GTS (DBA)

                        Thanks Aman,

                            

                        Aman.... wrote:

                         

                        There is no calculation for the memory sizing, set it to  whatever value you wish to. The non-default caches  are going to work for non-default block sized tablespaces. Here the important thing that you need to remember is that out of the 5 block sizes , only one size would be the default for database .

                        So for example, in 11.2, the default block size is 8KB so leaving that, you can set cache for 4 other block sizes.

                         

                        Quest:

                         

                        As per above comments the default block size is 8k. So how many blocks is been occupied for cache size ???

                        If possible can you explain in detail plz.

                         

                         

                        Thanks & Regards,

                        GTS

                        • 9. Re: DB_nk_CACHE_SIZE in oracle 10g r2
                          Aman....

                          GTS (DBA) wrote:

                           

                          Thanks Aman,

                             

                          Aman.... wrote:

                           

                          There is no calculation for the memory sizing, set it to  whatever value you wish to. The non-default caches  are going to work for non-default block sized tablespaces. Here the important thing that you need to remember is that out of the 5 block sizes , only one size would be the default for database .

                          So for example, in 11.2, the default block size is 8KB so leaving that, you can set cache for 4 other block sizes.

                           

                          Quest:

                           

                          As per above comments the default block size is 8k. So how many blocks is been occupied for cache size ???

                          If possible can you explain in detail plz.

                           

                           

                          Thanks & Regards,

                          GTS

                          Hmmm well set the size and divide that with 8 to find the relative count of the buffers that would fit in the buffer cache. Oracle uses Granule as the memory unit for assigning the memory and based on it, the value is assigned.

                           

                          HTH

                          Aman....

                          • 10. Re: DB_nk_CACHE_SIZE in oracle 10g r2
                            Girish Sharma

                            >So for example, in 11.2, the default block size is 8KB so leaving that, you can set cache for 4 other block sizes.

                            Yes.

                             

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

                            DB_nK_CACHE_SIZE

                             

                            Another Example :

                            Suppose you have db_block_size 8192, wish to create a tablespace of 16k blocksize, then first you have to say :

                            alter system set db_16k_cache_size=<number>m scope=both;

                            and now you can create tablespace of blocksize 16k,

                            but if you says :

                            alter system set db_8k_cache_size=<number>m scope=both; then you will get

                            ORA-00380: cannot specify db_8k_cache_size since 8K is the standard block size

                             

                            So, db_nk_size, here N will always be non_db_block_size value.

                             

                            Regards

                            Girish Sharma

                            • 11. Re: DB_nk_CACHE_SIZE in oracle 10g r2
                              GTS (DBA)

                              Thanks aman ;

                               

                              Aman.... wrote:

                               

                              GTS (DBA) wrote:

                               

                              Thanks Aman,

                              Aman.... wrote:

                               

                              There is no calculation for the memory sizing, set it to  whatever value you wish to. The non-default caches  are going to work for non-default block sized tablespaces. Here the important thing that you need to remember is that out of the 5 block sizes , only one size would be the default for database .

                              So for example, in 11.2, the default block size is 8KB so leaving that, you can set cache for 4 other block sizes.

                               

                              Quest:

                               

                              As per above comments the default block size is 8k. So how many blocks is been occupied for cache size ???

                              If possible can you explain in detail plz.

                               

                              Thanks & Regards,

                              GTS

                              Hmmm well set the size and divide that with 8 to find the relative count of the buffers that would fit in the buffer cache.

                              Oracle uses Granule as the memory unit for assigning the memory and based on it, the value is assigned.

                               

                              -------------------------------------------------------------------------

                               

                              If you  do  NOT mind , could you please provide little demo in our database ?

                              Right now i can NOT check in my database.

                              Please  show little demo 'Oracle uses Granule as the memory unit for assigning the memory and based on it, the value is assigned.

                               

                              Please ....

                               

                               

                               

                              • 12. Re: DB_nk_CACHE_SIZE in oracle 10g r2
                                Suntrupth
                                SQL> select component,current_size, granule_size from V$SGA_DYNAMIC_COMPONENTS;
                                
                                COMPONENT                 CURRENT_SIZE GRANULE_SIZE
                                ------------------------- ------------ ------------
                                shared pool                  411041792      4194304
                                large pool                     4194304      4194304
                                java pool                      4194304      4194304
                                streams pool                   4194304      4194304
                                DEFAULT buffer cache         230686720      4194304
                                KEEP buffer cache                    0      4194304
                                RECYCLE buffer cache                 0      4194304
                                DEFAULT 2K buffer cache              0      4194304
                                DEFAULT 4K buffer cache              0      4194304
                                DEFAULT 8K buffer cache              0      4194304
                                DEFAULT 16K buffer cache      33554432      4194304
                                
                                COMPONENT                 CURRENT_SIZE GRANULE_SIZE
                                ------------------------- ------------ ------------
                                DEFAULT 32K buffer cache             0      4194304
                                Shared IO Pool                       0      4194304
                                ASM Buffer Cache                     0      4194304
                                
                                14 rows selected.
                                
                                SQL> alter system set db_16k_cache_size=28M;
                                System altered.
                                
                                SQL> select component,current_size, granule_size from V$SGA_DYNAMIC_COMPONENTS where component='DEFAULT 16K buffer cache';
                                
                                COMPONENT                 CURRENT_SIZE GRANULE_SIZE
                                ------------------------- ------------ ------------
                                DEFAULT 16K buffer cache      29360128      4194304   <-- Allocated 28M (Multiple of 4)
                                
                                
                                SQL> select component,current_size, granule_size from V$SGA_DYNAMIC_COMPONENTS where component='DEFAULT 16K buffer cache';
                                
                                COMPONENT                 CURRENT_SIZE GRANULE_SIZE
                                ------------------------- ------------ ------------
                                DEFAULT 16K buffer cache      29360128      4194304   <-- Allocated 32M (Multiple of 4)
                                
                                
                                SQL> alter system set db_16k_cache_size=30M;
                                System altered.
                                
                                
                                SQL> select component,current_size, granule_size from V$SGA_DYNAMIC_COMPONENTS where component='DEFAULT 16K buffer cache';
                                
                                COMPONENT                 CURRENT_SIZE GRANULE_SIZE
                                ------------------------- ------------ ------------
                                DEFAULT 16K buffer cache      33554432      4194304   <-- Allocated 32M (Multiple of 4)
                                
                                
                                SQL> alter system set db_16k_cache_size=31M;
                                System altered.
                                
                                
                                SQL> select component,current_size, granule_size from V$SGA_DYNAMIC_COMPONENTS where component='DEFAULT 16K buffer cache';
                                
                                COMPONENT                 CURRENT_SIZE GRANULE_SIZE
                                ------------------------- ------------ ------------
                                DEFAULT 16K buffer cache      33554432      4194304  <-- Allocated 32M (Multiple of 4)
                                
                                

                                 

                                As you can see the db_16k_cache_size is always allocated in chunks of granule size (4M). Despite setting the size to 30M,31M,32M it will still allocate 32M.

                                 

                                Hope this is clear.

                                 

                                Regards,

                                Suntrupth

                                • 13. Re: DB_nk_CACHE_SIZE in oracle 10g r2
                                  GTS (DBA)

                                  Thamks  Suntrupth , AMAN & SHARMA ..

                                   

                                  @ Suntrupth FYI :

                                   

                                  When  posting reply with multipl examples , please do little  format.

                                  Anyhow good demo. Thanks.

                                  • 14. Re: DB_nk_CACHE_SIZE in oracle 10g r2
                                    Suntrupth

                                    Hi GTS(DBA),

                                     

                                    Sure, Will keep that in mind. I hadn't noticed the formatting till I refreshed the screen. Anyway, Thank you for letting me know.

                                     

                                    Regards,

                                    Suntrupth

                                    1 2 Previous Next