John's quite a hero versus John's quite the hero

Perhaps an illustration would help.

In American English, you can pronounce the word the in two ways: thē before a vowel, and thə before a consonant. If, however, you want to single out something or someone that/who is perhaps the epitome, you use the thē pronunciation even before a consonant. For example,

Richard is a great--if not thē greatest--motivational speaker working today.

I'm not saying the pronunciation is correct, but Americans do use it as a way of signaling someone or something is the epitome. In my sentence, I stared out saying Richard was great, and then I promoted him to thē greatest of motivational speakers. (Granted, my exemplar changes the word great to the word greatest, whereas your second exemplar keeps the words little and man the same.)

Your second sentence seems to function similarly. Whereas the first sentence is a lovely compliment, to be sure, the second sentence puts little Johnny up on a pedestal as the epitome of young manhood. A Brit would not likely pronounce the word the, thē. The word does, however, serve to indicate the exemplary, man-like behavior of the young boy, in a way in which the word a does not.

I think that quite the hero is nearly obsolete, and tends to have a somewhat ironic, or patronising, tone, absent from quite a hero.

(I took it that you did not mean "quite THE hero" that some others have assumed).

I suspect the answer is lurking in the question, but the addition of the word 'quite' is clouding the issue.

Try 'John's a hero' alongside 'John's the hero'. The first, 'a hero' carries an implication that the quality of heroism in John is an assessment or judgement made and held by the person speaking. Whereas, the second version 'the hero' carries an implication that this is a title of acclaim, given and held to be true by the speaker AND others, or perhaps only by others (when said with a certain dismissive inflection), or perhaps by nobody (when said sarcastically).

Essentially it's a subtle shift in meaning from a personally held judgement of quality which doesn't carry so much weight because it remains subjective, to a recognition of a shared (and usually more emphatic and influential) judgement of many. The dismissive and ironic versions follow naturally.

In a dismissive sense, the speaker might even do that gesture with the fingers in the air to indicate quote marks around the words 'the hero'. But in the first two senses ('a hero', and 'the hero') there is no special emphasis in speech.

Perhaps in American idiom the distinction is clearer. One might say, "John's a man" which might be stating the obvious, or a contraction of "John's a (grown-up) man". But it is obviously different and less emphatic than saying "John is -the man-", or "He's de man" in vernacular. The latter clearly suggest a public profile and acclaim.

The use of 'quite' is, I suspect, more of a British thing these days, but very few would recognize it is the same word as 'quit', as is 'quitted', 'quieted' or 'acquitted' which is essentially to say 'resolved', 'settled' or 'accounted for'. The original Latin for 'quit' suggesting 'quietening' or 'settling' was applied equally to natural events (which were attributed to relations between the Gods, or between the Gods and man) and to human relations and obligations and debts. Eventually to 'quit something' was to settle or state the account of something, or keep a 'good tab' (a credit standing or rating). So to 'acquit yourself well' was to be in 'good standing', and for something to be 'quite' was to suggest that the accounts had been audited and whatever was 'quite ....' was something that had been calculated and accounted.

In saying 'quite' the speaker lends a subtle level of abstraction to the statement or value that follows. To say 'he is quite a hero' is essentially to say 'he is a hero by my calculation'. And 'he is quite the hero' comes out as, 'he is the hero by popular account(ing)'. Rather than suggesting that 'calculation or accounting' makes it even more irrefutable and certain (as we'd expect), there's a very faint suggestion of a modest or muted result. The assessment is not drawn from the passion or emotive effect on the speaker or public, but derived from the somewhat dry 'book of accounts of human actions and intentions'. It is a way that British speakers might keep emotions at bay in their speech, as in:

"Carruthers, our chaps have just climbed Everest backwards, what do you say to that?!"
"Well yes old bean, quite."

In the case of a parent speaking of their child, the addition of 'quite' allows the preservation (or simulation of) some kind of objectivity and (perhaps false) modesty. They are saying, "It is not because I say it is so, or because I feel it is so, but because it is accounted (and audited) and this is the result that has been tallied up." All of this dissimulation is largely alien to American English I suspect, and certainly to Australian English.

My understanding is that "quite a hero" is the more neutral of the two phrases, whereas "quite the hero" expresses more novelty/unexpectedness, or a higher degree of heroism.

For an example that feels clearer to me:
"John is quite a pianist" says merely that John is solidly accomplished in the field.
"John is quite the pianist" almost begs for an exclamation mark, doesn't it? It sounds like the speaker is surprised at the magnitude of John's accomplishment, or that the listener should be surprised. And yes, that makes it more likely to be used sarcastically.

I don't think it can be analyzed in terms of the general semantic difference between the and a alone. It's just the way this phrase works.

(Non-native speaker here, and without particular connections to any one dialect.)

I think the second form combines a common idiomatic expression with the rhetorical device of synecdoche. See this answer from a related question. The way that I read and understand "John's quite the hero" is that John truly lives up to the paragon or exemplar or paradigmatic (or specific) instance of "the hero". In contrast, "John's quite a hero" is more subdued and replaces a comparison to a singularity (as denoted by "the hero") with group membership (as denoted by "a hero").

'John is quite heroic' is a rather subdued statement, implying he could be (and perhaps should be) more so. But the use of an adjective only refers John to the quality of heroism.

'John is quite a hero' is stronger, but still does not suggest that John is altogether heroic. But a progression is seen in that the noun 'hero' is used rather than the descriptive adjective. Instead of a description of a quality, now the quality itself is attached to John.

But the use of the indefinite article means that there are many heroes and John is but one of them.

'John is quite the hero' says, now, that John is - almost - the absolute example of heroism. He is - almost - 'the hero' above all other heroes.

'John, the hero' would be even stronger.

And, debatably (see Peter Masters and 'The Acquisition of the Zero and Null Article' (elsewhere on this site) . . . .

'John - hero' (or, colloquially, 'Hero John') is even stronger still.