I think most of this has already been said, but let me give you my spin and some real-world examples.

A CGI Script: Apache calling your script which accesses a MySQL database will take care of most of your problems for you as far as SMP goes. Since most CGIs are pretty short and simple scripts, you wouldn't see much, if any, speed increase with more than one CPU (from the scripts standpoint).

Stand-Alone Scripts: Say you had a script that parses text files, just for fun, let's say they are a couple hundred megs worth. Then let's say that you wanted to parse those files and insert info about them into a database. Here is where you could use a threaded program. Have one thread parsing the text files, and another thread writing to the database. Running on a multi-CPU system would help you some in this case, but to parse a text file and write to a database, your bottleneck isn't going to be the CPU in most cases, but your disk, or your network if writing to a remote database.

CPU Intesive Apps: Say the several hundred meg files had had above are filled with users connection information, like timestamps, and your boss wanted to know the max number of users on at any given point with a 1 minute granularity. All you have is connection info so you are going to have to walk through those several hundred megs and compare them all. Massively CPU intensive. In this case, you would benefit GREATLY from threads and a multi-CPU host.

So in summary: Multiple CPUs are obviously only going to help you when your bottleneck is the CPU. In most cases on a modern server, it is your disk, or your network.