OProfile is useful for measuring system-wide resource consumers (for example, you can see what pieces of code are causing cache misses in the kernel when your apache process is in the kernel etc, or which user processes take up the most CPU time).
DTrace can also do something similar (though it needs a little more work yet). But DTrace does a LOT more than this. Imagine a system-wide (kernel, binaries, libraries) 'strace', where you can trivially choose what to print out, and what parts to strace, and under what circumstances. DTrace does even more than that.
OProfile can't tell you exactly why your system call is returning EINVAL. OProfile can't tell you why your application is causing cross-calls. OProfile can't tell you what processes are writing to what files, in real time. OProfile can't debug race conditions.
OProfile is a profiler: it does its job and nothing more. DTrace is, essentially, an instrumentation suite; one of its abilities is to function as a simple profiler.
You won't really get a notion of why DTrace is so useful until you try it.