Re: Shared memory error

Is there a way(maybe from dba_hist* views) we can fetch the speed at which UNDO read/write is happening now from RECO vs from DATA disk group in the past?

On Wed, May 5, 2021 at 5:17 PM Pap <oracle.developer35_at_gmail.com> wrote:

> We have recently created a new UNDO tablespace on RECO tablespace because
> of space crunch on DATA disk group. So basically all the DML and everything
> happens on DATA whereas UNDO sits on RECO, so can it be the cause of such
> issues anyway?
>
> On Wed, May 5, 2021 at 2:00 PM Pap <oracle.developer35_at_gmail.com> wrote:
>
>> 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