KGH: NO ACCESS" Memory Allocation

Common Cause for ORA-4031 in 10gR2, Excess "KGH: NO ACCESS" Memory Allocation [Video] [ID 801787.1]
		수정 날짜 16-AUG-2011     유형 PROBLEM     상태 PUBLISHED

In this Document
  Symptoms
  Cause
  Solution
  References

---

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.4 - Release: 10.2 to 10.2
Information in this document applies to any platform.

Symptoms

Video - How to determine you are hitting this issue (02:44)

After several hours of database operation, ORA-4031 errors are reported.

An examination of the ORA-4031 trace file will show the Memory Allocation called "KGH: NO ACCESS" is consuming large amount of memory. For example the following shows over 500Mbytes  for this allocation.

Allocation Name             Size   

_________________________   __________

"free memory            "   163766600  

...

"KGH: NO ACCESS         "   560713888

Note that it is normal to periodically see "KGH: NO ACCESS" allocations up to about 64M.
This is memory that is in transition between SGA components when automatic memory management is resizing SGA components.  However, it is not normal to see persistent high allocations or a steady build up of this allocation type over time. The exception is when the database needs to make large changes i.e. when changing memory after a heavy load, or starting up using sub-optimal SGA settings, such as when an SPFILE is not being used.

The following query confirms that Memory Allocation "KGH: NO ACCESS" is large:

select * from v$sgastat where pool = 'shared pool' and (name in ('free memory', 'sql area', 'library cache', 'miscellaneous', 'row cache', 'KGH: NO ACCESS') );

The following query shows many GROW and SHRINK operations for "DEFAULT buffer cache" and "shared pool":

ALTER SESSION SET nls_date_format = 'DD/MM/YYYY HH:MI:SS';
SET PAGESIZE 900
SET LINESIZE 255
COL COMPONENT FORMAT A25
COL INITIAL_SIZE FORMAT A10
COL FINAL_SIZE FORMAT A10
SPOOL ASMM_RESIZE.TXT
select START_TIME, component, oper_type, oper_mode, initial_size/1024/1024 "INITIAL", FINAL_SIZE/1024/1024 "FINAL", END_TIME
from v$sga_resize_ops
where component in ('DEFAULT buffer cache', 'shared pool') and status = 'COMPLETE'
order by start_time, component;
SPOOL END

Cause

Too frequent resizes of Shared Pool and Buffer Cache, leading to excess "KGH: NO ACCESS" memory allocation, that consumes SGA memory.

Several bugs have been logged for this problem, for different versions within 10gR2;

• Fixed 10.2.0.2
  Unpublished Bug 4507532: SGA_TARGET DOESN'T WORK AS EXPECTED
  Bug 5045507 ASMM - FREQUENT RESIZING OF SHARED POOL & BUFFER CACHE
• Fixed 10.2.0.4
  Unpublished Bug 6528336: APPSST 10G GSI: LARGE NUMBER OF SESSIONS WAITING ON CURSOR: PIN S WAIT ON X
• Fixed 10.2.0.5
  Unpublished Bug 7189722: APPSST GSI 10G: VERY FREQUENT GROW/SHRINK SGA RESIZE OPERATION HAPPENING

If you are seeing this in version: 11.1.0.6 to 11.2.0.1, please see note:
Note 1127833.1 ORA-04031 in 11g & 11gR2, Excess "KGH: NO ACCESS" Memory Allocation

Solution

 In summary;

1. Disable ASMM

OR

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

OR

3. Increase the time between resize operations

OR

4. Apply the fix, by either upgrading or applying a one-off patch, depending on your version.

To fix the problem being encountered, where excessive ASMM resize operations leads to the "KGH: NO ACCESS" memory allocation consuming the memory available to the SGA, the above solutions are available, as per MetaLink notes;
Note 451960.1 How To Prevent The Growth Of The Component 'KGH: NO ACCESS' In The Shared Pool When ASMM Is Enabled
Note 742599.1 FREQUENT RESIZE OF SGA

Disabling ASMM will mean memory is no longer swapped between the pools, but it does require the SGA parameters to be set manually.

Setting minimum values for the Shared Pool and Buffer cache when ASMM is enabled means ASMM is still in operation, but any operations that would try to change the SGA component sizes below the minimum values will not occur, leading to less memory needing to pass through the "KGH: NO ACCESS" memory allocation. This is the only operation that does not need a database shutdown/startup.

Increase the time between the resize operations will mean that the default of 30 seconds is increased to a much larger time interval.

Finally, applying the patch or upgrading will fix the code in accordance to Oracle Development recommendations.

Solution 1: Disable ASMM and revert to setting the SGA Manually

1. Determine reasonable values for SGA Parameters DB_CACHE_SIZE, SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE and STREAMS_POOL_SIZE (as apprioriate).
   For further assistance, please review MetaLink note:
   Note 1008866.6 How to determine SGA Size (7.x, 8.0.x, 8i, 9i, 10g)
2. Disable ASMM:
   SQL> alter system set SGA_TARGET=0 scope=spfile;
3. Set the SGA pool sizes manually, using the values you determined from Step 1 (above):
   For example:
   SQL> alter system set SHARED_POOL_SIZE=1G scope=spfile.
   NOTE: Not all the parameters may need to be set, and will default to 0.
