Re: Shared memory error

I was under the impression that unless we manually change the shared_pool_size/db_cache_size parameters these values cant be changed themselves by oracle. so ideally dba_hist_parameter should show the same value throughout irrespective of the workload. I agree that resize operation can change(increase but not decrease below the set size) those internally but those would not be reflected in dba_hist_parameters. But what you are referring to is , if we have sga_target set as '0' then these values can well be changed by oracle and that too published in dba_hist_parameters too and can go well below the set value of shared_pool_size/db_cache_size?

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

> 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