KGH: NO ACCESS

The value for the component 'KGH: NO ACCESS' in the Shared Pool is growing when ASMM is enabled by setting SGA_TARGET. What does this component signify? How to prevent the growth of this component?

 

'KGH: NO ACCESS' refers to granules that are in transit with ASMM i.e memory being reassigned from the Shared Pool to the Database Buffer Cache and vice-versa.

This memory component in the Shared Pool marked as 'KGH: NO ACCESS' is used by the Buffer Cache.

One could see the value for this component increasing when ASMM (Automatic Shared Memory Management) is enabled by setting the SGA_TARGET parameter.

When ASMM is enabled, Oracle will dynamically manage the memory allocations between the tunable pools based on the requirement.

Note that one can still specify a value for the each of the tunable parameters i.e Shared Pool, Database Buffer Cache, Large Pool, Java Pool and Streams Pool when ASMM is enabled. When minimum value for each of the tunable parameters is set, Oracle will make sure that the amount of memory being allocated for the corresponding pool will not shrink below the specified amount.

Thus by setting minimum value for the Shared Pool and Database Buffer Cache, we will be ensuring that the amount of memory available for reassignment between the two pools will be lesser thus preventing the growth of this 'KGH: NO ACCESS' component.

 

To prevent the growth of this component you can either

1> Disable ASMM

Or

2> Set the minimum values for the Shared Pool and the Database Buffer Cache

Hint : To begin with one can startup the database with no values set for both shared_pool_size and db_cache_size, monitor the memory usage during peak load and then check the values for '_shared_pool_size' and '_db_cache_size' registered to the alert log at the time of next startup. Based on the observations made, the appropriate values can then be set for 'shared_pool_size' and 'db_cache_size'. Of course this can still be tuned further.