Re: shared pool waits

Hello Andy, do you say we should never use no_invalidate false to gather stats. or is there some specific use case for that? And as Pap pointed , and my understanding was also, this invalidate cursor scenario will result in cursor invalidation/query reoptimization and thus should be called as hard parses only. But as per initial data Pap shared, the in_hard_parse flag is showing mostly as 'N' for most samples, and it's matching your theory that these are not actually hard parses but soft parses. Can you please clarify a bit more on that, as because in this scenario the query will go through the reoptimization phase, so won't that call be a hard parse ?

Regards
Lok

On Thu, Sep 23, 2021 at 8:12 PM Pap <oracle.developer35_at_gmail.com> wrote:

> Thank you Andy.
>
> You mentioned '*It is not a hard parse, but it is a parse*', so what my
> understanding was two types of parsing hard parse and soft parse, if the
> underlying statistics gets updated , with no_invallidate False, it will
> make the existing plan cursor invalidated immediately and thus those have
> to go through the optimization phase again(although it doesn't have to be
> reloaded to shared pool again) and that is what hard parse means. Is my
> understanding wrong here?
>
> And if i get it correct, what you mean is , no_invalidate should be kept
> as default i.e AUTO so that it won't force parse. But then isn't it that ,
> there won't be any meaning of stats gather timing, say for volatile tables
> (truncate+load) or say partition tables having data moved to a new days
> partition , stats has to be accurate immediately after the load/dml else
> optimizer may end up assuming zero stats and a bad plan thus. Can you guide
> if in these cases too no_invalidate should still be kept 'Auto' and these
> scenarios should be handled in certain different ways?
>
> On Thu, Sep 23, 2021 at 5:12 PM Andy Sayer <andysayer_at_gmail.com> wrote:
>
>> Why are the exact stats so important? Do you have queries that need to do
>> different plans between each statistics gathering? You might want to
>> benchmark what happens when you don’t gather statistics so much or at least
>> when you opt for the new automatic delayed invalidation.
>>
>> It is not a hard parse, but it is a parse.
>>
>> Cursors will be invalidated as their dependent objects have their stats
>> gathered. I don’t recall how Oracle commits statistics with multiple
>> partitions but it is completely possible for each partition to have new
>> statistics independently, and if your statement doesn’t specify the
>> petition it’s using (using the extended partition syntax) it will be
>> invalidated.
>>
>> Thanks,
>> Andy
>>
>> On Thu, 23 Sep 2021 at 12:22, Pap <oracle.developer35_at_gmail.com> wrote:
>>
>>> Yes so the underlying table is partitioned table and the stats getting
>>> gathered on this table during the issue period. And we do have
>>> no_invalidate set as false in table level ,as we want for some queries to
>>> use the exact stats information to be used by the
>>> optimizer immediately after the gathering. And as the table is hash
>>> partitioned so when it's gathered all the partitions(even its incremental)
>>> were being gathered and it takes a bit of time for that.
>>>
>>> But in that case ,if hard parsing is the only issue, why are we not
>>> seeing IN_HARD_PARSE column as 'Y' for the most number of sample counts for
>>> this sql. Also if you see the time model statistics section which i
>>> shared here , its showing hard parse elapsed time as .6% of the DB time. So
>>> can it/hard parse be really the issue? But even then, what should we do to
>>> handle this issue in such a situation?
>>>
>>> Another question I was having was , isn't it true that the stats will be
>>> shared or say the dependent child cursor will be invalidated only after the
>>> final global stats rollup happens rather with individual gathering on each
>>> partition?
>>>
>>> On Thu, Sep 23, 2021 at 4:07 PM Andy Sayer <andysayer_at_gmail.com> wrote:
>>>
>>>> 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