Re: Shared pool error even it shows enough free memory

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