Depends on what you do.
As others have said, most games don't currently take advantage of dual core, but that will change once Unreal Engine 3 becomes the standard.
If you photo or video edit though, you'll get a nice performance boost right away. Programs such as Photoshop were designed to take advantage of multi processor systems, and thus take advantage of dual core.
Encoding is another task that will take advantage of dual core, and of course dual core will let you run multiple CPU intensive tasks at once. Dual core systems are "smooth" - nothing really slows them down.