Regarding the first part of your question, ever since John Keegan published The Face of Battle in 1976, it's been widely accepted that cavalry charges were about psychological, not physical, impact. It was a game to see who flinched first. On the one hand, horsemen are large and loud and terrifying, and humans have a strong instinct to get out of the way; only the toughest discipline and the highest morale can overrule that instinct. On the other hand, regardless of the determination of the rider, the horse is a cautious animal that will refuse to run where it thinks it cannot go. If an infantry formation keeps its nerve and retains closed ranks, horses will balk at the last moment, rather than collide with the target.

A cavalry charge can only successfully trample an infantry formation if there are gaps in that formation wide enough for a horse to pass through (which can appear if even a single man turns away or falls down). If there are, the cavalry will tear straight into the formation and use lances, swords and other arms to hack at the now disorganised and demoralised infantry, which usually results in a rout. If there are no gaps, though, the horses will stop short and turn away, achieving nothing. The classic example of the latter situation is the French cavalry's inability to break the English infantry squares at Waterloo.

It follows that a dense formation of heavy infantry like the orcs at Minas Tirith is impenetrable to a mass cavalry charge unless it starts to waver at the sight of the charge. To their credit, the filmmakers actually show the orcs losing heart when they realise that the Rohirrim are not breaking under arrow fire and are not slowing their advance; some lower their weapons and start looking around nervously. This implies that gaps may have appeared in the orc line, and consequently, that the charge of the Rohirrim could indeed have worked. At Helm's Deep, similarly, the bright light conjured by Gandalf is shown to blind the Uruk-Hai, causing them to lower their pikes and back away; this serves to explain the ensuing success of the cavalry charge.

Once the front line breaks and the horses get inside the formation, the cavalry, towering over their opponents and riding aggressive masses of muscle, have a huge advantage over their opponents, who are not likely to put up much resistance. The result is a rout and pursuit, which historically could rack up a stupendous casualty count on the losing side. Cavalry charges were a high-stakes game, but if the cavalry won, the results would not be dissimilar to those seen in the LOTR movies.

Now, as to the second part: how common were cavalry reinforcements? I can't speak for other periods, but it certainly was a common feature of warfare in Classical Greece. Many battles were decided by the timely arrival of cavalry:

The Greeks fought with the Persians at Malene in the country of Atarneus; the armies fought for a long time, until the Persian cavalry charged and fell upon the Greeks. So this was the accomplishment of the cavalry.

-- Herodotos 6.29.1 (Battle of Malene, 493 BC)

After holding on for a long while without either giving way, the Athenians, aided by their horse, of which the enemy had none, at length routed the Corinthians.

-- Thucydides 4.44.1 (Battle of Solygeia, 425 BC)

The cavalry, charging up, routed the entire phalanx of the enemy, and, following on a great distance in pursuit, filled the country with corpses, slaying more than three thousand of them.

-- Plutarch, Life of Pelopidas 32.7 (Battle of Kynoskephalai, 364 BC)

Since horsemen were so terrifying to infantry, they didn't always even need to charge to break the enemy:

It so happened also that Pagondas, seeing the distress of his left, had sent two squadrons of horse, where they could not be seen, round the hill, and their sudden appearance struck a panic into the victorious wing of the Athenians, who thought that it was another army coming against them.

-- Thucydides 4.96.5 (Battle of Delion, 424 BC)

The Greeks of course realised that they had to try to find some way to neutralise enemy cavalry and prevent a cavalry charge from routing their infantry. The easiest solution to the problem was to deploy more cavalry against them, which forced the horsemen of both sides to fight it out among each other. As long as they were fighting each other, the infantry could do its own thing without fear of the horse. However, this didn't always go as planned:

Now as the Athenian horse attacked the Theban they suffered defeat (...) and, harried to exhaustion by the opponents who confronted them, all turned and fled. (...) The Theban horse did not chase the fleeing men, but, assailing the phalanx opposing them, did their best to outflank the infantry.

-- Diodoros 15.85.4-7 (Second battle of Mantineia, 362 BC)

Thankfully for the Athenians, in a moment fit for a movie scene, a reserve unit of cavalry charged in to save the day:

The battle was a hot one; the Athenians were exhausted and had turned to flee, when the Eleian cavalry-commander, assigned to the rear, came to the aid of the fleeing men and, by striking down many Thebans, reversed the course of the battle.

In short, yes, units of horsemen were often held in reserve with the specific purpose of turning the tide of battle with a carefully timed charge. This is the same tactic Alexander the Great used with his elite Companion cavalry to defeat the Persians in battle and topple the Persian empire.

Your question is probably too vague to be answered. Cavalry have in many battles been a key player in turning the tide of a battle, one example that comes to mind in particular is the Battle of Adrianople, where the Gothic Cavalry showed up late to the battle and caught the Romans off guard, which ended up with a mass rout and the death of the Emperor. However, the same can be said for any kind of reinforcements in general depending on their timing, numbers, location, and ability, amongst other things.

When you say 'usefulness', are you questioning the value of their ability to kill the enemy in droves (as it is depicted in LoTR), or in some other measure?

edit Clarification of some sentences.