Re: Shared memory error

Well, apparently in 11.2, Oracle's wisdom was to automatically grow the shared pool regardless if you have the target parameters set to avoid 4031 errors. (Which is ridiculous IMHO)

They should have gone with more aggressive purging of the shared pool instead of holding on to so much crap.

You can disable this behavior apparently: (ref: Severe Performance Problems Due to Sudden drop/resize/shrink of buffer cache (Doc ID 1344228.1))

Note that this note specifically says:

*This mimics the behavior in 9i, and the shared pool will no longer be able to shrink.The advantage of this is that the performance issues documented in this note can be avoided. A duration will not encounter memory exhaustion while another duration has free memory.*

Which is completely wrong-headed (again, IMHO). You should *never, ever *want to shrink the shared pool due to pressure it puts on a very busy database and all the ramifications that go along with shrinking the library cache. They should have gone with dropping objects from the shared pool as soon as they are finished. Example: PRVTM used by partition exchanges - that segment of the shared pool will eat up huge amounts of space and never get freed (yes it will get reused, but not freed). So dumb (though I suspect there's technical coding reasons behind this approach that limits them to certain solutions).

As far as the change where resize operations occur even when target parameters are set to 0 , this galls me terribly because the goal in setting target parameters to 0 is usually to specifically *avoid *resize operations entirely. So Oracle (in their wisdom) said ' *we're just going to ignore what the administator is attempting to do because we can't manage the shared pool efficiently'.* Ref: SGA Re-Sizes Occurring Despite AMM/ASMM Being Disabled (MEMORY_TARGET/SGA_TARGET=0) (Doc ID 1269139.1)

Chris

On Wed, Jun 15, 2022 at 9:02 AM Chris Taylor < christopherdtaylor1994_at_gmail.com> 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