Re: Shared pool error even it shows enough free memory

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:
class="quotelev1">>
> 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