Re: Shared pool error even it shows enough free memory

DBA_HIST_MEMORY_RESIZE_OPS shows it (shared_pool_size) keeps trying to "grow" continuously but endup in status 'ERROR'. I am also seeing initial_size, target_size and final_size all noted as 14GB for all those trials. And similarly db_cache_size is going through "shrink" operation continuously and showing status as 'ERROR'. initial_size, target_size and final_size are all noted as 25GB.

We have shared_pool_size set as 8GB. We have memory_target and memory_max_target set as 0. Sga_target and sga_max_size both set as 40GB, dba_cache_size set as 13GB. Should we bump up db_cache_size to more?

On Thu, Jun 2, 2022 at 5:31 PM 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