Even though the filesystemio_options set as ASYNCH but i see in
v$iostat_file the column ASYNCH_IO is set as ''ASYNCH_OFF" for all the data
files. So is it because we are not having data guard configuration and thus
to avoid any block corruption, it's been set as "ASYNCH_OFF" but setting
this ON will increase the DB writer speed?
Saw one blog stating keeping db_writer_processes=1, dbwr_io_slaves=32
helped improve the dbwriter response significantly.
https://www.bobbydurrettdba.com/2013/04/26/db_writer_processes-dbwr_io_slaves-with-no-asynch-io-on-hp-ux/
Regards
Pap
On Wed, Apr 14, 2021 at 9:42 PM Pap <oracle.developer35@xxxxxxxxx> wrote:
Thanks a lot, Tanel.
I got the SAN IO statistics report from the infra team for two of the days
where application was pointing and was experiencing issues in DR but not in
primary. What i see is the IO rate(i.e. read/sec) in the attached
file(primary_DR_SAN_Stats.xlsx) , seems to be on higher side when
application was pointing to DR, but in both the cases i don't see any
increase in avserv i.e. average service time , so should we conclude that
, even we have some additional processes might be running on DR but should
not matter as long as the IO service time is almost zero and also no
queuing is observed from SAN standpoint for DR? But yes this does not match
with the picture which AWR shows i.e. DBWR response time slowness in DR as
compared to primary.
Additionally in the AWR which i had shared here earlier in this thread,
for DR as it shows "index contention" as the top most wait and there does
exist "ITL '' waits(in enqueu_activity_dr.xlsx) , so should we increase the
INITRANS value to help us improve the situation here on DR(and also in
primary) without any negative impact?
Regards
Pap
On Sun, Apr 11, 2021 at 7:36 AM Tanel Poder <tanel@xxxxxxxxxxxxxx> wrote:
The primary and DR run on different storage arrays that may be configured
and "sized" differently (sometimes DR/backup has slower disks or something
like that). Or even if the storage arrays, HBAs & SAN network are identical
across both environments, the DR storage array may be overloaded more than
primary (as everyone's doing their backups from DR).
On HP-UX as a starting point, you could use some IO monitoring tool that
(like sar) that shows you both the I/O rate this server is running & the
average service time (avserv) and avwait/avque (queue lengths). So, if the
DR has twice higher I/O service time than primary, while the I/O rate is
roughly the same (as AWR metrics indicated), then this should be a good
starting point for sysadmins/storage folks.
$ sar –d 1 10
HP-UX server01 B.11.23 U ia64 10/25/09
20:38:46 device %busy avque r+w/s blks/s avwait avserv
20:38:56 c0t0d0 1.30 0.50 3 50 0.00 11.07
c31t2d2 0.80 0.50 1 14 0.00 9.22
c31t2d0 3.30 0.50 5 96 0.00 6.14
c31t2d1 3.80 0.50 5 85 0.00 7.30
c31t2d4 1.20 0.50 2 102 0.00 6.04
c31t2d6 0.10 0.50 0 5 0.00 3.83
c31t2d3 2.80 0.50 6 96 0.00 4.67
c31t2d5 1.10 0.50 1 22 0.00 8.44
c31t2d7 0.40 0.50 1 13 0.00 4.90
c31t3d1 0.50 0.50 1 11 0.00 7.95
--
Tanel Poder
#vConf2021: Troubleshooting Very Complex Oracle Performance Problems
https://tanelpoder.com/conference/
On Sat, Apr 10, 2021 at 3:54 PM Pap <oracle.developer35@xxxxxxxxx> wrote:
As it almost seems like the DR side is performing badly because of some
additional process/database might be there which is still running and
adding overhead. So what kind of information/stats from the host or
SAN should we ask which will help us in identifying the contribution of
each of the processes in primary and DR and this would be able to find the
exact culprit which is creating a difference during that peak period?
Regards
Pap
On Sat, Apr 10, 2021 at 12:48 AM Pap <oracle.developer35@xxxxxxxxx>
wrote:
Got to know there are other 3 databases on this box, however at exactly
same time we also see a small spike in top activity graph in another
database. And i see even when we were live on Primary that other database
on DR having that small spike exists , which suggests some problem
persisting in the box level rather in the impacted database which we are
looking for. So it may be either at host or storage(which the SAN team is
denying though).
And another thing is even we saw in the AWR we are doing large amount
of work or the db block changes were on higher side on primary as compared
to DR , but the SAN team saying higher IOPS in DR as compared to primary
during that time observed without any queuing. So it sounds strange. So,
Just like database AWR report, Is there any such historical report which we
can generate out of the HP host or underlying SAN to understand what exact
process/database contributes how much during that peak time? And what is
the exact IO latency trend and segregation based on database , during that
time?
Regards
Pap
On Fri, Apr 9, 2021 at 7:54 PM Pap <oracle.developer35@xxxxxxxxx>
wrote:
But isn't it correct to say that system stats or dictionary stats can
only influence the optimizer to go for a different execution path? But in
our case both of the databases are giving an equal execution path, we
don't
see any difference in execution path for any such sql which can cause this
issue. It's mainly the single row inserts/updates are running longer on DR
as compared to primary during peak period.
Regards
Pap
On Fri, Apr 9, 2021 at 7:50 PM Athil Meer <athildba@xxxxxxxxx> wrote:
Hello Pap,
Are there any difference in fixed, dictionary and system stats
between these DBs
Thank you
Athil
On Fri, 9 Apr 2021, 5:09 pm Pap, <oracle.developer35@xxxxxxxxx>
wrote:
We have both application server and db server based out of primary
and DR side each. So when we point to DR our application code/server
also
runs from DR only. So there should not be any network delay causing such
difference.
Regards
Pap
On Fri, Apr 9, 2021 at 6:49 PM Mark W. Farnham <mwf@xxxxxxxx> wrote:
In a network sense, where is the client application with respect to
the two databases?
When you are using the “primary” database is the client at the
“primary” database location?
When you are using the “DR” database is the client at the “DR”
database location?
Depending on how the application handshakes information to be
written and how waits are rolled up, that can make all the difference.
A very quick test is to actually place a client at the location of
the “DR” database location on the LAN with the “DR” database if in fact
currently you are considering performance of an application on the
“primary” database location in both cases currently.
Explaining how and why the performance difference can actually be
network latency and speed yet appear to be differences in DBWR service
rate
is beyond the scope of this note.
mwf
*From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Pap
*Sent:* Friday, April 09, 2021 8:29 AM
*To:* Tanel Poder
*Cc:* Oracle L
*Subject:* Re: Difference in writer response time
Thank You Tanel.
Not having much understanding with respect to the infra side. But ,
actually the first thing we want to understand is why there is a
difference
in performance during that peak period between the two sites. As those
are
primary and DR so ideally everything should be the same including SAN
configurations. We have verified all the DB parameters are mostly the
same
and are not suspected such as to impact DBWR throughput one vs other.
But as you said there may be some issue wrt kernel or IO stack, so
wanted to understand what exact information we can ask from our
storage/SAN
team by which we can conclude is there is really a difference in
performance of storage IO between both sides?
And should we ask the IO information only during the issue period
to understand the difference as because in normal time both the
databases
perform similar just that during those peak activity the DR is behaving
badly as compared to primary?
Regards
Pap
On Fri, Apr 9, 2021 at 5:32 AM Tanel Poder <tanel@xxxxxxxxxxxxxx>
wrote:
Ok, sorry I didn't realize this was on HP-UX (I guess I'm too
spoiled nowadays with "servers" like this one
<https://tanelpoder.com/posts/11m-iops-with-10-ssds-on-amd-threadripper-pro-workstation/>
under my desk ;-)
Some comments:
1. The *free buffer waits* show that DBWR falls behind with
syncing dirty buffers to disk (~4000 blocks written per second). So
it looks like a DBWR throughput problem (caused by some issue in
the kernel
or I/O stack)
2. Other than storage hardware latency itself and things like
block device queue depths, the filesystem itself may be a
bottleneck - are
you using *VxFS *- and if yes, with or without *ODM*? VxFS
without ODM doesn't use concurrent I/O by default, potentially
causing
write-write contention at file level (POSIX single writer locking)
3. Also, the normal recommendation for production systems is to
use filesystemio_options=*setall*, but I don't remember if
HP-UX was special in that sense (or you just forced direct I/O with
a VxFS
mount option)
4. Run Snapper on DBWRs and see how many of them you have in
each instance and are they 100% busy waiting for one of the *db
file parallel write*, *db file asynch I/O submit* waits (or
show all CPU usage - no wait events whatsoever, can be due to an
instrumentation bug)
The various "higher level contention" wait events, *buffer busy
waits*, *enq: TX contention *and in rare cases even some latch
contention may be just a symptom of the underlying I/O problems. You
need
to do wait chain analysis to see beyond the "top level symptoms".
The ash_wait_chains.sql output is pretty wide, Oracle-L doesn't
allow me to send screenshots as images, so here are a couple of tweets
with
images of such problems (where the high level symptoms show different
waits
than the root cause):
- https://twitter.com/TanelPoder/status/1318023393304825856
- https://twitter.com/TanelPoder/status/1380304923678498820
Back when I used to deal with HP-UX/Solaris machines with VxFS
more, a really common bottleneck in high-performance databases (of that
era) was people not using concurrent I/O on that filesystem.
--
Tanel Poder
#vConf2021: Troubleshooting Very Complex Oracle Performance Problems
https://tanelpoder.com/conference/
On Thu, Apr 8, 2021 at 2:05 PM Pap <oracle.developer35@xxxxxxxxx>
wrote:
Thank you Tanel.
Its HP machine and its on file system. No ASM. The
filesystemsio_options in gv$paramer showing as "asynch" in both
databases.
and also disk_asynch_io is also set as TRUE. As I see from AWR, Its
HP-UX
IA(64 bit), 64 core, 8- socket machine in both side.
The SAN team confirms not much visible difference between the
underlying storage performance between those two sites. So wondering
as we
don't use data guards could this difference be just because of just the
structural difference in table/index structure? But we have all the
same
volume of data on both sides so not sure how that can play such a big
difference. And contrary to that, I see we are doing more work on the
Primary(faster) side during that time period as compared to DR(slow
side),
so how is that possible? I am seeing the direct write is higher on DR
i.e.
slow side as compared to primary, not sure if that is pointing to
something
suspicious.
I have fetched the wait event section of the AWR from both
databases and attaching here(in two tabs as primary and DR wait
profile).
Although I do see "free buffer waits" in one of the top lists but the
major
one is 'index contention'(top one is a composite PK index but local
index,
so less chances of structural difference causing such issues). But
again it
might be that the DBWR response trend is just a symptom of something
else
but not the cause as the SAN team is saying no difference there.
Regards
Pap
On Thu, Apr 8, 2021 at 10:34 PM Tanel Poder <tanel@xxxxxxxxxxxxxx>
wrote:
There's also one usual suspect, I don't see *filesystemio_options*
parameter set, are you running on ASM, not a filesystem, right?
Thanks,
Tanel.
On Thu, Apr 8, 2021 at 4:06 AM 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.
