Nice shallow water at about couple feet. It relies again on depth of water, and refraction angle. For example, high son doesn't provide this effect well, as it's not at a sharp angle cutting through the water, a refracting off the back sides of water ripples. Ripple strength is another facter. Rougher waters breakup the nice prism effect. You'll notice these soft prism effects come from pool water, ponds, shore surfaces, etc, areas where it's calm. Water quality also plays a huge role. For example, the heavily teas and silted water of peat-type lakes cuts these effects at the surface.
Here is a nice diagram regarding penetration of wavelengths. For ocean scenes, you will not notice most this effect going much beyond the shallows due to intensity and falloff of spectrums.