Re: High Buffer busy waits
Date: Wed, 2 Aug 2023 02:26:20 +0530
Message-ID: <CAEzWdqe6hbpk=O+28ozowMvg6J-camMj1iL=zidR6qUasmtdhw_at_mail.gmail.com>
Thank you so much.
Actually for this database, there exists an active-active HA setup
(bi-directional sync is happening using golden gate replication). And we
saw when the application with the same workload is running in that
other/secondary database but we don't see such high buffer busy waits
during the peak period.
We have fast_start_mttr_target value set as zero in both the databases.
And further checking the latest one entry from dba_registry_sqlpatch , in
the database in which we are getting buffer busy waits is "MERGE ON
But is there any way possible through which we can be more certain , it's really the same bug which is impacting us?
Actually we are now live on the secondary database on which the issue is not occurring but mostly it will come again when we will move back to the primary database. However, we have AWR data and that event trend for dba_hist_system_event is saying we had a higher spike in " log file switch (checkpoint incomplete) " exactly during the same issue period. So wondering, if there exists any other waits which trend could be verified during the time to prove the point of the mentioned bug? And the bug suggests "*the DB writer not picking up write for some time*", so does it mean that the wait chain which we are seeing is abnormal, as its showing us the ultimate blocker as "*idle *blocker 1,2395,34375 (oracle_at_XXXXXXXXXXX (DBW0)" , so the *idle *keyword itself pointing to the bug/idle DBW0 here?
On Tue, Aug 1, 2023 at 5:19 AM Tanel Poder <tanel_at_tanelpoder.com> wrote:
> Reading through other responses - it could of course be a bug too (when
> you hit a bug, all bets are off).
>
>
> On Mon, Jul 31, 2023 at 7:45 PM Tanel Poder <tanel_at_tanelpoder.com> wrote:
>
>> 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).
>>
>>>
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Aug 01 2023 - 22:56:20 CEST