Re: Shared memory error

Tim,

Prior to 19c (not sure about 19c), there shouldn't be *any *resize operations when target params are 0. That should disable resize operations if I'm not completely mistaken.

Chris

On Wed, Jun 15, 2022 at 8:30 AM 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