Re: High Buffer busy waits

As Tim has said, your primary problem is the "checkpoint incomplete" waits. To avoid waits on the "log file switch (checkpoint incomplete)" you generally need more redo log. Tim has suggested making the existing log files larger, but if you don't like the idea of very large log files you could just add more files of the same size; so long as you don't have a silly (tiny) size it's the total volume of the redo log that matters nost not the specific size chosen for the files.

Since you refer to this as a "sudden" occurrence It is possible that the log writer was temporarily delayed by an unusual slowdown in the database writer - so its worth checking if there was some load on your I/O subsystem (possibly from outside Oracle) that slowed down Oracle's I/O rates.

You'll notice that the buffer busy waits at the top of the list is labelled "(1st level bmb)" - that about space management blocks, not about competition on the data blocks, so changing the initrans setting wouldn't make a difference. The much smaller percentage of buffer busy waits labelled as [data block] would be the ones that might improve with an increase in initrans, but I wouldn't mess about with the setting until after I'd checked the effect of eliminating the checkpoint waits.

An idea that I've not experimented with for systems like yours that have a large number of concurrent processes doing single row inserts is whether the size of the extents makes any difference. The way in which Oracle associates processes with data blocks does change with the size of the extent, and I have wondered whether the algorithm goes a little further and allocates more level 1 bitmap blocks as the number of inserting processes grows. You could examine the block addresses of the bmbs that show up in bbws to see how many different blocks are involved in a short period of time, this might give us some clues about how the feature works in extreme cases.

Regards
Jonathan Lewis

On Wed, 26 Jul 2023 at 22:17, yudhi s <learnerdatabase99_at_gmail.com> wrote:

> Hello Listers,
>
> We suddenly see higher response time for our incoming transactions
> during certain time periods. and then tried running tanels DASH_WAIT_CHAIN
> getting below wait chains. It's pointing to high 'Buffer busy waits' and
> the top sqls it's pointing to is a single row INSERT query.
>
> And as ist a single row insert , so the plan_hash_value is showing as '0'
> and also the Avg number of execution of the Insert query remains same. So
> wondering what can be the cause of the issue? Can it be associated with
> something related to infrastructure as the final blocker showing as "DB
> writer"?
> or
> As the current_obj# in ASH is pointing to table only and it has default
> settings i.e. ini_trans - 1 and max_trans- 255 and we are using ASSM.
> Should we consider increasing the value of ini_trans here?
>
>
>
> https://github.com/tanelpoder/tpt-oracle/blob/master/ash/dash_wait_chains.sql
>
>
> %This SECONDS AAS DISTINCT_SIDS WAIT_CHAIN FIRST_SEEN LAST_SEEN
> 38% 5710 15.9 19 -> USER1:(PROGMGR) buffer busy waits [1st level bmb] ->
> USER1:(PROGMGR) log file switch (checkpoint incomplete) -> [idle blocker
> 1,2395,34375 (oracle_at_XXXXXXXXXXX (DBW0))] 7/25/2023 5:46 7/25/2023 5:46
> 20% 3050 8.5 166 -> USER1:(PROGMGR) log file switch (checkpoint
> incomplete) -> [idle blocker 1,2395,34375 (oracle_at_XXXXXXXXXXX (DBW0))] 7/25/2023
> 5:46 7/25/2023 5:46
> 7% 990 2.8 47 -> USER1:(PROGCAP) ON CPU 7/25/2023 5:46 7/25/2023 5:51
> 5% 770 2.1 40 -> USER1:(PROGCAP) log file switch (checkpoint incomplete)
> -> [idle blocker 1,2395,34375 (oracle_at_XXXXXXXXXXX (DBW0))] 7/25/2023 5:46 7/25/2023
> 5:46
> 5% 700 1.9 16 -> USER1:(PROGMGR) buffer busy waits [data block] ->
> USER1:(PROGMGR) log file switch (checkpoint incomplete) -> [idle blocker
> 1,2395,34375 (oracle_at_XXXXXXXXXXX (DBW0))] 7/25/2023 5:46 7/25/2023 5:46
> 3% 390 1.1 5 -> USER1:(PROGCAP1) buffer busy waits [data block] ->
> USER1:(PROGCAP1) log file switch (checkpoint incomplete) -> [idle blocker
> 1,2395,34375 (oracle_at_XXXXXXXXXXX (DBW0))] 7/25/2023 5:46 7/25/2023 5:46
> 2% 330 0.9 5 -> RPT_USER:(JDBC Thin Client) ON CPU 7/25/2023 5:46 7/25/2023
> 5:51
> 2% 290 0.8 6 -> ADMIN:(JDBC Thin Client) ON CPU 7/25/2023 5:46 7/25/2023
> 5:51
> 2% 270 0.8 22 -> USER1:(PROGMGR) ON CPU 7/25/2023 5:46 7/25/2023 5:51
>

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