Re: High Buffer busy waits
Date: Wed, 26 Jul 2023 21:08:32 -0400
Message-ID: <eaef6950-fde6-7e22-3118-641afbe5f442_at_gmail.com>
On 7/26/23 17:16, yudhi s wrote:
> 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.
In addition to what Tim said, buffer busy wait is a concurrency error. I don't know what your application looks like, but the right place to start investigating is My Oracle Support:
Resolving Issues Where Waits for 'buffer busy waits' are Seen When Concurrency for Buffers is High (Doc ID 1476043.1)
There are many recipes for reducing concurrent access to a set of blocks:
- Increase PCTFREE in the afflicted tables
- Re-engineer the application to make sure that different threads are hitting different blocks.
- Reverse indexes will scatter the block accesses to the index at the cost of losing range scan of the reversed indexes.
- Scaleable sequences: https://oracle-base.com/articles/18c/scalable-sequences-18c#creating-scalable-sequences (big thanks to Tim Hall)
However, as Tim (Tim Gorman, not Tim Hall) has said, log switches can and will kill the performance. Resizing your redo logs is the low hanging fruit. Re-architecting the application is not.
-- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com -- http://www.freelists.org/webpage/oracle-lReceived on Thu Jul 27 2023 - 03:08:32 CEST