Re: Shared pool error even it shows enough free memory

_at_Lok also see this bug which may match your issue:

ORA-4031 on Shared Pool with High Allocation in PRTMV Even if Patch 20635353 is Applied (Doc ID 2517921.1)

That matches your (""pacdHds_kkpaco","kksgaAlloc: firstN") arguments.

PRTMV is used (if IIRC) by partition exchange DDLs - I've run into that bucket a few times having issues.

Chris

On Fri, Jun 3, 2022 at 3:19 PM Chris Taylor < christopherdtaylor1994_at_gmail.com> wrote:

> I may be alone here, but the whole SGA auto management is so broken up to
> 12.2 at least.
>
> You're going to steal my buffer cache to grow the shared pool? Lazy
> solution. How about quit breaking the shared pool into buckets and
> splintering it to hell & gone.
>
> Turning off SGA memory automatic resizing and scoping your db_cache_size
> and shared_pool_size, shared_pool_reserved_size (25-30% of
> shared_pool_size) will eliminate so many issues on a busy system.
>
> It was so bad at one time on one of our databases, that our buffer cache
> had shrank to 1.2 GB and the shared pool was over 30GB. So *wrong. *
>
> I'm not an old fuddy duddy either. I like "stuff that works" . Auto
> resize operations on a very busy database is just horrible. Wait till the
> database tries to *shrink your shared pool *automatically on a busy
> system and the thing locks up - it's *fantastic.*
>
> Chris
>
> On Thu, Jun 2, 2022 at 8:53 AM Tim Gorman <tim.evdbt_at_gmail.com> wrote:
>
>> Lok,
>>
>> Not sure what parameters you're configuring, it sounds like a job for
>> setting the SHARED_POOL_RESERVED_SIZE parameter
>> <https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/SHARED_POOL_RESERVED_SIZE.html#GUID-8B0F0388-B135-4661-84A0-1C3C78360E71>.
>> As documented, the reserved area of the Shared Pool is intended to
>> accommodate larger objects, and 4096 bytes probably constitutes a larger
>> object in the Shared Pool. Historically, I think a "larger object in the
>> Shared Pool" was defined as 4000 bytes or greater, if I recall correctly?
>>
>> I'd also like to caution against the obsessive setting of SGA-related
>> parameters. For at least 20 years now, Oracle database has supported
>> automatic SGA management by setting the SGA_TARGET > 0, which converts most
>> of the other SGA-related parameters into "floor" values for their
>> respective components, rather than hard setting values. By "floor" values,
>> auto SGA management allows the component to be enlarged and shrunk, but
>> never allows it to be shrunk smaller than the setting of the parameter.
>>
>> My recommendation is that, if you set SHARED_POOL_RESERVED_SIZE, then be
>> sure to set SGA_TARGET to enable auto SGA management (if it is isn't
>> already set), and then unset *all *other SGA-related parameters, other
>> than setting SHARED_POOL_SIZE = 5G as suggested by your previous analysis
>> in V$SGASTAT. This will allow auto SGA management to do its job, and also
>> set a "floor" value for the Shared Pool, along with the newly-set size of
>> the reserved area. If the Shared Pool needs to grow larger than 5 GB,
>> especially with a larger-than-default value of SHARED_POOL_RESERVED_SIZE,
>> it should be able to do so.
>>
>> You can monitor the auto SGA management in action by querying
>> V$SGA_RESIZE_OPS. If you observe repetitive grow/shrink operations between
>> two components (i.e. often Shared Pool vs Buffer Cache), you can attempt to
>> dampen that activity either by increasing SGA_TARGET (if there is enough
>> physical memory onboard to do that), or by gradually increase the parameter
>> setting of one of the two competing components, to raise the "floor" for
>> that component. Be gentle and be patient. If you're accustomed to
>> steering a boat, then you'll know that you only correct your steering
>> halfway, and the same is true here.
>>
>> There might be no way to eliminate this back-and-forth activity observed
>> in V$SGA_RESIZE_OPS, as it might be due to normal operations such as
>> parsing an enormous SQL cursor (i.e. increasing Shared Pool) which of
>> course is followed by the execution of that enormous SQL cursor (i.e.
>> increasing Buffer Cache), so be slow and cautious with any adjustments,
>> always bearing in mind that the symptoms you're seeing (i.e. repetitive
>> grow/shrink) could be the "natural rhythm" of the application, and there is
>> never a point where the automatic SGA management finds "equilibrium" and
>> ceases adjusting the sizes of the SGA components. Observing this reality
>> makes the foolhardiness of attempting to impose a steady state on the sizes
>> of the various SGA components by hard-setting all of the parameters
>> apparent, and reveals the genius behind automatic SGA management.
>>
>> Hope this helps,
>>
>> -Tim
>>
>>
>>
>> On 6/2/2022 5:01 AM, Lok P wrote:
>>
>> Hello Listers. Its oracle version is 19.11. We are suddenly seeing many
>> queries failing with Ora-04031 even if we see the "free memory" as ~5GB in
>> v$sgastat where pool='shared pool'. We then flushed the shared pool and
>> also increased the shared pool size to 8GB from initial 6GB, and things ran
>> fine for a couple of hours but we again encountered the same error after a
>> couple of hours of good run. What could be the cause?
>>
>> ORA-04031: unable to allocate 4096 bytes of shared memory ("shared
>> pool","IDX1","pacdHds_kkpaco","kksgaAlloc: firstN")
>>
>>
>>
>>

--
http://www.freelists.org/webpage/oracle-l