Re: High Buffer busy waits

4G redo logs are certainly better than the default 100MB but instead of guessing the right size for redo logs, you can calculate required reasonable adjustment based on Statspack/AWR history: https://blog.pythian.com/do-you-have-enough-redo/

On Thu, Jul 27, 2023 at 12:18 AM 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
>

-- 
Regards
Timur Akhmadeev

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