BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

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.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter