I was talking to an Apple sales rep, and they were saying the Snow Leopard will determine how to spread the load over the cores automatically. So I guess it isn't dependent on the software. The only issue I might have is to boot into 64bit or 32bit.
The sales rep was wrong, it very much depends on the software and an app coded for one core generally won't be helped at all by the OS. That's not uncommon, they seem to give out wrong info all the time.