(Reposting my earlier post as it didn't go through Oracle-L first. Note
that I assumed you were running on Linux when I wrote that post, but may be
still useful in general):
Does this measurably affect application response times? DBWR is a mostly
independent background process that works asynchronously in the background.
So if your application sessions don't see any direct waits like "free
buffer waits", then it matters less what exactly DBWR is doing.
But if you're just wondering why the difference in otherwise identical
environments and similar workload - there may be a few factors affecting
DBWR wait durations.
But the first thing to say, as DBWR is an asynchronous I/O engine with
dynamic write batch sizes, etc, you probably shouldn't focus on whatever
exactly this "avg DBWR wait event" even is measuring (it possibly measures
just the async I/O completion/reaping waits and not any potential I/O
submission waits (and the actual time it took from getting from I/O
submission to completion). Asynch I/O complicates things, one I/O operation
does mean one wait event anymore.
You can read about the "db file parallel write" vs "db file async I/O
submit" waits here (and Frits Hoogland has written detailed articles about
this too):
*Oracle:*
-
https://tanelpoder.com/posts/log-file-switch-checkpoint-incomplete-and-lgwr-waiting-for-checkpoint-progress/
*OS/Linux level:*
- https://tanelpoder.com/posts/high-system-load-low-cpu-utilization-on-
linux/
<https://tanelpoder.com/posts/high-system-load-low-cpu-utilization-on-linux/>
If you still want to drill deeper, run Snapper on DBWRs in both scenarios
(to get more metrics about DBWR actual throughput and how active/inactive
these processes are). For example, for a 60 second snapshot of all DBWn
processes' activity:
- @snapper all 60 1 dbwr
And at OS level (on Linux), you can run my Linux Process Snapper
<https://tanelpoder.com/psnapper/> script (described in the above article)
that can tell you if DBWR write syscalls are hitting any OS/storage
subsystem level bottlenecks (like filesystem journaling blocking or inode
contention, if you're running on a filesystem).
--
Tanel Poder
#vConf2021: Troubleshooting Very Complex Oracle Performance Problems
https://tanelpoder.com/conference/
On Thu, Apr 8, 2021 at 6:18 AM Pap <oracle.developer35@xxxxxxxxx> wrote:
I also see a difference in the Avg DBWR response time during normal
periods i.e. 45ms vs 65 ms for primary and DR respectively. Does this mean
that it's not any parameter difference rather there must be some difference
in underlying storage/san?
On Thu, Apr 8, 2021 at 1:35 PM Pap <oracle.developer35@xxxxxxxxx> wrote:
Hello Lister, We have two databases which are the same in all the
hardware configurations along with DB parameters and are in sync/replicated
using golden gate. And those are treated as primary and DR for us and both
are active. But we are seeing differences in behaviour when our application
points to primary VS when it points to DR. We are seeing during specific
times of the day when our system is at its peak, in one of the database i.e
DR the DBWR response times spikes till ~200+ms slowing down the write
queries while in another database the dbwr response time stays ~65ms for a
similar amount of data and transaction volume. So wanted to understand what
can cause such drastic differences? Note- Its version 11.2.0.4 of Oracle.
I tried capturing a few key sections of AWR as during the peak time and
put it in the attachment in two different tabs. I am seeing the one having
a high DBWR response is doing lesser work as compared to the other one. So
wondering if it could be the difference in SAN? but As confirmed by the
infra team its solid state disk for both of the databases with no
difference in infra level. So wanted to understand , why do we see such
differences in both databases then?
Thanks and regards
Pap
