Re: shared pool waits

Thank you Andy. If I check now, I am seeing ~11 entries/child cursors in gv$sql_shared_cusor and all of those ~10 of those having rolling_invalid_mismatch column as 'Y' and same 10 are also having 'purged_cursor' column as 'Y'. So is it an issue? Or maybe we have to see what it looks like when the issue appears i.e. when ~84 child cursors are created.

On Thu, Sep 23, 2021 at 3:31 PM Andy Sayer <andysayer_at_gmail.com> wrote:

> 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