I use a Nikon D300, an I currently own a 50mm f/1.4, a 80-200 AF-D f/2.8, a couple kit zooms, and a 105mm f/2.8mm VR micro; Good for photographing the hair on a fly's rear.
If i was shooting a non moving subject, even in low lighting, I would simply use a tripod, a remote release, and mirror up mode (only available with single lens reflex cameras), and stop down to at least f/11 to attain the desired DOF. If I was shooting a single subject in low ligthing, I would need to use the widest aperture available (adds effect with the narrow depth of field + allows for a faster shutter speed). Of course, when hand holding a camera, you must use a shutter speed equivalent to the focal length of your lens (aka: 200mm = at least 1/200 of a second) or sometimes faster with a moving subject.
Even wide open, sometimes you may need to bump up the ISO to attain a good shutter speed in low lighting situations (such as a fish tank). With my D300, I can get away with using 1600 ISO without noticable noise even on an 8x10 print.
I have never owned a P&S camera that comes anywhere near my dSLR. Even the cheapest dSLRs, like my girlfriend's D60, blow point and shoot cameras out of the water. Of course, when I can't hoof around kilos upon kilos of optics, I use my P&S, the P90. It gets points for portability, but I am used to a dSLR and as such, I feel crippled when I don't have my D300.
wow.. my fingers hurt after my first post.