Re: Question on concurrency wait time

Hello Pap,
it is not about the BTC file size or DB buffer cache, it is about the BCT buffer in SGA.

How this all can be related? Let's take line 2 from your DASH wait chain script output for example.

Session (USER1:(PRG2) block change tracking buffer space) is waiting on a free BTC buffer and holds a block (in buffer cache) in an incompatible mode while waiting on a free BTC buffer. Now the other 5.4 sessions (USER1:(PRG2) buffer busy waits [data block]) in avg also try to modify this block (this can be an index block) but have to wait until the blocking session (which waits on free BTC buffer) can go on and change the block mode to a compatible one.

So overall the root cause of the issue is "block change tracking buffer space" and "buffer busy waits" is just a consequence of it.

Now you need to figure out:
1) Do you have an I/O latency issue with the BTC file? 2) Is your BTC buffer big enough to handle the database load (and also in regard to I/O latency of BTC file)?

... and then fix the I/O latency problem of the BTC file or/and increase the BCT buffer.

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 24.10.2022 11:59 CEST geschrieben:
>
>
> Thank You So much Stefan.
>
> I am not sure why all the responses I sent back earlier bounced back from the list. I had replied earlier, suspecting maybe the smaller size of the buffer cache is causing this, because the rate of inserts happen throughout the day is almost the same , but we see the concurrency during the time when there is heavy activity happening on the database from another app. But now as you rightly pointed out and also its showing in the ASH stats the BCT Buffer space wait seems to be the starting point. And then we may have to increase the size of the BCT buffer. Currently we have database size ~27TB and the BCT file size in v$block_change_tracking showing as ~2GB.
>
> But again how can this be related? Its understood that during higher activity(say high DML's) from another application which may not be related to this object/index, but then that can cause this INSERT query(along with other DMLS) response to go slow itself and the top wait event it should log against that insert query as "BCT buffer space". However we are seeing the top wait event against this INSERT query as concurrency/buffer busy waits and the current_obj# pointing to the index partition? As my understanding was concurrency/buffer busy wait on index block can only happen during the race scenario, i.e. if the inserts are too heavy/concurrent and are all going to change one specific block. Please correct me if my understanding is wrong.
>
> And yes, we were initially suspecting if it's the size of the buffer cache which matters here and has to be bigger to cater the load. Then, we tried to see the sga advisory , if it's pointing to the same fact. This database has a db_cache_size of 6GB with sga_max_size ~35GB and sga_target set as 0. But not sure why , I don't see any records populated in dba_hist_sga_advice. But from the rows in dba_hist_db_cache_advice it's showing that doubling the db cache i.e. increasing it by a factor of -2 will just help in reducing the physical reads by ~15 to ~17%. Not sure if these figures are reliable or not.

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