4. Shutdown and startup the database, so that ASMM is turned off, and the new manual SGA settings take affect.
   

Solution 2: Leave ASMM enabled, but set minimum values for the Shared Pool and Buffer Cache

1. During a typical, busy period on the database, run the following query:
   SET PAGESIZE 100
   COL COMPONENT FORMAT A25
   COL FINAL_SIZE FORMAT A15
   select component, AVG(FINAL_SIZE) "AVG FINAL", MEDIAN(FINAL_SIZE) "MEDIAN FINAL", MAX(FINAL_SIZE) "MAX FINAL"
   from v$sga_resize_ops
   group by component;
2. For the row "DEFAULT buffer cache", determine which value is greater, "AVG FINAL" or "MEDIAN FINAL".
   Record this value as the Minimum Buffer Cache n.
3. For the row "shared pool", determine which value is greater, "AVG FINAL" or "MEDIAN FINAL".
   Record this value as the Minimum Shared Pool m.
4. Add together the values for the Minimum Buffer Cache & Minimum Shared Pool, and compare this to your current SGA_TARGET or SGA_MAX_SIZE.
5. If the total is greater than SGA_TARGET or SGA_MAX_SIZE, then they will also need to be increased. Determine how big SGA_TARGET or SGA_MAX_SIZE can be, and then implement the increase, for example:
   SQL> ALTER SYSTEM SET SGA_TARGET=nnn SCOPE=BOTH;
6. Set parameter DB_CACHE_SIZE to the value of the Minimum Buffer Cache (as per Step 2 above).
   SQL> ALTER SYSTEM SET DB_CACHE_SIZE=n SCOPE=SPFILE;
7. Set parameter SHARED_POOL_SIZE to the value of the Minimum Shared Pool (as per Step 3 above).
   SQL> ALTER SYSTEM SET SHARED_POOL_SIZE=m SCOPE=SPFILE;
8. Re-start the database.
   
9. Alternately, you can try to perform the memory changes without re-starting the database.
   However, you need to determine what the dynamic settings are first, by running the following query:
   SQL> select component, current_size from v$sga_dynamic_components where component like '% pool' or component = 'DEFAULT buffer cache';
10. If both the "shared pool" and the "DEFAULT buffer cache" are less than the minimum values determined in Steps 2  & 3 above, then you can try using SCOPE=BOTH, when setting the DB_CACHE_SIZE and SHARED_POOL_SIZE.
    

Solution 3: Increase the time between resize operations

1. Review the Solutions from Note 742599.1, in particular, the solution involving parameter "_memory_broker_stat_interval".
2. For your database, determine a reasonable period of time for the delay between resize operations, given the default is 30 seconds.
   
3. Set parameter "_memory_broker_stat_interval" to the time period n you determined in Step 2 (above):
   SQL> ALTER SYSTEM SET "_memory_broker_stat_interval"=n SCOPE=SPFILE;
4. Shutdown and startup the database, so that the underscore parameter takes affect.
   

Solution 4: Apply the fix

10.2.0.1

If you are using v10.2.0.1, upgrade to a minimum of v10.2.0.3 (or, see below).

10.2.0.2

If you are using v10.2.0.2, no bugs have been logged for this version, so it is recommended that you upgrade (see below).

10.2.0.3

1. Download and review the readme and pre-requisites for Patch 6528336
   As of April 2009, the platforms are; Linux x86, IBM AIX 64-bit, HP-UX Itanium.
   
2. If the patch does not exist for your platform, please log a Service Request, requesting a Backport of Bug 6528336 to your platform.
   

10.2.0.4

1. Download and review the readme and pre-requisites for Patch 7189722
   As of April 2009, the platforms are; Linux x86, Linux x86-64, IBM AIX 64-bit, HP-UX Itanium, Sun Solaris SPARC 64-bit.
   
2. If the patch does not exist for your platform, please log a Service Request, requesting a Backport of Bug 7189722 to your platform.

OR Oracle recommends that you upgrade to v10.2.0.5, when it is available for your platform.

References

BUG:5045507 - ASMM - FREQUENT RESIZING OF SHARED POOL & BUFFER CACHE
NOTE:1008866.6 - How to determine SGA Size (7.x, 8.x, 9.x, 10g)
NOTE:1127833.1 - ORA-04031 in 11g & 11gR2, Excess "KGH: NO ACCESS" Memory Allocation
NOTE:451960.1 - How To Prevent The Growth Of The Component 'KGH: NO ACCESS' In The Shared Pool When ASMM Is Enabled
NOTE:742599.1 - High 'cursor: pin S wait on X' and/or 'library cache lock' Waits Generated by Frequent Shared Pool/Buffer Cache Resize Activity

첨부 파일 How to determine you are hitting this issue (2,687.61 KB)

관련 자료 제품 Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition 키워드 SHARED POOL; SIZING; V$SGASTAT; V$SGA_DYNAMIC_COMPONENTS; V$SGA_RESIZE_OPS; VIDEO; DYNAMIC ADV DIAGNOSTIC TOOLS; DYNAMIC ADV TRAINING 오류 ORA-4031; 4031 ERROR