Re: Shared pool error even it shows enough free memory

And we are seeing the wait event "sga:allocation forcing component growth" exactly from the same point of time when these resize operations happen and the ora-4031 failure happens.

On Thu, Jun 2, 2022 at 9:06 PM Lok P <loknath.73_at_gmail.com> wrote:

> Yes Jonathan. It's true that all these failures are happening on the
> operation(loading/fetching data etc) related to the partitioned
> table/indexes. Don't see any written procedure/job which is manually
> fetching the explain plan but I did see a few queries with 'monitor' hint
> in them, not sure if that can cause this , but they have been running like
> that since long. This database does have big partition tables holding
> ~4000+ partitions; those are compressed ones. And as I checked with the
> app team, no recent increase in the number of partitions happened or new
> objects/indexes added to these partitioned tables too. And we
> collect stats using incremental options on these partition tables.
>
> I did "select * from part_tab where 1=2 ; " and it failed with below, the
> index showing the parameter is part of this table only. This is a
> composite partition table with ~4000+ sub-partitions with ~25 bitmap
> indexes and one b-tree index as PK, all are local indexes. So should we
> bump up the sga_target keeping other params the same or should we look for
> another way to fix this up?
>
> ORA-04031: unable to allocate 4096 bytes of shared memory ("shared
> pool","PART_TAB_IX6","pacdHds_kkpaco","kksgaAlloc: firstN")
>
> On Thu, Jun 2, 2022 at 8:19 PM Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
>>
>> That looks like problems with parsing queries involving partitioned
>> tables, possibly handling statistics on indexes.
>>
>> Since it's filling the SGA maybe you're using "explain plan", which
>> demands shared pool memory, rather than running the query and pulling the
>> plan from memory afterwards, which uses PGA memory for the optimisation
>> stages.
>>
>> Have you increased the number of partitions of some object recently, or
>> added a couple of indexes to objects with a large number of partitions; or
>> been testing different ways of writing some queries and using explain plan
>> to check the execution paths.
>>
>> Regards
>> Jonathan Lewis
>>
>>
>>
>> On Thu, 2 Jun 2022 at 13:01, Lok P <loknath.73_at_gmail.com> wrote:
>>
>>> Hello Listers. Its oracle version is 19.11. We are suddenly seeing many
>>> queries failing with Ora-04031 even if we see the "free memory" as ~5GB in
>>> v$sgastat where pool='shared pool'. We then flushed the shared pool and
>>> also increased the shared pool size to 8GB from initial 6GB, and things ran
>>> fine for a couple of hours but we again encountered the same error after a
>>> couple of hours of good run. What could be the cause?
>>>
>>> ORA-04031: unable to allocate 4096 bytes of shared memory ("shared
>>> pool","IDX1","pacdHds_kkpaco","kksgaAlloc: firstN")
>>>
>>>
>>>

--
http://www.freelists.org/webpage/oracle-l