Please excuse the naive question. I have had two classes now in which this theorem was taught and proven, but I have only ever seen a single (indirect?) application involving the quantum harmonic oscillator. Even if this is not the strongest spectral theorem, it still seems useful enough that there should be many nice examples illustrating its utility. So... what are some of those examples?
(I couldn't readily find any nice examples looking through a few functional analysis textbooks, either. Maybe I have the wrong books.)
What is the spectral theorem for compact operators good for? Here are some examples. (I am ignoring the self-adjoint aspects, since they don't really play a role in the theorem. And it is valid for more general spaces than Hilbert spaces too, so I will also ignore that part, in the sense that I won't pay too much attention to whether my examples deal with Hilbert spaces on the nose, rather than some variant.)
Proving the Peter--Weyl theorem.
Proving the Hodge decomposition for cohomology of manifolds (using the fact that the inverse to the Laplacian is compact); Willie noted this example in his answer too.
Proving the finiteness of cohomology of coherent sheaves on compact complex analytic manifolds.
In its $p$-adic version, the theory of compact operators is basic to the theory of $p$-adic automorphic forms: e.g. in the construction of so-called eigenvarieties parameterizing $p$-adic families of automorphic Hecke eigenforms of finite slope.
It is also a basic tool in more classical problems, such as the theory of integral equations. (It is in this context that the theory was first developed; see Dieudonne's book on the history of functional analysis for a very nice account of the historical development of the theory.)