Returning to the subject of Sunday's post, I have gotten to the bottom of all of the issues regarding SATA vs. PATA and the missing DVD drive. I am grateful to those on the web who have posted various solutions regarding these issues. I'm going to go one step further and document my thought process a bit in order to assist others who don't even know where to begin looking at these issues.
1) I started from the issue that my DVD drive was not being enumerated by the ata_piix driver. I got hooked on the notion that I needed to enabled ATAPI support in the driver. It's experimental so it's not enabled by default. Depending on whether it's compiled into the kernel or loaded as a module, you have to either pass a boot parameter (libata.atapi_enabled=1) or do the same via the modprobe.conf file.

2) I spent an inordinate amount of time working on this solution. Trying it each way, compiling new kernels, rebuilding my initrd, etc. All trying to get it to accept the parameter. One thing that really hindered me was the fact that there is no way that I know of to query the loaded modules and ask "what parameters did you receive at the time you were loaded?" to sanity check that I was doing the right thing.
3) After reading a few dozen web pages, I noticed one mentioned that you should set your BIOS to AHCI if possible to get this work. While I think this is actually in error (since if you were set to AHCI it would use a different driver), it led me to the eventual correct conclusion. My BIOS (on the Abit AW8D) supports a bunch of different modes, and I for some reason had it set to Combined Mode. I now know that Combined Mode has issues, but I didn't know that at the time I set things up. What I really wanted was enhanced mode, and to set the IDE mode to AHCI. Since I have an ICH7 chipset, this should be no big deal, and Fedora will use the ahci driver instead of ata_piix. This is exactly how it works on my laptop with the same chipset.
4) However, I couldn't get the system to boot in that configuration. I assumed that I was doing something totally wrong and that it wasn't recognizing the drives. I was totally stumped because it would boot and then print out

"GRUB GRUB GRUB" or something like that, as if Grub was trying to load and getting screwed up.

5) I tried booting in AHCI mode with the rescue disk. No problem. System comes up, drives can be mounted.

6) Now I'm starting to suspect that it's just an issue with the grub configuration. Sure enough, I run:

grub-install --recheck /dev/sda (since my MBR and boot partition are on the SATA drive)

and notice that previously, the device map looked like:

hd0 /dev/sda (the SATA drive)

hd1 /dev/hda (the PATA drive)

and now it's swapped. Apparently the switch from Combined to Enhanced (required for AHCI) caused the drives to be enumerated a different order. This wouldn't be such a big deal, except that grub has to be installed on the first drive, or you have to add a chainloader directive. But what's with the GRUB GRUB GRUB stuff? Then I remember, I used the PATA drive as the main drive on a previous installation so it has an old (and now totally corrupted) grub installation. That was the big red herring this whole time.

7) So I grub-install onto /dev/hda and then change all the hd0s to hd1 (since the boot partition is still on the second drive) in the grub.conf file. System boots, but hangs since I still have the ide0=noprobe directive. Take that out, try again, system boots to login.
8) It now comes up with the ahci driver (I guess kudzu on redhat figures this all out), and the the PATA drive and the DVD drive are handled by the ide subsystem. I look at the drives, and everything is golden. DMA is enabled on everything, and I'm seeing hdparm timing results consistent with what I was seeing with the ata_piix driver.

So that ends the saga of the slow or missing drives. What is frustrating is how much time I spent on the problem of trying to keep the ata_piix driver from fighting with the ide-generic driver, rather than noticing that my real problem was that I was just in the wrong mode. The ahci driver doesn't have this problem so it's not even an issue. I was so focused on thinking that I was clever with the initial solution of setting the combined_mode flag that I ignored all the evidence that was telling me that I was doing things wrong from the beginning. One last note, I think that everyone using Linux on SATA drives owes Jeff Garzik a note of thanks for all his hard work, and if you'd like to learn more about this stuff, his webpages have a wealth of information.