Cavitation happens whenever a tiny area is lowered in pressure (or raised in temperature) by an extreme amount, causing a bubble to form. Since the whole area is usually not that hot or low in pressure, the bubble isn't stable, and rapidly collapses, the collapse creates a powerful shockwave that can pit and destroy metal.
Centrifugal pumps are common victims of cavitation if their 'suction side' is being restricted. The pump keeps pulling, and if it can't get 'fed' fast enough, the pressure in the microscopic region behind the vanes of the pump can drop so low that the liquid there vaporizes (even if it is room temperature). The bubble hits the cooler, higher pressures around it, and rapidly contracts again, causing a 'pop' which you can actually hear, and which will slowly eat up the vanes on the pump. Thats why you always put valves AFTER centrifugal pumps, never before.
In coolant, cavitation can occur in places where a metal deposit traps a bit of liquid inside. The liquid, since it can't travel, rapidly heats up and boils. As soon as the expanding bubble hits the cooler liquid nearby, it collapses again. This rapid formation and collapse of microscopic bubbles pummels the metal, and literally blasts it into pieces. I've seen the results of cavitation, and it can be hard to imagine that it was done by tiny bubbles.
Incidentally, one interesting area of research right now is in the field of sonochemistry, where researchers are using focused beams of intense ultrasonic sound waves to create bubbles in liquid. Since a sound wave is a series of high and low pressure zones in a row, if you increase the intensity enough, the low pressure zones are so low that tiny bubbles form at the low pressure zones. When the bubbles collapse, the localized temperature at the center of the bubble is greater than the surface of the sun (which gives some insight as to why cavitation can cause so much damage).
Anyway, what that means for the need to use Water Wetter, I dunno.
peace,
sam