We have 2 identical Oracle servers with the exact same objects in each database. In one database, the first time a query is executed and the data is moved to SGA from disk the query takes about 40 seconds. However, in the other data base, when the query is executed and the data is moved to SGA from disk the query takes about 6 minutes.
If we execute the query the second time on each, the results are the same.
We are pretty sure that there is an I/O bottleneck on the 6 minute machine.
What command could I use on the UNIX box that will show me the OS process id and the packet transfer rate in order to check our theory?
Which manufacturer and version of unix?
Which version of Oracle?
How many different computers are involved in this? It is a client-server system or is all the action taking place on the server(s)?
Which manufacturer and version of unix?
OS - Sun SPARC Enterprise M4000 w/Sun OS 5.10 (64 bit)
Which version of Oracle?
10.0.2.0
How many different computers are involved in this? It is a client-server system or is all the action taking place on the server(s)?
The M400 is really only used to load Oracle into memory. All of the tablespaces reside on a Network Appliance system which consists of 168 disks referred to as an aggregate.
We believe that there is an I/O problem getting the data from the filer system to SGA on the slower server. However, the network folk think we are wrong, so, we are trying to find a way to monitor the transfer rate during the execution of the query on each box to compare the transfer rates and expose the bottleneck. We think it is a bad NIC card, but, the network folks are not helpful, so we have to find a way to prove it to the higher ups. You know what I mean?
On the faster machine, only about 10 seconds are added when the data is on disk. The slower machine adds about 6 minutes to get the same amount of data to SGA.
We are executing the exact same query on the exact same data on the exact same Oracle instance and the exact same box.
We just took the init and spfile and file volume from one and copied it to the other server and mounted it with Oracle.
First check that your server NIC and your network port are set to NOT autonegotiate. Both need to be positively set to a LAN speed and full duplex. If you have to change the setting, then cold boot the server afterwards.
A Half Duplex error caused by autonegotiation is hard to detect from unix but could cause your symptoms.