Re: Shared pool error even it shows enough free memory

Lok,

In hindsight, there really is no mystery when new errors of this sort start occurring.  All it takes is one user with an analytic tool generating SQL statements of 100,000 lines of text or more to create a cursor that is several dozen megabytes in size.  Shoving one of those cursors into the Shared Pool will cause all kinds of resizing operations to increase the size of the Shared Pool.  If you have auto SGA mgmt enabled, it might not give an ORA-04031 error, and the Shared Pool will be expanded, and the SQL parse operation completes successfully.  Then, as that humongous SQL statement progresses to the SQL open and SQL fetch, you'll see auto SGA mgmt start demanding more Buffer Cache, possibly at the cost of the Shared Pool.

As a DBA, there is nothing you can do to prevent this happening.  As a user or developer, there is nothing you can do to avoid this happening.  Nothing is wrong with the database, nothing is wrong with the analytic application.  It is just what happens.  It is why auto SGA mgmt was developed.

So the probable answer to "/...why suddenly we started seeing .../" is because someone somewhere started doing one thing new.  Workloads aren't static.

Hope this helps,

-Tim

On 6/2/2022 7:29 AM, Lok P wrote:
> 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