"Holy" is an exclammation of surprise or dismay too; it doesn't necessarily have to be paired with a swear. E.g.: Holy smokes! But, really, it’s used as an intensifier. Something holy paired with something profane is shocking and sounds wrong. Thus, it makes "greater impact." There was also once upon a time where saying "Holy Christ!" was blasphemous (it’s against the Bible) and worse than anyhing else, so people replaced the name with other words.

In my experience, it's not common to use this expressions any more in most parts of the English-speaking world, except perhaps as a joke. It was a common (funny) theme in the original Batman comics and TV episodes e.g. holy mackerel!