It works well as an oil additive in some cases, and some have found benefit as a fuel additive. It's been used in aviation since before WWII and was shipped in 50 gal drums for use in the big radials in WWII. MMO has a long history of beneficial use in aviation, but it's certainly not a cure-all and needs to be used carefully IMHO.
It does seem to be heavier than fuel, and if you add even a little bit at the top of a tank of 100LL and then take a sample from the sump, it'll be clear or discolored and smell funny almost immediately. Some people think it makes the fuel at the sump smell like paint thinner.
Since many tanks have been patched over the decades with unknown methodologies, it is not unheard of that a non-leaking tank may start to leak a bit after the addition of MMO. In the case I was involved with a previously non-leaking tank started leaking (weaping a little in two spots) and stopped leaking after the MMO was run out of the tank, so perhaps the sealant hardened back up a bit. I know of another case with a PA-28 tank that leaked after the owner started using MMO. The history of that tank is unknown as well, but it can happen. On the other hand, many people use MMO in their fuel regularly and don't experience leaks. I suspect tanks that have been patched with non-standard or non-approved sealants may be the most susceptible, but that's a guess on my part. The tank that sprung two leaks and then fixed itself looked like a calico cat inside, so it appears to have been patched over the years with a number of different sealants. In any case, I suggest using MMO in fuel tanks cautiously, at least initially to see whether it causes any problems.