Re: Shared pool error even it shows enough free memory

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