Oracle 9i Dynamic SGA: Doubts on db_nk_cache_size parameters

[Former Member]

Hi,

Since SAP is using standard 8K block size, which cache parameters do we need to configured for Dynamic SGA for SAP system? OSS note 617416 is not clear on this part.

Does anyone knows it uses db_cache_size or db_8k_cache_size?

If I only configure db_cache_size and leave the rest as value=0, will this sounds right?

db_cache_size = db_block_buffers * db_block size

[Former Member · Accepted solution]

Hi Annie

<b>db_cache_size</b> — db_cache_size determines the number of database block buffers in the Oracle SGA and represents the single most important parameter to Oracle memory.

In the pre-9i releases, you define the BLOCK_SIZE when creating the database and it cannot be changed. In 9i also this is true. In addition to the standard block size of the database, you can create tablespaces with different block size. The block size of the tablespace is specified using the BLOCK SIZE clause of CREATE TABLESPACE.

For you to use this feature, you need to set the right buffer cache parameter. The DB_CACHE_SIZE specifies the buffer cache size for the objects in tablespaces created with the standard block size. DB_nK_CACHE_SIZE parameter sets the appropriate buffer cache for the non-standard block sized tablespace. ‘n’ could be 2, 4, 8, 16 or 32 but it should not be equal to your standard block size. The default values for DB_nK_CACHE_SIZE parameters are 0.

<b>Note:</b> It is also important to remember that there are downsides to having a very large db_cache_size. While direct access to data is done with hashing, at times the database must examine all of the blocks in the RAM cache:

<b>Systems with high invalidations</b> — Whenever a program issues a truncate table, uses temporary tables or runs a large data purge, Oracle must sweep all of the blocks in the db_cache_size to remove dirty blocks. This approach can cause excessive overhead for system with a db_cache_size greater than 10gB.

<b>High Update Systems</b> — The database writer (DBWR) process must sweep all of the blocks in db_cache_size when performing an asynchronous write. Having a huge db_cache_size can cause excessive work for the database writer.

You can check the following links:

http://www.bijoos.com/ora9/nf9i_multblock.htm

http://www.lazydba.com/oracle/0__20724.html

This will clear all your doubts.

Regards

Sumit Jain

[reward with points if useful]

[Former Member · Accepted solution]

Hi Sumit,

Thanks for the information. Just to further clarify, for a standard SAP system running 8k block size, switching from manual SGA to Dynamic SGA, there isn't any need to configure db_nk_cache_size, isn't it?

[Former Member · Accepted solution]

Hi Annie

As far as I think switching from manual SGA to Dynamic SGA derz no need to configure db_nk_cache_size. No configuration needed for that matter.

Hope this clarifies ur doubt.

[Former Member · Accepted solution]

Hi.

With the dynamic SGA , the size of the buffer cache, the shared pool, the large pool, and the process-private memory can be changed without shutting down the instance.

The DB_nK_CACHE_SIZE parameters cannot be used to size the cache for the standard block size. If the value of DB_BLOCK_SIZE is nK, it is illegal to set DB_nK_CACHE_SIZE. The size of the cache for the standard block size is always determined from the value of DB_CACHE_SIZE.

If this is not the case You can change the size of the buffer cache while the instance is running, without having to shut down the database. with the ALTER SYSTEM statement

Vinod

[Former Member · Accepted solution]

Thanks for the prompt response.