shared pool waits
From: Pap <oracle.developer35_at_gmail.com>
Date: Tue, 21 Sep 2021 00:55:15 +0530
Message-ID: <CAEjw_fj4vGY=W+URk7BhdvsVfe3m-D_BGdeAJg_cjBKq3rASbw_at_mail.gmail.com>
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.
Date: Tue, 21 Sep 2021 00:55:15 +0530
Message-ID: <CAEjw_fj4vGY=W+URk7BhdvsVfe3m-D_BGdeAJg_cjBKq3rASbw_at_mail.gmail.com>
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-lReceived on Mon Sep 20 2021 - 21:25:15 CEST