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
select START_TIME, component, oper_type, oper_mode, initial_size/1024/1024 "INITIAL", FINAL_SIZE/1024/1024 "FINAL", END_TIME
where component in ('DEFAULT buffer cache', 'shared pool') and status = 'COMPLETE'
order by start_time, component;
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
Note 1127833.1 ORA-04031 in 11g & 11gR2, Excess "KGH: NO ACCESS" Memory Allocation