The effect it has (apart from the physical effect of its loading explained above), is for those who have a slow connection or that the page is taking a long time to load for some reason, the person who is viewing the page will get the "full" image a few seconds earlier (albeit "pixellated"), and know that it is loading, rather than a partly blank image holder (white space).  It is basically a trick to catch your attention on the image so you wait for it to fully resolve rather than navigate away from the page.  3 scans is generally a good idea. Also it seems that using Progressive (multiple scans) over Linear (default) reduces the filesize and for that reason i use it all the time.

ETA - two exerts from wikipedia on progressive jpg's:

"It is also possible to transform between baseline and progressive formats without any loss of quality, since the only difference is the order in which the coefficients are placed in the file."

"There is also an interlaced "Progressive JPEG" format, in which data is compressed in multiple passes of progressively higher detail. This is ideal for large images that will be displayed while downloading over a slow connection, allowing a reasonable preview after receiving only a portion of the data. However, progressive JPEGs are not as widely supported."

"In every colour, there's the light.
In every stone sleeps a crystal.
Remember the Shaman, when he used to say:
Man is the dream of the Dolphin"

Rich Meadows Photography