Re: High Buffer busy waits

Yudhi,

This shows very clearly that they're all waiting on "log file switch".

Please make the online redo log files much larger than they are currently, in order to slow the frequency of log switches.  Make them at least 4 GB apiece, and don't be shy to make them much larger.  Friends don't let friends drive with small redo log files.

Single-row INSERT statements always have PLAN_HASH_VALUE of zero, because there is no execution plan for a single-row INSERT.

Hope this helps,

-Tim

On 7/26/2023 2:46 PM, yudhi s 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