Re: High Buffer busy waits

You need to make your checkpoints faster, so that they don't get into the way of log file switches. You have a DBWR write I/O problem, not a LGWR or buffer busy wait problem (DBWR is the ultimate blocker in the wait chain). Increasing/adding redologs may help to reduce these log switch waits/symptoms, but ultimately DBWR is not keeping up with syncing enough dirty buffers to disk (so that older redologs could be overwritten).

Have you set your fast_start_mttr_target in this system? This is one way to make DBWR more active syncing dirty buffers to disk (so that DBWR would be constantly writing at a slower pace, instead of being mostly idle and causing write I/O spikes during a log switch).

--
Tanel Poder
https://tanelpoder.com

On Wed, Jul 26, 2023 at 5:18 PM 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