The G94 core that powers the 9600 GT is to all intents and purposes the same as the G92 that sits inside the 8800 GT and 8800 GTS 512, only with less stream processors and different clock speeds. The 9600 GT has just 64 stream processors, or just over half the amount in the 8800 GT, and as a result there's significantly fewer transistors in G94 (505 milliion as opposed to 754 million). At 650MHz, the core clock is actually 50MHz faster than that of the 8800 GT and, in line with this, the shader clock is also a bit faster at 1625MHz. All of which is pretty much as you'd expect.
However, there's a twist in the 9600 GT's tail as, unlike most other mid-range cards that are made from cut-down versions of more powerful parts, it actually has the same memory bandwidth, and number of ROPs as its bigger brothers. This means there's a massive imbalance between the card's shader processing capabilities and its sheer pixel output.
If none of that really means anything to you then think of it like this. The stream processors are like the inner workings of a car manufacturing plant. The combination of people and machines can churn out a finite number of cars per hour at any particular quality level. So, if you want small cheap cars you can get 20 an hour or if you want beautiful handcrafted works of art you can only get one an hour. It's the same with all those in-game graphics settings - if you turn them all up you get a higher quality end product but it takes longer to produce.
Now, think of the ROPs and memory bandwidth as the delivery services that come and go from the factory. If the delivery trucks don't drop off enough supplies or take away enough completed cars, the factory grinds to a halt. So, you need the trucks to be coming and going at least as fast as the factory is producing. However, go too much faster than needed and you end up with trucks making wasted trips, which is the situation the 9600 GT finds itself in. If you turn in-game details down, the card can crank out the frames because of the higher memory-bandwidth and number of ROPs (faster trucks) but if you turn the details up the shader's can't keep up (trucks making wasted trips).
Anyway, that's quite enough of that analogy. Just know that while performance may by impressive in older games, the card may struggle as more and more shader-heavy games are released.
Elsewhere the card offers the same HD video capabilities as the rest of nVidia's latest cards but due to a recent driver improvement nVidia is now offering a number of new video post-processing techniques - called Dynamic Contrast Enhancement and Dynamic Blue, Green, and Skin Tone Enhancements - that leverage the huge processing power of all those stream processors to improve video quality. There's also support for dual-stream decoding, which enables you to accelerate two videos at the same time. As a result of this, you can now watch HD movies without Aero being disabled.