Re: shared pool waits

Nope, your analysis definitely seems to be correct. I missed this note earlier - thanks for pointing it out.

Chris

On Wed, Sep 22, 2021 at 2:03 PM Pap <oracle.developer35_at_gmail.com> wrote:

>
> 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