I recently made the upgrade from an Athlon x2 (2.3Ghtz) to an Athlon II x3 3.1 GHtz. The performance jump is very noticeable. My futuremark score went from about 6500 to just over 11000. (That's the only benchmark I've checked...) I did not upgrade the motherboard, ram or anything else.
The upgrade is great value in terms of giving an old system more speed. That said, I'm sure that my system would be faster with a new motherboard and faster ram. Realistically getting a new motherboard and ram along with the new CPU would triple the cost of the upgrade. Thus, the new processor is likely not delivering all the performance that it could but I'm probably getting most of the increase in speed at about 1/3 the cost. That's a pretty good value proposition.
In terms of a bottleneck, most systems are limited by a slow component. If you replace an X2 6400 with a phenom II 965 it should improve the speed of the system significantly. (I can't think of any reason why it would not .) The problem is that is the AM2 motherboard will be limiting how quickly the 965 can communicate with the other components. This will be true of any processor working with an old motherboard.
The only other thing you might want to check before doing the upgrade is your power supply. It should be able to supply your processor, video card and a safety margin using only the 12V rail(s). The safety margin is up to your discretion but 100W is pretty safe whereas 50W might be asking for trouble, particularly on an old power supply.
