Re: shared pool waits

You mentioned the ash top showing in_hard_parse as 'N' means it's not doing hard parse for that sql. So why do you think it's parsing issue and your non default no_validate parameter set at table level could be the cause of this issue?

On Tue, Sep 21, 2021 at 12:55 AM Pap <oracle.developer35_at_gmail.com> wrote:

> Hi , We have a customer application in which we see high wait events like
> 'cursor:mutex ' and 'library cache lock' for a select query occasionally
> and thus a specific functionality impacted. This select query(which is part
> of a plsql procedure) is quick query which runs ~5 million times/hr. But
> even though number of execution is same mostly throughout the day, it still
> went through these odd wait events making the per execution time went
> higher for around ~15 minutes duration causing slowness. And during this
> period, the ASH shows fro this query, the value of column in_hard_parse as
> 'N' but in_parse as 'Y' and 'N' both. And we saw we were having stats
> gather running on that base object during same time. We have no_invalidate
> set as 'FALSE" as table stats preference, So wanted to understand from
> experts, can it be really because of 'parsing' issue and we should delete
> this no_invalidate preference so that it can inherit the default global
> preference i.e no_invalidate=>auto? The database version is 19C.
>

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