I have a Dual 1.25 MDD FW400 (The *very* last OS9-bootable "Power Mac") and here's a couple observations:
Speed: With Dual 1.25 CPUs and the Radeon 9000 video card in general use the machine "feels" about as fast as the 1.5Ghz PowerBook G4s I have kicking around the house. It's faster in some ways, slower in others. (Hard disk access==faster; absolute performance running, for instance, Flash video==slower) I'm sure that if you found some task that leveraged both CPUs efficiently it would be significantly faster in terms of "wall-clock time elapsed" than the single-CPU Powerbook in completing a batch processing job. But in terms of "interactive use" I really don't get the feeling the second CPU makes that much difference.
So... as G4s go an MDD is fast, but it's worth pointing out the obvious:
the machine is a complete and total slug compared to a Macbook Pro. I occasionally use the machine to capture video from a DV camcorder because it has a nice large hard drive in it, but after rendering one or two projects in iDVD on it and having it take the better part of a day I came up with a new work flow: I capture the video on the G4, reboot it into Target Disk mode, and hook up my MacBook to create the DVD from the footage. It's insane how much faster the Intel CPUs are. I'm sure you probably knew this already, but figured I'd mention it since I don't know exactly what you want the G4 *for*. If it's to run 2006 or earlier vintage software an MDD should serve you well. But if you're expecting it to be a better "daily driver" for the modern web than your Powerbook it will brutally disappoint.
OS 9 booting: To my knowledge pretty much any machine Apple made faster than about 700-800Mhz that could boot OS 9 wanted to have a special tweaked version of 9.2.2, and said tweaked version was distributed on the OS X-based software recovery disks that shipped with it.
(Wikipedia has a giant table that points out which machines required "machine specific versions" of 9.2.2.). I *think* the fastest "Quicksilvers" are the one exception, in that they'll boot with the "generic" OS 9.2.something disks you could order from Apple if you needed it after... 10.1?, 10.2? came out? (My memory is fuzzy on this one.)
I'd say it behooves you to try to get the software recovery disk with any machine you happen to buy if at all possible, since I have observed real issues with transferring the "tweaked" versions of OS 9 from one machine to another, and presumably that might also be an issue if you, say, installed the "MDD recovery disk" version on a Quicksilver... which would require hacking anyway. To "install" OS 9 on an MDD you have to install OS X to be able to run the Software Recovery tool which copies OS 9 to a disk partition. The recovery tool is keyed to the machine type, so if you were to run the MDD version on a Quicksilver it would probably barf. It's "easy" to work around this problem, but I'd still worry a little if some machine-specific tweak might cause subtle problems.
(Just for fun, here's the problems I've observed with these specific versions:
1: With the "Powerbook G4 Titanium 867Mhz" version of 9.2.2 on a B&W G3 via a disk imaging the B&W was never stable. In particular the video acceleration extensions were farkled and there were sound problems. They went away when I reinstalled with generic OS 9.0.4 and upgraded to 9.2.2 manually. Oddly the B&W also seems to do fine with an Aluminum-era G4 "Classic support only" system folder... off a 10.3 disk, I think.
2: Cube-specific version of 9.0.4 on a tray-loader iMac: Sound was completely busted, and the problem wasn't solved by a 9.1 update. (Interestingly the same problem occurs if you try to use the Cube recovery-disk version in Sheepshaver.) The 9.0.2 from a slightly older Power Mac G4 recovery disk seemed to work fine, however.
3: My MDD doesn't work booting a "Classic" system folder taken off a Tiger-era Powerbook recovery CD. (it gets into 9 but a lot of stuff doesn't work.) I was surprised because I've actually had reasonable luck in the past with recovery-CD Classic images. I'm not sure whether they've stripped down the System folder in later version or my MDD itself is the problem, as I haven't cared enough to dig into it.)
Of course, you may have no problems at all shuffling system folders around at whim. I'm fully prepared to accept that the deep-seated hostility and contempt I have towards OS 9 is mutual and it's just messing with me.
Reliability: My vague understanding is that Apple did a partial recall on the MDDs because of extremely loud power supply fans and that it's the loud-fan supplies that tend to self-destruct. I do see advice regularly that you unplug your MDD when you're not using it, the claim being that letting it sit in standby mode is actually what causes it to fail. So... I dunno. Mine has a reasonably quiet fan and hasn't died yet despite being very heavily used by its previous owner.
Anyway, that's all I know. Again on the speed point... make you're you're buying this turkey for the right reasons. If you want a desktop computer that actually feels "fast" in a 2010-era context you should be saving those pennies for something else.