Re: Shared pool error even it shows enough free memory

Tried re-generating the 10053 trace for the same query , and this time seeing the queries around X$ views only in the trace.

https://gist.github.com/oraclelearner/4e6ab38fcd39cdecdf0ae4a602b99671

On Fri, Jun 3, 2022 at 12:58 PM Lok P <loknath.73_at_gmail.com> wrote:

> 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