I'd have to agree with CMH and supersmashbrada that your limiting factor in games would be the GPU in most cases (exception below), but in apps that use cpu only like video encoding and music converting you will see a difference.
CPU Bound:
The frame rate bottleneck in a game is the CPU. The GPU is waiting around for data to be fed to it. IE: at low resolutions where the cpu isn't feeding the gpu with frame information fast enough
To test this lower your CPU speed to its default settings, and load up a recent game Crysis, Farcry and set the resolution to say 640x480 and lower all the the settings to their lowest levels, then run the same test again with the overclock you should notice a difference. I choose those games because they have built in benchmarking tools, I'm sure there are more games out there with benchmarks, so please no flaming for mentioning Crysis.
GPU Bound:
The frame rate bottleneck in a game is the GPU. The CPU is / could supply data faster than the GPU can render it. IE: at 1920x1080 and above, where the GPU's Frame buffer / GPU itself just isn't fast enough
To test for GPU limitations set your monitor to its highest resolution and playable settings. Run the benchmarks with your CPU at stock speeds then run it overclocked. If you are GPU bound you should see little to no change in the Frame Rate.
And FFS Don't us a console port like GTA, etc to test this cause you will always be cpu bound.
Sorry if any of this information is confusing, and/or wrong, this is a hard subject to describe IMO.
Negative affects of overclocking.
More Heat
Shorter lifespan