Just having a game be dual-core "aware" or "utilize" the second core, doesn't necessarily translate to actual performance increases though. Quake4 is still a perfect example of this. At lower resolutions (in this case, I'm considering 1280x1024 to be "lower"), Quake4 can gain as much as a 40% increase clock-for-clock dual vs. single. At higher resolutions, that increase drops to 0%.
I have no doubt that, in the future, this will improve so that more and more games are utilizing the dual-core architecture better (such as Alan Wake, separating particular tasks to different cores). And, with dual-core chips becoming so mainstream (I can't imagine building a new machine today without one), it makes sense to use one even if there is no major benefit for you at the moment.
But, for those of us still on s939 single-core, we find ourselves in a bit of a jam. Do I spend money on a dual-core CPU that won't benefit my gaming much (at my resolution), or do I spend that money on a faster video card that is guaranteed to increase my performance by quite a bit? Or do I just save as much as I can and upgrade my whole platform when I can?
This comes down to individual choice, really. But, if I had a decent video card such as a 7900gt, with a single-core A64 running at around 2.4ghz, I would throw that $150 into a new video card before I threw it at an X2 3800+ (for gaming).
But, if you're gaming on an LCD that limits you to 1280x1024, or often run CPU-intensive tasks such as video encoding (especially *while* gaming), find an amazing deal on a s939 X2, or have a very good video card and just want to spend some more money, or even run into a new game which benchmarks show to benefit greatly from dual-core, go for it.