Re: Shared pool error even it shows enough free memory

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