Home » RDBMS Server » Performance Tuning » Help required in analyzing Network waits from AWR (Oracle 10.2.0.3 on AIX)
Help required in analyzing Network waits from AWR [message #585763] Wed, 29 May 2013 10:19 Go to next message
sysdba007
Messages: 28
Registered: November 2012
Junior Member
Hello

We are experiencing Network waits on one of our 2-node clustered databases

In every 1 hour of clock time we are finding 700-900 seconds of Network waits

From the AWR data I find that "ARCH wait on SENDREQ" is one of the main constituent for these Network waits and as such I suspect Network between Production database and its corresponding database might be slow

Question 1) Does this understanding look correct?

Question 2) Apart from the above what could be the other causes of the Network waits. Can we point out any particular area from following AWR extract
Seeing some gc* waits initially I thought it might be due to slow interconnect between the cluster nodes but some google search denotes it is not the case
So what could be other causes? I mean which network link I would check?

Thanks and Regards
sysdba007


              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:     22631 22-May-13 10:00:11       976       7.9
  End Snap:     22632 22-May-13 11:00:28       978       8.1
   Elapsed:               60.29 (mins)
   DB Time:              795.66 (mins)

Cache Sizes
~~~~~~~~~~~                       Begin        End
                             ---------- ----------
               Buffer Cache:    41,856M    41,856M  Std Block Size:         8K
           Shared Pool Size:     4,768M     4,768M      Log Buffer:    14,268K

Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:            881,316.98              8,680.32
              Logical reads:            196,666.10              1,937.01
              Block changes:              4,272.03                 42.08
             Physical reads:              2,284.29                 22.50
            Physical writes:                265.63                  2.62
                 User calls:              8,806.28                 86.74
                     Parses:              3,668.13                 36.13
                Hard parses:                  0.79                  0.01
                      Sorts:                408.40                  4.02
                     Logons:                  0.10                  0.00
                   Executes:              5,121.07                 50.44
               Transactions:                101.53


                                                                  Avg
                                       %Time       Total Wait    wait     Waits
Wait Class                      Waits  -outs         Time (s)    (ms)      /txn
-------------------- ---------------- ------ ---------------- ------- ---------
User I/O                    4,300,944     .0           30,916       7      11.7
Commit                        375,147     .0            4,097      11       1.0
System I/O                    447,588     .0            3,382       8       1.2
Cluster                       914,194     .0              923       1       2.5
Network                    35,607,365     .0              781       0      97.0
Application                    47,874    2.6              775      16       0.1
Other                         634,810   93.9               62       0       1.7
Configuration                   1,068     .1               44      41       0.0
Concurrency                    19,969     .3               22       1       0.1
          -------------------------------------------------------------

		  
                                             %Time  Total Wait    wait     Waits
Event                                 Waits  -outs    Time (s)    (ms)      /txn
---------------------------- -------------- ------ ----------- ------- ---------
db file sequential read           3,856,417     .0      29,900       8      10.5
log file sync                       375,147     .0       4,097      11       1.0
log file parallel write             293,891     .0       1,842       6       0.8
db file parallel write              114,875     .0       1,108      10       0.3
db file scattered read              394,943     .0         886       2       1.1
enq: TM - contention                  2,839   42.5         665     234       0.0
gc cr grant 2-way                   535,560     .0         443       1       1.5
ARCH wait on SENDREQ                  3,342     .0         435     130       0.0
Log archive I/O                       7,656     .0         312      41       0.0
SQL*Net more data from clien      1,820,698     .0         258       0       5.0
gc buffer busy                       31,190     .1         218       7       0.1
gc current grant 2-way              185,697     .0         140       1       0.5
read by other session                15,751     .0         123       8       0.0
enq: TX - row lock contentio            979    1.8          98     101       0.0

Service Name                      DB Time (s)   DB CPU (s)      Reads      Reads
-------------------------------- ------------ ------------ ---------- ----------
SYS$USERS                            47,717.6     12,431.7  8,239,490 ##########
SYS$BACKGROUND                            0.0          0.0     24,654    135,966
oradb                                     0.0          0.0          0          0
oradb.world                               0.0          0.0          0          0
          -------------------------------------------------------------

