Re: shared pool waits

  • From: Pap <oracle.developer35@xxxxxxxxx>
  • To: Chris Taylor <christopherdtaylor1994@xxxxxxxxx>
  • Date: Wed, 22 Sep 2021 23:33:03 +0530

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@xxxxxxxxx> 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@xxxxxxxxx> 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.


Other related posts: