Re: Question on concurrency wait time

Hello Pap,
based on the wait chain output the root cause of your "buffer busy waits" problem is not the index(es) or application behavior but "block change tracking buffer space".

This is related to the block change tracking feature (e.g. for RMAN incremental backups) - it seems like your BCT buffer is way to small to keep up with the change rate (and/or I/O to your block change tracking file is very slow) at this time. I troubleshot several issues with block change tracking - even in the most recent versions, so not a very uncommon case.

You can try to increase the BTC buffer (e.g. MOS ID #2094946.1) but please double/triple check if the increase works as there are several known bugs (e.g. like #32428097 and many more - even it says fixed in 19.x).

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher Website: http://www.soocs.de
Twitter: _at_OracleSK

> Pap <oracle.developer35_at_gmail.com> hat am 23.10.2022 14:32 CEST geschrieben:
>
> Hello Listers,
> We have a customer database on version 19.15. We are experiencing high concurrency waits(Buffer busy waits) for one of the INSERT query and the object its pointing to in ASH is the primary key composite index which is on three columns(Unix_time_id,status,part_date_time) followed by other one which is on one column i.e create_date column. Both of these two indexes are local indexes. And the table is a weekly range partition on the date column (part_date_time which is populated by sysdate value from application).
>
> Below is the output from the Tanel's DASH_wait_chain query from the issue period. This spike in concurrency happens for 2-3minutes(even less time duration in many occasions) impacting one of the critical latency sensitive jobs. Our understanding was , as the first column of the primary key index is generated from application code as a unix timestamp(defined as VARCHAR2(40) data type) and is mostly unique, so the contention should be minimal. For a specific time period, the values of the first column - Unix_time_id looks like below i.e even different but the first 7 to 8 characters are the same. So can it be the cause of concurrency here and if yes, how can we avoid it?

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