So as you rightly said it's just not the 'DBWR' process related waits but
you are seeing lot of 'log file checkpoint incomplete' and 'block change
tracking waits' all over the ASH report, it may means that you are flooding
the database with unexpected amount of DMLS concurrently which the database
is not able to handle. You may try to see if these are all row by row DMLS
spawning from multiple sessions at same time and which can be converted to
bulk DML to alleviate the chatter at DB side for that interval of time.
On Sat, Mar 4, 2023 at 4:26 PM yudhi s <learnerdatabase99@xxxxxxxxx> wrote:
Tried to capture the details from gv$active_session_history using Tanels
"ash_wait_chains.sql" script during issue period from three different
intervals i.e 5seconds, 10 seconds and 20 seconds and published in below
link. This time the script didn't fail with the Ora-01489.
https://gist.github.com/databasetech0073/3b8c9549ed9825a9888c8da1c74e60ac
Most of those are pointing to DBWR process being the main blocker along
with some 'log file switch checkpoint incomplete' and 'block change
tracking' waita. So does it mean that the database writer is slow to catch
up with the amount of DMLS which the application submits during this
interval?
On Sat, Mar 4, 2023 at 12:19 AM yudhi s <learnerdatabase99@xxxxxxxxx>
wrote:
Thank You Pete. We are not seeing any signs of IO spike or Disk
utilization being saturated in the storage cells during this time. Below
are the database details.
It's Oracle 19c. And Exadata machine.
Disk_asynch_io - True, filesystemio_options-None.
Platform- linux X86 64bit, CPU - 96, Cores- 48, Sockets-2.
And also just to note , as I am unable to fetch the dash_wait_chain from
during the exact peak period as it is failing with error 'result
concatenation is too long' , so not sure if the above
supplied dash_wait_chain output from immediate before the issue period is
reliable to what extent.
On Fri, Mar 3, 2023 at 10:16 PM Peter Hitchman <pjhoraclel@xxxxxxxxx>
wrote:
Hi,
I think some more detail about your set-up will help to get some answers:
Oracle version
Operating system
Number of CPUs
and
The database I/O configuration (disk_asynch_io and filesystemio_options)
and the set-up of the storage being used.
I think that this points to the I/O subsystem not being able to keep up
with the load it is being given.
Regards
Pete
