Re: Shared memory error

Thank you Tanel. We have *_enable_shared_pool_durations* set as TRUE. And we don't see much of the resize operations for the node which came across this error. Oracle suggests to set the minimum value for both shared_pool and db_cache to get rid of this ora-4031. So wondering if this will have a similar impact as setting _enable_shared_pool_durations to FALSE? Another thing we noticed is post this error , we are seeing a lot of queries running slow across databases, and there have been no change in plan but the 'cell single block physical read' has been increased significantly and eventually they are going for UNDO(i.e. High transaction table consistent read undo record applied). Can this be related to the memory error anyway?

On Tue, May 4, 2021 at 9:06 PM 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