Wireshark, formerly known as Ethereal, is a free network packet capture program for several operating system platforms. Wireshark is able to reveal a wide range of network communication problems:
The problems might include self-inflicted problems that result from implementing changes to improve one performance problem, as suggested here:
The problems might include client computers with 100Mb network cards connected into gigabit switches, which then results in unexpected packet retransmits until the client computer is moved to a 100Mb switch that then connects to the gigabit switch (this is a rare problem, somewhere I have a Wireshark capture that shows this behavior).
The problems might include failing network equipment or bad network wiring or excessive EMF in the environment that distorts traffic on CAT 5e, CAT 6, and wireless connections.
The problems might include a client application that unexpectedly takes 10 to 20 seconds to “log in” when it should take 1 second or less.
The problems might include issues with inappropriate fetch array sizes, poor choices for SDU size, forcing jumbo TCP/IP frames through intermediate network hardware that does not support frame sizes larger than roughly 1500 bytes.
The problems might include a high latency network or WAN connections.
There is nothing terribly out of the ordinary with the above. The client computer in packet 1 sent an ARP broadcast packet to the network asking for the MAC address of the network card on the network that is associated with IP address 184.108.40.206, and the response should be returned to the client computer at IP address 220.127.116.11. Roughly 0.002 seconds later the client computer attempted to connect to the database server using the TNS protocol. Roughly 0.1 seconds later the connection completed. Roughly 0.06 seconds after the connection attempt finished, the client computer started sending queries to the database server. There were a couple of delays between the submission of the SQL statement and the response from the server, such as the 0.12 second delay between packet 26 and 27, but nothing significant.
In the above, notice that the server (IP address 18.104.22.168) is resending packets that it assumed were lost in transit due to the long delays between packets without receiving an ACK from the client computer (the ACK packet may have been lost). Notice also the long delays between packets that might either be a symptom of network problems, or CPU/Wait Event that could be captured in a 10046 extended SQL trace.
Next, let’s take a look at the effects of adjusting the fetch array size (number of rows retrieved in each fetch call – ARRAYSIZE setting in SQL*Plus) when executing a SQL statement in SQL*Plus that selects from a table having an average row length of 245 bytes, with the client on a wired 100Mb connection, and with the standard Oracle SDU size. The server is still at IP address 22.214.171.124, the client computer (same as used above) is now at IP address 126.96.36.199.
Fetch Array Size 15:
In the above, you might notice that after every two packets that are sent by the server, the client computer sends back an ACK packet – this is typical behavior. So, what happens when someone “optimizes” the network card parameters?
In the above, notice the number of packets transmitted before the client sends an ACK packet, and typically just before the client sends the ACK, there is a delay of roughly 0.2 seconds. OK, a little slower. So, what happens when we switch from the table with the average row length of 245 bytes to a table containing roughly 1MB to 2MB JPEG pictures? Compare how long it takes to reach the 35th packet in the following two screenshots:
(Late Additions to the Post)
SQL*Plus SELECT from the table with the average row length of 245 bytes using the 802.11G (54Mb) connection:
Fetch Array Size 100 with “Optimized” ACK frequency (Same Data):