Re: Shared memory error

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_at_tanelpoder.com> 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_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