Re: Shared pool error even it shows enough free memory

>> So should we bump up the sga_target keeping other params the same or
should we look for another way to fix this up?

Fix WHAT up ?
You have one symptom but you haven't identified the problem so you can't fix it.

Since you seem to have a reproducible and very simple example why don't you:

alter session set events '10053 trace name context forever; select /*+ new_query */ * from part_tab where 1 = 2; alter session set events '10053 trace name context off;

and have a look at the trace file - you will find under the heading "SINGLE TABLE ACCESS PATH" that Oracle has tried to cost the query for a table scan and for each of the 26 possible indexes, and in the "BASE STATISTICAL INFORMATION" that is has loaded some information about the table and indexes - you may find that each index takes a few lines to say something like "using composite stats", you may find several thousand lines of text about partition and/or subpartition stats, and that might indicate that some of your stats collections procedures have failed and left Oracle trying to create composite stats dynamically from (sub)partition stats - and that MIGHT be the problem.

Regards
Jonathan Lewis

On Thu, 2 Jun 2022 at 16:50, Lok P <loknath.73_at_gmail.com> wrote:

> And we are seeing the wait event "sga:allocation forcing component growth"
> exactly from the same point of time when these resize operations happen and
> the ora-4031 failure happens.
>
> On Thu, Jun 2, 2022 at 9:06 PM Lok P <loknath.73_at_gmail.com> wrote:
>
>> Yes Jonathan. It's true that all these failures are happening on the
>> operation(loading/fetching data etc) related to the partitioned
>> table/indexes. Don't see any written procedure/job which is manually
>> fetching the explain plan but I did see a few queries with 'monitor' hint
>> in them, not sure if that can cause this , but they have been running like
>> that since long. This database does have big partition tables holding
>> ~4000+ partitions; those are compressed ones. And as I checked with the
>> app team, no recent increase in the number of partitions happened or new
>> objects/indexes added to these partitioned tables too. And we
>> collect stats using incremental options on these partition tables.
>>
>> I did "select * from part_tab where 1=2 ; " and it failed with below, the
>> index showing the parameter is part of this table only. This is a
>> composite partition table with ~4000+ sub-partitions with ~25 bitmap
>> indexes and one b-tree index as PK, all are local indexes. So should we
>> bump up the sga_target keeping other params the same or should we look for
>> another way to fix this up?
>>
>> ORA-04031: unable to allocate 4096 bytes of shared memory ("shared
>> pool","PART_TAB_IX6","pacdHds_kkpaco","kksgaAlloc: firstN")
>>
>> On Thu, Jun 2, 2022 at 8:19 PM Jonathan Lewis <jlewisoracle_at_gmail.com>
>> wrote:
>>
>>>
>>> That looks like problems with parsing queries involving partitioned
>>> tables, possibly handling statistics on indexes.
>>>
>>> Since it's filling the SGA maybe you're using "explain plan", which
>>> demands shared pool memory, rather than running the query and pulling the
>>> plan from memory afterwards, which uses PGA memory for the optimisation
>>> stages.
>>>
>>> Have you increased the number of partitions of some object recently, or
>>> added a couple of indexes to objects with a large number of partitions; or
>>> been testing different ways of writing some queries and using explain plan
>>> to check the execution paths.
>>>
>>> Regards
>>> Jonathan Lewis
>>>
>>>
>>>
>>> On Thu, 2 Jun 2022 at 13:01, Lok P <loknath.73_at_gmail.com> 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