Re: High response time with top 'Other' and 'application' wait class

Thank you so much. Yes, we saw at first, and also I forgot to mention these underlying tables of the SELECT query which is coming on TOP are also defined as KEEP pools whereas this database does not have a keep_cache_size set, so to say and agree, that it seems exactly matching with the bug description. However, it's mentioned and also confirmed from Oracle that the same issue is already fixed in prior versions.

On Tue, Nov 29, 2022 at 8:31 AM yudhi s <learnerdatabase99_at_gmail.com> wrote:

> There is a bug i see in MOS which is almost matching with your symptom
>
> Slow Performance Due to Waits for: "Enq: KO - Fast Object Checkpoint" and
> "Reliable Message" (Doc ID 1377830.1)
>
>
>
> On Tue, Nov 29, 2022 at 8:25 AM Lok P <loknath.73_at_gmail.com> wrote:
>
>> Hello ,
>> It's version 19C of the oracle database. We got complaints from the
>> application team of sudden slowness and thus app traffic automatically
>> redirected to another active DB replica.While looking into the DB side, we
>> do observe high wait events from wait class 'Other' and 'Application' in
>> OEM. It lasted for exactly 2-3 minutes as the application team is
>> complaining about. And then fetching the "ash wait chain" and "ash top" ,
>> it's pointing to significantly high wait event like "reliable message"
>> (~75%), "enq: KO - fast object checkpoint","gcs drm freeze in enter server
>> mode" from same wait class 'other' and 'application'.
>>
>> Below is the ASH top and ASH wait chain from the issue duration of
>> ~5minutes interval. And the select query which is coming on top. And table
>> CB is holding just ~41 rows in it.
>> https://gist.github.com/oraclelearner/44394ab8206fc7bd51041eb3d45bdf9f
>>
>> And also the top query which is showing in "ash top" is a SELECT query
>> and it does have a FTS in it (which is suggesting why the checkpoint
>> waits), however, this select query doesn't have any plan change observed in
>> it or not any high execution. And also we are not seeing any specific
>> blocking session for this. The "wait chain" showing LMON/lock monitor
>> process in one of the lines also does point to some event like "Wait for
>> msg sends to complete ''. Not yet able to figure out how these are related
>> to this issue. Still trying to understand what could have caused such a
>> scenario?
>>
>> This SELECT query executes 100's of thousands of times in ~15minutes
>> window. So is it possible that this query(or say its underlying table CB)
>> might have thrashed out from buffer cache to disk and fetched data from
>> disk rather than cache for that time and this causing all these sort of
>> issues? And considering this table is very small (~1MB in size and holding
>> just ~41 rows), should we consider putting it as inmemory by changing
>> "inmemory_clause_default" to 'BASE_LEVEL' and "INMEMORY_SIZE" to 16GB ?
>>
>>

--
http://www.freelists.org/webpage/oracle-l