Re: shared pool waits

Thanks Chris for sharing the info.

However if you see i did posted the 'time mode statistics' section of the AWR from the issue period in this email trail before. And I am copying it again as below. It shows 'failed parse elapsed time' as almost zero. But the bug which you mentioned i.e. 1353015.1 is showing that figure higher to be around ~66% in that doc at least. So it seems in our case it's not really because of parsing failure. Correct me if I am wrong. Time Model Statistics
DB Time represents total time in user calls DB CPU represents CPU time of foreground processes Total CPU Time represents foreground and background processes Statistics including the word "background" measure background process time, therefore do not contribute to the DB time statistic Ordered by % of DB time in descending order, followed by Statistic Name Statistic Name Time (s) % of DB Time % of Total CPU Time sql execute elapsed time 25,785.60 98.44 DB CPU 10,903.76 41.63 96.61
parse time elapsed 7,757.12 29.61
connection management call elapsed time 195.45 0.75 hard parse elapsed time 176.07 0.67
PL/SQL execution elapsed time 166.99 0.64 Tablespace encryption elapsed time 123.76 0.47 Tablespace encryption cpu time 118.03 0.45 1.05 hard parse (sharing criteria) elapsed time 43.45 0.17 RMAN cpu time (backup/restore) 15.04 0.06 0.13 sequence load elapsed time 13.71 0.05
PL/SQL compilation elapsed time 7.26 0.03 hard parse (bind mismatch) elapsed time 3.32 0.01 repeated bind elapsed time 2.77 0.01
failed parse elapsed time 0.07 0
inbound PL/SQL rpc elapsed time 0.02 0
DB time 26,194.00
background elapsed time 385.8
background cpu time 382.58 3.39
total CPU time 11,286.34

On Wed, Sep 22, 2021 at 8:31 PM Chris Taylor < christopherdtaylor1994_at_gmail.com> wrote:

> I know there's been a lot of comments on this - but we had a similar issue
> this week on our ADG standby so I opened a case with Oracle.
>
> 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.
>
> But I set the event and have been looking to see if it recurs.
>
> I point this out because maybe you have the same issue with SQLs that are
> somehow "fubar" and Oracle isn't able to parse them - I had no idea that
> bad parsing could cause shared pool contention.
>
> Customer Proposed Action Plan
> 1) Failed Parses
>
> Description
> -----------
> Please execute the following actions:
>
>
> 1) Set event 10035
>
> ALTER SYSTEM SET EVENTS '10035 trace name context forever, level 1';
>
>
> 2) When the event is set, any statement that fails to parse as a result of
> an error, will be documented in the alert.log
>
>
> 3) Fix the application to issue valid SQL.
>
>
> 4) For details refer Doc ID 1353015.1
> <https://support.oracle.com/epmos/faces/DocumentDisplay?parent=SrDetailText&sourceId=3-27094059801&id=1353015.1>
>
> Details
> -------
> None
>
> Justification
> -------------
> This database instance is spending too much time in failed parse activity.
> Failed parses when massive can lead the database to an unexpected behavior,
> usually related to mutexes.
> There is a high parse time activity in this database and 16.68(%) is
> accounted to failed parses.
>
> Reference
> ---------
> How to Identify Hard Parse Failures ( Doc ID 1353015.1
> <https://support.oracle.com/epmos/faces/DocumentDisplay?parent=SrDetailText&sourceId=3-27094059801&id=1353015.1>
> ),
> High Waits for 'library cache lock' and 'library cache: mutex X' Due to
> Parse Failures When Using JDBC ResultSet.TYPE_SCROLL_SENSITIVE ( Doc ID
> 1566018.1
> <https://support.oracle.com/epmos/faces/DocumentDisplay?parent=SrDetailText&sourceId=3-27094059801&id=1566018.1>
> ),
> Troubleshooting 'cursor: pin S wait on X' waits. ( Doc ID 1349387.1
> <https://support.oracle.com/epmos/faces/DocumentDisplay?parent=SrDetailText&sourceId=3-27094059801&id=1349387.1>
> )
>
> On Mon, Sep 20, 2021 at 3:25 PM 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