AW: shared pool waits

From: <ahmed.fikri_at_t-online.de>
Date: Tue, 21 Sep 2021 09:27:54 +0200 (CEST)
Message-ID: <1632209273993.2196490.b3f0bdb4d8a7d98e59d2a07fe56f9119a6e5132a_at_spica.telekom.de>

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> An: "ahmed.fikri_at_t-online.de" <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 - 09:27:54 CEST