Generally speaking, dual core CPUs or dual CPUs (SMP) won't make things faster, so much as they will make things SMOOTHER.
A game, or any other program, has to be specifically written to use more than one CPU, but you can do like Bob mentioned. Have the game running on one CPU, and everything else on a second CPU. This allows the first CPU to focus all its attention on the game, while the second CPU handles all the little requests background programs make.
If a program is written to be able to exploit dual CPUs, then things can get a significant speed boost. It's not a straight addition of clock speeds though. So if you have 2 CPUs that are each 1GHz, and you're running something such as Adobe Photoshop which will take advantage of multiple CPUs if present, it won't be like running it on a single 2GHz CPU system. In some ways the dual CPU system will be faster, and in others it will be a little slower. It all depends on how well the application programmers did the logic to break tasks up and distribute them between CPUs. It's a very difficult thing to do well, which can lead to large performance differences between two different SMP aware programs.
Right now, I don't think there's likely to be a single game out there that's SMP aware. As multi-core chips become more and more common, this may well change, but for right now there probably won't be enough of a performance gain in any game to justify the costs of a SMP board, a second CPU, etc. If you have the opportunity to inherit a SMP system, or can get one on the cheap, that would be a different story. Just being able to have a CPU dedicated entirely to a game would likely improve the experience. You may not get much in the way of added FPS, but you should be able to maintain a much more consistent FPS level.