Re: Shared memory error

Thank you Tim.
We are seeing there were a lot of resize operations in the past that happened with status as 'COMPLETE'. Why is it failing suddenly then? Also these setup of shard_pool_size and db_cache_size params are just the minimum sizes set to safeguard and it's up to oracle to bump up that value based on the workload. Please correct me if I'm wrong. Also we are seeing in dba_history_parameter there is a drop in value of db_cache_size happening from ~3.5GB to 2GB, but no such changes happened for shared_pool_size. So ideally this should not be the cause of this sudden error. Correct me wrong?

On Wed, Jun 15, 2022 at 6:00 PM Tim Gorman <tim.evdbt_at_gmail.com> wrote:

> SGA_TARGET is zero, so of course a resize operation failed.
>
> Please reset SGA_TARGET to equal SGA_MAX_SIZE and please consider
> unsetting all of the other SGA parameters except SHARED_POOL_SIZE, to allow
> automatic SGA management to do what it is supposed to do? Please unset the
> underscore parameters too.
>
> The days of micromanaging SGA components by guesses and wishes were over
> decades ago.
>
>
>
> On 6/15/2022 2:20 AM, Pap wrote:
>
> We are hitting below Ora-4031 in one customer database. Even we have 'free
> memory' available (~2GB) in shared_pool during the error. And also the size
> of the "KKSSP" sub pool and it was showing null value only. Its version
> 19.11 database has the below memory setup. We do see the resize operation
> in which the shared_pool_size was 12GB and was GROWing and db_cache was 2GB
> and was tryin to SHRINK and both errored out.
>
> ORA-04031: unable to allocate 12312 bytes of shared memory ("shared
> pool","unknown object","KKSSP^3497","kglseshtTable")
>
> memory_target - 0, memory_max_target - 0,sga_max_size - 35GB,sga_target -
> 0,shared_pool_size - 8GB,shared_pool_reserved_size -
> 1.2GB,_shared_pool_reserved_pct - 15,db_cache_size- 20GB
>
>
>

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