Re: Shared memory error

Agreed.  I'm thinking that the behavior may have changed to alert DBAs that resizes should have been made?

Recognizing that shrinking the Shared Pool can be a bad idea because there are some important data structures there that may need to persist for a while, I tend to unset all parameters for SGA component sizing except SHARED_POOL_SIZE.  When SGA_TARGET > 0, the definition of the other SGA component parameters (such as SHARED_POOL_SIZE, DB_CACHE_SIZE, etc) changes from being a static value to becoming a "floor" value.  That is, when SGA_TARGET > 0, then SGA components can grow (and shrink) but not below the set value.

I prefer to ensure that the Shared Pool will not shrink below a specified size, in order to minimize the inevitable latch/enqueue contention that would come with moving or removing some of the data structures that must persist in a shrinking Shared Pool.  I tend to monitor the history of the size of the Shared Pool in the DBA_HIST_PARAMETER view and try to set to something like the 95th percentile value, if possible.

Hope this helps...

On 6/15/2022 6:02 AM, Chris Taylor wrote:
> 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