Re: Shared memory error
Date: Tue, 4 May 2021 11:36:08 -0400
Message-ID: <CAMHX9JLfexAbXgp7YnwBq=mzsFOHB4mw4a_btwFumkRFvyVdZw_at_mail.gmail.com>
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):
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:
-
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
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...)
https://tanelpoder.com/2009/01/02/oracle-memory-troubleshooting-part-1-heapdump-analyzer/
<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).
--
Tanel Poder
https://tanelpoder.com/events/
On Tue, May 4, 2021 at 9:02 AM Pap <oracle.developer35_at_gmail.com> 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
>
--
http://www.freelists.org/webpage/oracle-l
Received on Tue May 04 2021 - 17:36:08 CEST