I've seen this in movies and in computer games, and I don't really understand it. I can only see ladders used in terms of siege mentioned in passing.
An easy tactical counter would appear to be to just push the ladder over, which would kill/injure those on the ladder, as well as those around the area in which the ladder fell. I would imagine that climbing up a ladder while carrying weapons and armor would take quite a long time, and leave more than enough time for people manning the wall to simply push it over (or at the very least, snipe those climbing up with arrows, where the top person would fall onto those beneath them, creating a chain reaction).
Why were they effective? Did they simply overwhelm those manning the walls so they couldn't push them over in time?
A lot of the times, the ladders themselves were very heavy. They were built out of iron or very dense, very solid wood. It often took teams of men to actually put the ladders up against the wall and on the battlements of walls, while deep enough for a group of men to possibly push it over, the defenders were also under arrow fire and artillery barrages. Not to mention the possibility of other siege engines like siege towers or battering rams.
The defenders of a fortification definitely had distinct, very powerful advantages and this led to most attacking armies to simply besiege the fortification with enough men to encircle it while sending the rest of the army to forage and pillage the surrounding countryside and nearby villages.
Going back to your idea of pushing the ladders off the wall: while this was possible and happened at times, many attacks on fortifications used siege towers. The Chinese Han dynasty and the Roman Empire often used siege towers when attacking cities. The Romans used them when taking Masada and at Jotapata, and attempted to use them in the Battle of Jerusalem but were unsuccessful because the Jewish defenders tunneled underneath it and caused it to fall into the earth.
The use of ladders in attacking walled cities was called "Escalade" and involved a tactic involving using soldiers attacking up the ladder as a distraction while sneaking in a saboteur that could open the city gates from the inside. To counter ladder usage, additional defenses were constructed. Moats were often dug around walls, the walls themselves were crenelated to not allow a ladder to sit against the wall, machicolations (sites within the walls that allowed defenders to attack the soldiers going up the ladders without exposing themselves to much danger), and the use of burning oil and tar.
Using ladders or the escalade tactic was usually only reserved for the most dire, last-ditch efforts to take a city that could not be properly besieged, as a siege was by far the safest and least expensive way to gain a victory. Why waste your valuable troops by turning them into heavy casualties when you can just starve your enemy out?