Service Wait Class Stats             DB/Inst: oradb/oradb1  Snaps: 19631-19632
-> Wait Class info for services in the Service Statistics section.
-> Total Waits and Time Waited displayed for the following wait
   classes:  User I/O, Concurrency, Administrative, Network
-> Time Waited (Wt Time) in centisecond (100th of a second)

Service Name
----------------------------------------------------------------
 User I/O  User I/O  Concurcy  Concurcy     Admin     Admin   Network   Network
Total Wts   Wt Time Total Wts   Wt Time Total Wts   Wt Time Total Wts   Wt Time
--------- --------- --------- --------- --------- --------- --------- ---------
SYS$USERS
  4265448   3090213     17415      1884         0         0  35603361     34662
SYS$BACKGROUND
    35547      1400      2554       321         0         0      3342     43479
oradb.world
        0         0         0         0         0         0         0         0
          -------------------------------------------------------------
		  
		  
Statistic Name                                       Time (s) % of DB Time
------------------------------------------ ------------------ ------------
sql execute elapsed time                             41,939.8         87.9
DB CPU                                               12,436.2         26.1
parse time elapsed                                      504.7          1.1
PL/SQL execution elapsed time                           133.2           .3
hard parse elapsed time                                  62.8           .1
connection management call elapsed time                   2.6           .0
sequence load elapsed time                                2.1           .0
repeated bind elapsed time                                1.9           .0
PL/SQL compilation elapsed time                           1.0           .0
hard parse (sharing criteria) elapsed time                0.5           .0
hard parse (bind mismatch) elapsed time                   0.5           .0
DB time                                              47,739.4          N/A
background elapsed time                               4,258.5          N/A
background cpu time                                     272.1          N/A
		  

Re: Help required in analyzing Network waits from AWR [message #585765 is a reply to message #585763] Wed, 29 May 2013 10:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
IMO, you suffer from Compulsive Tuning Disorder
SQL> select 781/47739 from dual;

 781/47739
----------
 .01635979



If you 100% eliminated the Network wait, at most things would be 1.6% faster.
I suggest you find other issues upon which you can spend your time.
Re: Help required in analyzing Network waits from AWR [message #585766 is a reply to message #585763] Wed, 29 May 2013 10:32 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I think you are trying to fix something that is does not need to be fixed. No end user has telephoned your help desk to complain that the "ARCH wait on SENDREQ" event is too high. It has no impact on response time or throughput and is therefore not a problem. And even if it were a problem, it is a tiny percentage of your 47739 seconds of DB Time.
If there is a performance problem, you should probably be looking at tuning the SQL to bring down the number of single block reads. It is those that are soaking up your DB Time.
Re: Help required in analyzing Network waits from AWR [message #585807 is a reply to message #585766] Thu, 30 May 2013 02:40 Go to previous message
sysdba007
Messages: 28
Registered: November 2012
Junior Member
Hello BlackSwan and John

Many thanks for your replies

I liked the term 'CTD' very much; But honestly I don't think I am suffering from it Smile ... Thanks to you Gurus from ORAFAQ forum, from whom I lerant many things

In fact following is my exact reply to the person complaining about it. I have just excluded 'salutation' and have masked db names; rest all is word to word same as I replied him

Quote:

In a AWR report of 1 hour span, on a system with 16 CPU(logical) the max db time that can be availed is 57600 seconds out of which in average 700-800 seconds are sent on "Network waits" and again out of the 700-800 seconds, 50-60% is constituted by the element "ARCH wait on SENDREQ" which occurs while shipping archivelogs to standby database / data guard

Now on the XXX database with 2 node cluster (XXX01 / XXX02) the node -1 i.e. xxx01 is mostly active and from which approximately 3 GB of archivelogs are shipped to standby database ( yyyy01 / yyyy02)

As such if we think these 700-800 seconds are significant w.r.t. the database time available for a period we might want to check network link in between the XXX cmd database and its corresponding standby database (YYY)


Thanks and Regards
sysdba007
Previous Topic: Best disk config for SME scale datawarehouse
Next Topic: Keep Pool
Goto Forum:
  


Current Time: Thu Mar 28 11:43:16 CDT 2024