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

Thank You Tanel.

_serial_direct_read is set as default 'AUTO'. The blocks for table CB in dba_tables is showing as - 124. So yes, its really a tiny reference data table.

Also i checked dba_hist_active_sess_history there were no "cell multiblock physical read" noted against that sql in past too. All i can see is "cell smart table scan" ,"enq: KO - fast object checkpoint","reliable message". But during the issue period it just flooded with more "enq: KO - fast object checkpoint" and "reliable message" wait events. As you said it may be that the bug still persists.

On Tue, 29 Nov, 2022, 10:57 am Tanel Poder, <tanel_at_tanelpoder.com> wrote:

> Oh I just noticed that the CB table is just 41 rows. How big is it in
> segment size (blocks) and does DBA_TABLES.BLOCKS also show a tiny number?
>
> Do you have *_serial_direct_read* set to *always* by any chance? (that
> would explain why direct path read/smart scans are attempted even on tiny
> tables).
>
> Could still be some bug too, sometimes bugs that get fixed in a version,
> aren't completely fixed to cater for all scenarios and complexity levels...
>
> But if you created a nice index to avoid the FTS completely and got index
> range scan/index unique scan instead, the problem would likely be gone.
> Even without bugs, doing smart scan on a 41 row table is gonna have much
> higher overhead than just doing a single buffer get into a single-block
> index in the buffer cache.
>
> --
> Tanel Poder
> https://learn.tanelpoder.com
>
>
> On Mon, Nov 28, 2022 at 9:55 PM 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