1) Macro, to get a full view of the small object, cos when u engage macro, the DOF become shallow, so u need smaller aperture to get more DOF
2) Bright sunlight or lightsource. for eg, shooting silouette with sun or flash on the back, or shooting a brightly lighted area without additional filters
Small aperture used for deeper (or wider) depth of field. Also to maximum cut down the amount of light pass through the lens.
Small aperture doesn't mean the best aperture for the lens. Sweet spot (or the sweetest aperture) for the lens usually (but not always) 1 or 2 step down from maximum aperture.
Pro
1. Increase DOF, useful for macro photography
2. Slow down the shutter speed, useful for silky effect on water flowing or long exposure for stars trail, etc
3. Star bust effect on bright source like Sun
One more thing to add, f/22 when used on a digital sensor (>10mp) will not improve sharpness further because of diffraction limitations. Smallest aperture you should use is f/11 or f/16.