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@xxxxxxxxx> 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")
