Oracle – Query Performance Degradation from Seconds to Minutes

The CBO will consider lots of different ways to execute your query, taking into account all the indexes and statistics on both tables and indexes.

If you disable an index, obviously the CBO then cuts out all the plans it might have considered that involved that index.

The question really boils down to: "why is the CBO choosing a poor plan", and the first thing to consider is whether your stats are accurately reflecting reality in your database. Is this the case? How do you gather stats, and how often on these tables and indexes?

If and only if the stats are good and the CBO is still choosing a bad plan, you'll need to start considering "why?", then you'll want to have a look at the plan, especially the estimated cardinalities for each step, and see which are badly off. In some cases this is because of correlations that standard stats cannot help with, but cross that bridge when you get to it :)

This is almost certainly a networking issue (as the execution on a different machine is way faster) so I doubt you'll be able to go much further with any Oracle tools. I've never had any needs to tweak out-of-the-box network parameters to get good performance.

What I suggest is you use a standard network benchmarking tool, e.g. iPerf. It needs to be cross-platform since you have a mixed Linux/Windows environment. Keep in mind that the issue could be due to either low bandwidth or high latency. Run the same test from the other client from which you're getting good SQL Plus results, compare and if it's massively different send the evidence to your networking admin guys.

By the way, if you still need more advice I suggest you post to https://serverfault.com/ as you'll likely to reach more experienced network admins there.