High Buffer busy waits

From: yudhi s <learnerdatabase99_at_gmail.com>
Date: Thu, 27 Jul 2023 02:46:57 +0530
Message-ID: <CAEzWdqdCVnMowyy0OKROBospNMT7AJ7qUpvZxJiQdc4w+y_T8Q_at_mail.gmail.com>



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
Received on Wed Jul 26 2023 - 23:16:57 CEST

Original text of this message