Check out the article in Wikipedia at
http://en.wikipedia.org/wiki/Self-modifying_code
Basically:
Accidental modification can lead to the program crashing in a location extremely unrelated to the actual code that had the error. (eg An write to an unitialized pointer may cause a crash when the pointed INSTRUCTION is executed, which appears random).
And since the code in the source file will not be changed it's a nightmare to find.
Modification of code also leads to easier attack vectors, since the attacker can write actual code and execute it, without regard for higher level protection (eg no-execute bit).
Actual usage:
You can hide code inside data, eg scramble the code and reassemble it at runtime. Many copy-protection programs did that. This way the actual code doing the checks was not present on a disassembled executable.
You can also store compressed executable code along with the code to decompress in-place. The first time the code is executed it's decompressed. After that it's already present. This plays havoc with modern OS paging, but it's an extreme solution for systems with tight storage memory and large RAM (or extremely slow storage).
Nowdays it's mostly used to change pointers to functions when loading dynamic libraries.
eg Load the library and set the pointers to the function versions you want (optimized for this processor).
I guess you could also apply it the same way to change the functions in-place based on hardware / configuration information.
Another use today is the JIT compilation in Java and Javascript interpreters. The bytecode gets changed into actual machine code and executed. Since they use metrics to optimize the code (based on loop counters and branches taken), the same thing could be done to optimize native code.
You run the code for some time with instrumentation instructions (eg counting branches) and then change the actual code by removing the instrumentation and changing the branching code.
But the difficulty and security problems are what has all but removed self-modifying code from our current programming habbits.
And the slowdown due to having to flush the cache when the code changes.