Re: shared pool waits

84 children is quite a lot. Have you checked what the parse reasons were in v$sql_shared_cursor ? Worth googling the reasons with inurl: https://hourim.wordpress.com/

Hope that helps,
Andy

On Thu, 23 Sep 2021 at 10:31, Pap <oracle.developer35_at_gmail.com> wrote:

> In our case , optimizer_adaptive_plans TRUE and
> optimizer_adaptive_statistics as FALSE. But in any case, if this is doing
> high number of soft parses and that is causing issue, wont this resulted
> into high number of child cursor or version count. The max number of
> version count for this sql i saw is ~84 during this ~15 minutes period for
> ~190K execution of this select query. But yes there is no change in plan
> its keep using same plan for all the executions. Is this really pointing to
> an issue related to adaptive feature or parsing?
>
> On Thu, Sep 23, 2021 at 3:12 AM Mladen Gogala <gogala.mladen_at_gmail.com>
> wrote:
>
>>
>> On 9/22/21 11:01, Chris Taylor wrote:
>> > They came back with this - which is interesting because the
>> > application issues pretty much the same SQL all the time - so why
>> > would the SQL be invalid/unable to parse? Odd.
>>
>>
>> The answer are adaptive plans. You get cardinality feedback and your
>> plan "adapts". In English, that means re-executing a soft parse.
>>
>> --
>> Mladen Gogala
>> Database Consultant
>> Tel: (347) 321-1217
>> https://dbwhisperer.wordpress.com
>>
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>

--
http://www.freelists.org/webpage/oracle-l