The 5900XT has nice memory bandwidth, and that enables it to be pretty much as fast as the 6600GT in Quake-3 engine games, for example Castle Wolfenstein.
In newer games it is much slower.
However, I have been able to play through HL2 with the high reflection for GeForce FX patch and it was very good overall. I later bought a 6800 Ultra and I have to say for single-player there is not much of an improvement. Of course I have higher resolution now but alas the 5900XT could do enough resolution at acceptable framerates. With Doom3 I had to reduce resolution too much for my taste.
BTW, my 5900XT is very nice from a power consumption standpoint, I really miss that aspect (permanently running machines).