Re: Shared pool error even it shows enough free memory

This query is failing for node-1 and working fine on node-2. Our application runs on node-1, so it seems node-1 memory is having issues but not node-2. So then we tried generating 10053 traces for node-1 by running that simple 'select 1...' query . It's as below. I see it just failed at the start while doing some 'group by validation'. It didn't go much before failure.

https://gist.github.com/oraclelearner/1583dc1ff3350fcb9c104d3d77064efc

Then we did a clean stats gathering as below from another node-2 for this object and it succeeded. Then tried running the same simple query (SELECT 1 as OUT FROM tab_part WHERE 1=2; ) on node-1 that is still failing with ora-4031. Now we are thinking to bounce node-1 and set the _shared_pool_reserved_pct =15 and bounce and see the behaviour.

exec Dbms_stats.gather_table_stats(ownname=>'USER1',tabname=>'TAB_PART', degree=> 16);

Wit below table level preference:-

OWNER TABLE_NAME PREFERENCE_NAME PREFERENCE_VALUE USER1 TAB_PART GRANULARITY ALL USER1 TAB_PART INCREMENTAL TRUE USER1 TAB_PART INCREMENTAL_STALENESS USE_STALE_PERCENT USER1 TAB_PART METHOD_OPT FOR ALL COLUMNS SIZE REPEAT On Thu, Jun 2, 2022 at 9:34 PM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> >> 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