Re: shared pool waits

Rolling invalidate sounds like you’re gathering statistics on the dependent tables with rolling cursor invalidation. The default behaviour is that it happens within 5 hours (with some randomness so that you don’t reparse everything at once). See
https://blog.toadworld.com/why-my-execution-plan-has-not-been-shared-part-ii

It’s an issue if your parse waits are significant enough to be an issue.

What is the stats gathering plan for the tables in this query? Anything fun like partitioning?

Thanks,
Andy

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

> 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