Re: Shared pool error even it shows enough free memory

Thank You Tim.

We have shared_pool_reserved_size set as 800M. And I believe apart from sga_max_size(which is the hard limit) all others are just minimum values here but not the upper limit. We have
Shared_pool size - 8GB
Sga_max_size - 40GB
sga_target- 40GB
DB_cache_size- 13GB
stream_pool_size- 256MB
Java_pool_size- 0
large_pool_size-0

We do have enough physical memory on that box, But I am wondering why suddenly we started seeing the resize operations for shared_pool and db_cache and both the operation errored out without completing. And that to 'free memory' is still showing up in v$sgastat as ~5gb+. And in the v$sga_resize_ops the "Shared pool " component keeps erroring out with ~14GB while trying to 'GROW' and similarly 'DB_CACHE_SIZE' component keep failing while trying to "shrink" beyond "25" GB. I am thinking if it's buggy or we really have to bump up sga_target? By the way, I am also trying to see if I can see some spike pattern/ DB activity which is playing a role here.

On Thu, Jun 2, 2022 at 6:23 PM 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