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@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxx>
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@xxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
