I have a dual P3 server (with P3-S 1.26GHz!) at my home right now... that I'm currently using to do some finite-element runs. Its a wonderful machine... and great to work with... but I think the MAIN reason is that a home user REALLY can't exploit it. Megahertz for megahertz (or a measure of performance--which is better), a single processor is really cheaper. You can easily purchase a 2GHz chip for what two SMP capable 1GHz chips cost. Plus, you can't think of CPUs linearly adding--they do SHARE resources such as RAM and the hard drives.
I think the BIG barrier, though, is software. Because, in my opinion, there's no benefit to SMP if you're not running multi-threaded apps. Sure, you can "multi-task" well... but in all honesty, with as cheap as computer components are, buy a second computer (with a second hard drive, etc etc etc) and a KVM switch. I personally decided, that with my own money, that's what I would do.... when I lock up my P4 with a FE run, I just switch over to my old P3-933... which handles any "minor" work I need to do (word processing, browsing, email... even doing the math for my homework).
Now, dual machines do have their place in servers (where multiple people are putting large demands on the system) and computational systems where you can make multiple "runs" on the same machine--or "thread" your program to use multiple CPUs.
But for the home user, stick with a single processor. I think you'll see more benefit spending the extra cash (which is a lot--expensive motherboards with no onboard features, an extra processor, and typically registered RAM) elsewhere. Go SCSI for the cash.