I can't speak for all applications- as you say, many don't place the demands on the duals. Photoshop rendering, 3D modeling, CAD, video rendering, game graphics- these all benefit from the dual processor. What you are saying was especially true in OS 9, and earlier versions of software, prior to the prevalence of dual processors. Now, OS X, as well as most third party software, have dual processor optimization built in. So much so, it's not advertised as it used to be. Also, with everything becoming more and more graphics intensive, the duals, combined with a performance graphics card, would really shine, as compared to a single.
Granted, not all gains are 40-60%, some are, some are 15-25%. But, a gain, is a gain.
I also have a Dual 450mhz GE (Mystic). It is only slightly upgraded- RAM, Graphics card, hard drive, superdrive, BUT, it loads the OS X, on start up, about 40% faster than this single 2.0. Not sure, but I would say that says something for the dual with L3 cache.
All things equal, if I did it again, I'd spend a little more money, for a lot more performance.
G4AGP(450)Sawtooth, 2ghz PowerLogix, 2gbRAM, 300gbSATA+160gbATA, ATI Radeon 9800 Mac OS X (10.4.8) Pioneer DVR-109, ExtHD 160gb x2, 23"Cinema Display, Ratoc USB2.0, Nikon Coolscan