ORA-04031 with Frequent Resizing of Shared Pool & Buffer Cache with ASMM on 10.2.0.1 (Doc ID 461160.1)

Last updated on MARCH 19, 2009

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.1
This problem can occur on any platform.
Applicable only on 10.2.0.1.0 release.

Symptoms

Frequent ORA-4031 even after increasing SGA_TARGET, Shared_pool

Things observed:

1) ASMM is being used: Sga_target >0

2) 'KGH: NO ACCESS' increasing in shared pool

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

We can see High  KGH: NOACCESS entries in the shared pool.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms