Re: High Buffer busy waits

Heh! I guessed it's RAC just by looking at the wait chains. There have been some bugs in 19c about dbwr not picking up writes, for example Bug 33973908.

On Thu, 27 Jul 2023, 00:48 yudhi s, <learnerdatabase99_at_gmail.com> wrote:

> Missed to mention, it's version 19C of oracle and on a exadata box.
>
> On Thu, 27 Jul, 2023, 2:46 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
>>
>

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