Re: High Buffer busy waits

Hi,

Just being curious:
does anyone know if current_obj# is pointing to the correct object? I did some research on "latch cache buffer chains" and found out that current_obj# is unreliable for that wait event. Which makes sense, because the object of the wait is a latch.

Thanks

Lothar

Am 27.07.2023 um 10:06 schrieb Jonathan Lewis:
>
> As Tim has said, your primary problem is the "checkpoint incomplete"
> waits. To avoid waits on the "log file switch (checkpoint incomplete)"
> you generally need more redo log. Tim has suggested making the
> existing log files larger, but if you don't like the idea of very
> large log files you could just add more files of the same size; so
> long as you don't have a silly (tiny) size it's the total volume of
> the redo log that matters nost not the specific size chosen for the
> files.
>
> Since you refer to this as a "sudden" occurrence It is possible that
> the log writer was temporarily delayed by an unusual slowdown in the
> database writer - so its worth checking if there was some load on your
> I/O subsystem (possibly from outside Oracle) that slowed down Oracle's
> I/O rates.
>
> You'll notice that the buffer busy waits at the top of the list is
> labelled "(1st level bmb)" - that about space management blocks, not
> about competition on the data blocks, so changing the initrans setting
> wouldn't make a difference. The much smaller percentage of buffer busy
> waits labelled as [data block] would be the ones that might improve
> with an increase in initrans, but I wouldn't mess about with the
> setting until after I'd checked the effect of eliminating the
> checkpoint waits.
>
> An idea that I've not experimented with for systems like yours that
> have a large number of concurrent processes doing single row inserts
> is whether the size of the extents makes any difference. The way in
> which Oracle associates processes with data blocks does change with
> the size of the extent, and I have wondered whether the algorithm goes
> a little further and allocates more level 1 bitmap blocks as the
> number of inserting processes grows.  You could examine the block
> addresses of the bmbs that show up in bbws to see how many different
> blocks are involved in a short period of time, this might give us some
> clues about how the feature works in extreme cases.
>
> Regards
> Jonathan Lewis
>
>
>
>
>
> On Wed, 26 Jul 2023 at 22:17, 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