If you say you've never seen bumpmapping used well, you haven't played a video game in quite a long while.
Take Oblivion for example: Most of the details are bumpmaps; all but the basic structures in fact.
Perhaps you've played any sci-fi game in the last five years? All those wires and pipes, tiles, bricks: bumpmapping.
Played any game where there's a tile floor? All of that grout and all of those bevels are bumpmapping.
The reason you think you haven't seen bumpmapping used well is because you only notice the bad bumpmapping as bumpmapping.
You notice the good bumpmapping as detail.