Yes, you'd need it delivered often, and dry ice is rather expensive. You then have OH&S risks associated with the handling of dry ice as it can be quite dangerous if the correct precautions (eg; PPE) are not taken. Some go with combination systems that use ice (being cheaper) to cool the regular output of a glycol system causing it to sit low to the ground. Antari, for example, make such a product; http://www.antari.com/index.php/web/Products_i/28. Of course, that's all well and good so long as you're going to be content to keep refilling the machine and have a process for dealing with the water. You can purchase refrigeration addons so that you don't need to use ice, but by that stage you're spending quite a bit of dosh. Some people just cut entry and exit holes into an esky filled with water and let that do the job.
The thing that keeps the fog low is purely the temperature. It will fall down steps and off edges of stages (and in stage shows, particularly, the orchestra pit). Dry ice will provide CO2 and water vapour and that's what gives you your smoke. It is normal in higher concentrations of smoke to find water deposits afterward.
Conventional (warm) smoke machines use glycol, usually mixed with distilled water (cheaper!). Different concentrations give you different dissipation times. The basic principle is that the stuff is pressurised (hence the noise you often hear at release) and passed through a hot element to vaporise it. It'll spread out through the air because it's quite warm. Afterwards in higher concentrations you'll end up with a sticky glycol residue over all your gear. Common failures on these machines are the pump assembly, the heating box/element, and blocked fluid hoses. None of that is much use if you run the machine dry though...
Hazers are a little different and are often considered 'atmospherics' by effects designers and is used to highlight beams of light through the air where they would otherwise not be visible. The trick with haze is to keep as low of a concentration in the air as possible whilst still being able to see light beams. Haze is designed to be turned on in advance of its use to allow it to spread throughout the space and is intended to keep a uniform distribution of particles through the air and uses a fan to achieve this. Afterwards you'll end up with an oily deposit across all of your gear. Hazers are more expensive, so you'll often see people buying smoke machines where a hazer is what is actually required. Smoke is used when you want to give the appearance of smoke; haze is used when you want to see beams of light.
All of these systems will set off photo-detectors when fitted to fire systems except in cases of low concentration where the detectors have been suitably desensitised once the smoke hits them. A more popular choice is to fit thermal detectors in areas where smoke is likely to be used. Many venues use a combination of both and isolate groups of detectors as required.
In terms of Scooby in particular, whilst I've never ridden the attraction with the effects working I would suggest that the correct choice for the space would be two hazers. Of course, these hazers will need frequent maintenance as they are usually not made to run for days at a time, and will also need frequent fluid refills to stave off the danger of running them dry (needing repairs). A consideration for the ride itself would no doubt be the continued deposits of residue on the track and associated equipment. No doubt this requires careful management.
And for the record (usually) don't use gobos as their beam width (being a laser and all) is insufficient. Instead they use mirrored prisms that move and rotate to create moving patterns. They also aren't gelled to different colours as white laser emitters are extremely rare, and therefore you can't make green light by using a subtractive process from a red light source. Instead there are separately coloured laser emitters in the same casing. Most lasers (or, at least most lasers you should be using around the general public) should have safety cutoffs so that in the event of a failure of the prism system (which would leave the entire output of the laser pointing in a single straight line without moving) the laser will be cut off to avoid damaging the sight of anyone in front of it.
Any questions?