Re: shared pool waits

As I just shared the AWR section it doesn't show much hard parsing. So do you think DBMS_SHARED_POOL.KEEP will really help us here?

On Tue, Sep 21, 2021 at 7:37 PM Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

> On 9/20/21 15:25, Pap 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.
>
>
> How about pinning the procedure in the SGA? That seems to be the purpose
> of DBMS_SHARED_POOL.KEEP procedure.
>
> --
> 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