 |
|
Monitoring Network Performance from Oracle
STATSPACK
Oracle Tips by Burleson Consulting
|
From STATSPACK, you can query the
stats$system_event table to see the amount of time Oracle has waited
for network packets. As you recall, there are several system events
that can show us network activity:
L 7-16
SQL> select
distinct event from stats$system_event
2 where event like 'SQL%';
EVENT
----------------------------------------------------------------
SQL*Net break/reset to client
SQL*Net message from client
SQL*Net message from dblink
SQL*Net message to client
SQL*Net message to dblink
SQL*Net more data from client
SQL*Net more data to client
From this STATSPACK table, we can select all of the
significant events, the number of waits, and the average wait time in
seconds. Remember, most networks such as TCP/IP send an acknowledgment
when a packet has been received, as shown in Figure 7-3.
The
rpt_event.sql script here can be run to see all Oracle system
events that were captured in the STATSPACK stats$system_event table.
Figure 7-38:Tracking network latency by timing
between send and acknowledgment
rpt_event.sql
L 7-17
set pages
999;
column mydate heading 'Yr. Mo Dy Hr' format a13;
column event format a30;
column waits format 999,999;
column secs_waited format 999,999,999;
column avg_wait_secs format 99,999;
select
to_char(snap_time,'yyyy-mm-dd HH24') mydate,
e.event,
e.total_waits - nvl(b.total_waits,0) waits,
((e.time_waited - nvl(b.time_waited,0))/100) /
nvl((e.total_waits - nvl(b.total_waits,0)),.01) avg_wait_secs
from
stats$system_event b,
stats$system_event e,
stats$snapshot sn
where
e.snap_id = sn.snap_id
and
b.snap_id = e.snap_id-1
and
b.event = e.event
and
e.event like 'SQL*Net%'
and
e.total_waits - b.total_waits > 100
and
e.time_waited - b.time_waited > 100
;
Here is a sample of the output from this
report, showing the events and the wait times for each event. This is
a great report for showing specific times when the network is
overloaded with packet traffic.
L 7-18
Yr. Mo Dy
Hr EVENT WAITS AVG_WAIT_SECS
------------- ------------------------------ -------- -------------
2001-09-20 15 SQL*Net message from client 1,277 1
2001-09-20 16 SQL*Net message from client 133 64
2001-09-20 18 SQL*Net message from client 325 1
2001-09-20 19 SQL*Net message from client 410 0
2001-09-20 20 SQL*Net message from client 438 22
2001-09-20 22 SQL*Net message from client 306 8
2001-09-21 10 SQL*Net message from client 253 4
2001-09-21 12 SQL*Net message from client 208 0
2001-09-21 13 SQL*Net message from client 230 6
2001-09-21 14 SQL*Net message from client 311 6
2001-09-21 17 SQL*Net message from client 269 21
2001-09-21 18 SQL*Net message from client 222 29
2001-09-21 19 SQL*Net message from client 362 22
2001-09-22 11 SQL*Net message from client 111 32
2001-09-22 15 SQL*Net message from client 353 10
2001-09-22 20 SQL*Net message from client 184 18
2001-09-22 22 SQL*Net message from client 642 104
2001-09-23 11 SQL*Net message from client 125 22
2001-09-23 12 SQL*Net message from client 329 11
2001-09-23 13 SQL*Net message from client 329 172
2001-09-23 14 SQL*Net message from client 310 4
2001-09-23 15 SQL*Net message from client 501 17
2001-09-23 16 SQL*Net message from client 197 49
2001-09-23 19 SQL*Net message from client 214 20
2001-09-24 16 SQL*Net message from client 343 251
These STATSPACK reports can often give the Remote DBA
an idea about potential network problems because Oracle captures the
number of seconds that have been waited for each distributed event. Of
course, Oracle can identify a latency problem, but we need to go out
to the network to find the exact cause of the network problem.
While network tuning is very complex, let's
take a brief overview of the standard tools that are used in a UNIX
environment to monitor network transmissions.
Tuning the Distributed
Network
Tuning a network is a very long painstaking
process of gathering statistics and analyzing them. Unfortunately,
there are no quick or simple answers that will solve all network
performance issues. Basically, you will have to generate a sniffer
trace and check for utilization statistics, retransmissions, and delta
times.
Note that while it is easy to extend STATSPACK
to monitor disk I/O information, it is extremely difficult to extend
STATSPACK to capture network traffic information. Network information
varies widely between systems, and it is almost impossible to capture
meaningful disk I/O information into STATSPACK extension tables.
The most basic tool used by network
administrators is the UNIX netstat utility. Unfortunately, netstat is
implemented differently by all of the UNIX vendors, and the output
from netstat looks very different depending on the operating system
that you are using. Let's take a brief tour of netstat and see how it
can be used by the Oracle Remote DBA to monitor network activity.
Using netstat to Monitor Network Activity
Netstat is a generic UNIX utility that displays
the contents of various network-related structures in various formats.
These formats are determined by the options passed to the netstat
command.
Netstat is very good at telling the Remote DBA what is
happening on the network at the current time, but netstat does not
give a good trending capability or periodic snapshot functionality.
Most network administrators purchase a specialized third-party tool
for long-term network monitoring. Let's look at some of the
differences in netstat and see some of the network information that
netstat provides about the current state of the network.
Netstat on Solaris
On a Sun Solaris server, the netstat utility
provides information about all network traffic touching the server:
L 7-19
>netstat
TCP: IPv4
Local Address Remote Address
Swind Send-Q Rwind
Recv-Q State
------------- ------------------- ----- ------ ----- ------
-----------
sting.32773 ting.1521 32768 0 32768 0
ESTABLISHED
sting.1521 ting.32773 32768 0 32768 0
ESTABLISHED
sting.32774 ting.1521 32768 0 32768 0
ESTABLISHED
sting.1521 ting.32774 32768 0 32768 0
ESTABLISHED
sting.32775 ting.1521 32768 0 32768 0
ESTABLISHED
sting.1521 ting.32775 32768 0 32768 0
ESTABLISHED
sting.1521 az.janet.com.32777 24820 0 24820 0
ESTABLISHED
sting.1521 rumpy.jan.com.34601 24820 0 24820 0
ESTABLISHED
sting.22 onsrv1.jan.com.1120 31856 0 24616 0
ESTABLISHED
sting.1521 rumpy.jan.com.35460 24820 0 24820 0
ESTABLISHED
Active UNIX domain sockets
Address Type
Vnode Conn
Local Addr Remote
Addr
300021bda88 stream-ord 30002225e70 00000000 /var/tmp/
.oracle/s#255.1
300021bdc30 stream-ord 300021f02c0 00000000 /var/tmp/ .oracle/sextproc_key
300021bddd8 stream-ord 300021f0848 00000000 /var/tmp/
.oracle/s#252.1
netstat for Linux
In Linux, we see that the output from netstat
is quite different from Solaris:
L 7-20
Proto Recv-Q
Send-Q Local Address Foreign
Address State
tcp 0 0
donsrv1.rov:netbios-ssn
intranet.janet.com:1351 ESTABLISHED
tcp 0 0
donsrv1.janet.com:1120
sting.janet.com:ssh TIME_WAIT
tcp 0 40
donsrv1.janet.com:ssh
hpop3-146.gloryroa:1096 ESTABLISHED
tcp 0 0
donsrv1.rov:netbios-ssn
192.168.1.105:1025 ESTABLISHED
tcp 0 0
donsrv1.janet.com:6010
donsrv1.janet.com:1104 CLOSE_WAIT
tcp 0 0
donsrv1.janet.com:6010
donsrv1.janet.com:1103 CLOSE_WAIT
tcp 0 0
donsrv1.janet.com:1023
grumpy.janet.com:ssh ESTABLISHED
tcp 0 0
donsrv1.janet.com:ssh
exodus-rtr-2.arsdi:2195 ESTABLISHED
tcp 0 0
donsrv1.rov:netbios-ssn
192.168.1.107:1025 ESTABLISHED
tcp 0 0
donsrv1.rov:netbios-ssn
192.168.1.126:1030 ESTABLISHED
Active UNIX domain sockets (w/o servers)
Proto RefCnt Flags Type State I-Node Path
unix 1 [ ] STREAM CONNECTED 741 @0000002a
unix 1 [ ] STREAM CONNECTED 745 @0000002b
unix 0 [ ] STREAM CONNECTED 182 @0000001a
unix 1 [ ] STREAM CONNECTED 763 @00000030
unix 8 [ ] DGRAM 397 /dev/log
unix 0 [ ] DGRAM 234471
unix 0 [ ] DGRAM 234252
unix 0 [ ] DGRAM 843
unix 1 [ ] STREAM CONNECTED 764 /tmp/.X11-unix/X0
unix 1 [ ] STREAM CONNECTED 746 /tmp/.font-unix/fs-1
unix 1 [ ] STREAM CONNECTED 748 /tmp/.X11-unix/X0
unix 0 [ ] DGRAM 654
unix 0 [ ] DGRAM 589
unix 0 [ ] DGRAM 560
unix 0 [ ] DGRAM 419
[oracle@donsrv1 oracle]$ netstat -sp tcp
Ip:
15753092 total packets received
1 with invalid headers
0 forwarded
0 incoming packets discarded
99397 incoming packets delivered
20325485 requests sent out
Icmp:
1041 ICMP messages received
37 input ICMP message failed.
ICMP input histogram:
destination unreachable: 972
timeout in transit: 31
echo requests: 27
echo replies: 11
490 ICMP messages sent
0 ICMP messages failed
ICMP output histogram:
destination unreachable: 463
echo replies: 27
Tcp:
131 active connections openings
0 passive connection openings
14 failed connection attempts
0 connection resets received
6 connections established
15652680 segments received
20276668 segments send out
6933 segments retransmited
2 bad segments received.
25 resets sent
Udp:
97289 packets received
11 packets to unknown port received.
3 packet receive errors
48279 packets sent
TcpExt:
9 packets pruned from receive queue because of socket buffer
overrun
unix
0 [ ]
DGRAM 407
Hopefully, this brief description of the netstat utility
will give you an appreciation for the scope and complexity of network
tuning.
This is an excerpt from "Oracle9i
High Performance tuning with STATSPACK" by Oracle Press.
 |
If you like Oracle tuning, you may enjoy the new book "Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
 |
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today. |
 |
|
