Take hundreds or thousands of tnsping samples. Don't sample once every second, or you might have an unsavory encounter on your way home from work. Create a simple shell script that runs tnsping once every 30 or 60 seconds, directing the output to a text file. And let this run for a couple of days. Do some fancy awk'ing and grep'ing, and then drop the data into an Excel spreadsheet to get a good understanding of the situation.

Besides calculating the typical mathematical statistics on the latency, such as the average and standard deviation, look for daily trends. It could be that during peak Oracle processing times, latency time skyrockets. Perhaps there is a lot of non-Oracle activity during this time, and the network is taking a beating, so the administrators are routing all other traffic (including your SQL*Net packets) around the problem! I have found that the network administrators may not know the Oracle system is experiencing network issues-that is, until you tell them about it. They are focused on support issues and ensuring the network is available (sounds like some DBAs I know).

Figure 4-23 is the result of sampling tnsping results once an hour for three months. There were 1,210 samples collected, the average latency was 47.90 ms, and 90% of all the samples were less than 47.38 ms. It might seem strange that the average latency is greater than the ninetieth percentile figure, but this is due to a few samples over 600 ms. For this particular application, service levels dictated the latency must be below 100 ms 90% of the time. Most applications require the latency to be within 20 ms or even 5 ms. So while there are clearly latency times greater then 100 ms, both the average and the ninetieth percentile figures meet service-level requirements.