Overclocking can be useful for some things, but depending on your device it may or may not promote instability. I have one N900 that overclocks to 900Mhz without issue, minus it getting warm when running at that speed. The second, anything over 600 and I find fs/memory corruption on the uSD card (and sometimes internal fs) if written to a lot while overclocked.

Generally overclocking is useful for short bursts. Going to a flash-heavy site to do image uploads? Turn it on for that for a short time, then back to stable before it goes in the pocket. Running at higher all the time is asking for trouble, as is running at high rates for > 10 minutes or so, since it's not designed to dissipate the heat that makes. There are tons of examples in the forums/wiki for desktop widgets or settings apps that allow this with ease. Queen Bee Widget being one of more popular.