Re: Shared memory error

In the thread below one of the responses stating "*sga_target=0 and sga_max_size=1g mean that you have enabled automatic memory management" *is this correct statement? In that case we might be hitting the bug which Lok was pointing to initially i.e. combination of huge pages with AMM. And if this setup is AMM , then that also suggests why the resize operations are happening.

https://community.oracle.com/tech/developers/discussion/4333252/sga-target

On Wed, Jun 15, 2022 at 7:35 PM Pap <oracle.developer35_at_gmail.com> wrote:

> 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