Hi Tanel,
Does your workaround work for 12c databases?
Do you know if there's a way to define a minimum size for each subpool?
My client's database (12.1.0.2) uses 7 subpools and the 7th one is often
too small compared to the 6 others and the ora-04031 happens quite
frequently, even when there's no resize operations in v$sga_resize_ops.
On Tue, May 4, 2021, 17:36 Tanel Poder <tanel@xxxxxxxxxxxxxx> wrote:
Since this is 11.2 and if you don't see shared pool shrink/grow operations
in v$sga_resize_ops - and since the failed allocation sizes are pretty
small (256 & 760 bytes, not 10+ kB and not even the standard 4k extent size
for library cache object heaps), I would suspect that you ran out of memory
in one of the shared sub-sub-pools for session-duration allocations (KKSSP
means "Session Pages" where session-connected things like library cache
lock & library cache pins are kept).
Oracle 12c splits your shared pool subpools into only 2 sub-sub-pools
(durations), but 11.2 splits them to 4.
What's the* _enable_shared_pool_durations* value in your env?
Also, you can take a look into number of shared pool sub-pools and their
free memory (although it doesn't show fragmentation info):
-
https://tanelpoder.com/2009/06/04/ora-04031-errors-and-monitoring-shared-pool-subpool-memory-utilization-with-sgastatxsql/
And since an ORA-4031 error should dump some shared pool heap details to a
tracefile, you can run heapdump analyzer on it, to see how much free memory
you had in the sub-sub-heaps at the time:
-
https://tanelpoder.com/2009/01/02/oracle-memory-troubleshooting-part-1-heapdump-analyzer/
On versions below 12c, when having unexplained shared pool memory errors
(ORA-4031s) and you don't want to go deeper with dynamic tracing
<https://github.com/tanelpoder/tpt-oracle/blob/master/dtrace/trace_kghal.sh>
and things like x$ksmlru
<https://github.com/tanelpoder/tpt-oracle/blob/master/ksmlru.sql>, then a
common workaround is to set *_enable_shared_pool_durations = false* (with
the usual comments that you should get some blessing from Oracle support or
by a MOS search for that parameter & documented bugs/issues).
In past (perhaps back in 9i, 10g days), I sometimes worked around the
unexplained shared pool issues (bugs), by reducing the *_kghdsidx_count*
value (and sometimes setting it to 1), to avoid the complexity (and new
bugs) of the shared pool subpools completely (but on 11.2.0.4 it probably
works ok enough...)
--
Tanel Poder
https://tanelpoder.com/events/
On Tue, May 4, 2021 at 9:02 AM Pap <oracle.developer35@xxxxxxxxx> wrote:
Hello Listers, It's a 4 node RAC database with version 11.2.0.4. It's
using ASMM. We saw queries failing with ORA-04031 error twice in the past ,
even if the sum of all the components of the shared_pool was around ~15GB
during the time(with sga_target being set as ~100GB on each node). We are
not seeing any such spike/variation in overall usage of the shared_pool
components but still getting this below error intermittently. .
And i remember, we used to see the same error in few other databases in
the past, but there we used to see the component "KGH-NO ACCESS" of shared
pool used to grow rapidly chewing up all the sga memory. But in this case
we are not seeing such symptoms and we seem to have free memory left while
it's errored out. So wondering if there is any associated bug?
*Parameters from V$parameter:-*
sga_max_size - 120GB
sga_target - 100GB
shared_pool_size - 0
memory_target - 0
*Error:*
ORA-04031: unable to allocate 760 bytes of shared memory ("shared
pool","unknown object","KKSSP^847","kglss")
ORA-04031: unable to allocate 256 bytes of shared memory ("shared
pool","unknown object","KKSSP^1807","kgllk")
ORA-04031: unable to allocate bytes of shared memory ("","","","")
Regards
Pap