AW: shared pool waits

From: <ahmed.fikri_at_t-online.de>
Date: Tue, 21 Sep 2021 12:23:59 +0200 (CEST)
Message-ID: <1632219838996.2202261.e52cd9ae7b3e1be03c3d613c11ec7caba87b442e_at_spica.telekom.de>

I think in the web there are a lot of articles about this. Bugs 21834574, 5184776,....
None of them proposed that forcing a hard parse would help..      

-----Original-Nachricht-----
Betreff: Re: shared pool waits
Datum: 2021-09-21T10:46:25+0200
Von: "Pap" <oracle.developer35_at_gmail.com> An: "ahmed.fikri_at_t-online.de" <ahmed.fikri_at_t-online.de>      

Thank you Ahmed. You pointed out this to be an Oracle bug. Can you please shed some more on that regard. What the bug exactly is and the situation when it impacts.  

And yes, this database is RAC but this sql is executed from one instance only as per the service configured. We normally see this issue when stats gather on the underlying object which is executing from another instance. Can this be pointing to something else?

On Tue, Sep 21, 2021 at 1:32 PM ahmed.fikri_at_t-online.de <mailto:ahmed.fikri_at_t-online.de> <ahmed.fikri_at_t-online.de <mailto:ahmed.fikri_at_t-online.de> > wrote:   You mentioned database 19c. I think the relevant point is whether or not   you are using RAC. From my observation, this is typical of RAC. The   problems observed do not occur every day, but when it does happen then   jobs that were only 2-3 hours in duration take more than 20 hours and   removing the SQLs that were causing the problem from the shared pool   solved the problem.    

  Best regards
  Ahmed                

  -----Original-Nachricht-----
  Betreff: AW: shared pool waits
  Datum: 2021-09-21T09:28:25+0200
  Von: "ahmed.fikri_at_t-online.de <mailto:ahmed.fikri_at_t-online.de> " <   ahmed.fikri_at_t-online.de <mailto:ahmed.fikri_at_t-online.de> >   An: "list, oracle" <oracle-l_at_freelists.org
<mailto:oracle-l_at_freelists.org> >
           

  indeed hard parse in this case is better than waiting eternity due to   oracle bugs.            

  -----Original-Nachricht-----
  Betreff: Re: shared pool waits
  Datum: 2021-09-21T09:11:20+0200
  Von: "Pap" <oracle.developer35_at_gmail.com
<mailto:oracle.developer35_at_gmail.com> >
  An: "ahmed.fikri_at_t-online.de <mailto:ahmed.fikri_at_t-online.de> " <   ahmed.fikri_at_t-online.de <mailto:ahmed.fikri_at_t-online.de> >            

  Not getting it fully though. If we purge a sql from cursor cache, doesn't   it mean there will be hard parsing and that will cause more contention   and take more CPU to parse subsequently? Though I am also trying to   understand Lok's point, as we should have noticed the in_hard_parse flag   as 'Y' in case this issue would have been caused by hard parse.    

  But again , when I see the P1 and P1text while the ASH shows the event as   'library cache:mutex x' or 'cursor mutex s', they are pointing to the   same sql_text only. Does that mean even doing soft parsing only but   because of the so many number of executions/soft parses we are suffering?   But then I am wondering why do we see these issues mainly while stats   gathering is running from another session on the same underlying object?   How can this be related?    

  The query looks like below.    

  SELECT MAX (ID1) FROM TAB1 WHERE C1= :B1 AND C2 = :B2

---

  | Id | Operation                    | Name          | Rows | Bytes | Cost

  (%CPU)| Time | Pstart| Pstop |

---

  |  0 | SELECT STATEMENT             |               |      |      |    4
  (100)|         |      |      |
  |  1 | PARTITION RANGE SINGLE      |               |    1 |   52 |       
     |         |  KEY |  KEY |
  |  2 |  SORT AGGREGATE             |               |    1 |   52 |       
     |         |      |      |
  |  3 |   FIRST ROW                 |               |    1 |   52 |    4 
  (0)| 00:00:01 |      |      |

  | 4 | INDEX RANGE SCAN (MIN/MAX)| IDX1 | 1 | 52 | 4 (0)|   00:00:01 | KEY | KEY |

---

   

  On Tue, Sep 21, 2021 at 12:02 PM ahmed.fikri_at_t-online.de
<mailto:ahmed.fikri_at_t-online.de> <ahmed.fikri_at_t-online.de
<mailto:ahmed.fikri_at_t-online.de> > wrote:
    Hi,      

    pragmatically I used to solve these problems (in my opinion oracle     bugs) by creating a scheduler job to detect and remove the SQLs, that     cause such problems, from the shared pool (using     dbms_shared_pool.purge).
    Since one can not change the vendor's code.      

    Best regards
    Ahmed

    -----Original-Nachricht-----
    Betreff: shared pool waits
    Datum: 2021-09-20T21:25:36+0200
    Von: "Pap" <oracle.developer35_at_gmail.com     <mailto:oracle.developer35_at_gmail.com> >     An: "Oracle L" <oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org>     >

    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

Received on Tue Sep 21 2021 - 12:23:59 CEST