There are actually two points in time of major 'completions' of the Great Wall – the Qin and Han, and the Ming walls. The wall that stands today, and what most people think of, is the wall that was built during the Ming dynasty. Your question is broad in many strokes, so much so that it could very well take an entire book to assess the “effectiveness” of the wall after its completions. Furthermore, it depends on how, and what, is ‘effective’ – how you see the wall used, and thus how you would rate its effectiveness. However, if we could loosely break it up:
During the Qin and Han dynasties, small subsections of walls were built up and linked together – these small sections never became a single, continuous wall, so obviously its military effectiveness was limited. You could really just navigate the length of the wall to get around it, or bribe your way in, however if you instead look at it as a set of forward reconnaissance posts, then it was quite effective. Assuming it was properly manned, invasion forces were easily seen and reported, and defensive forces could quickly and easily be raised to halt the attacks. This is essentially why your definition (and classification) of what is ‘effective’ matters so much – to garrison the entire wall with, say, archers, is an impossible task, especially with enough to repel any decent sized invasion force. However, the use of a frontier wall defence was simply so that you could gain information on incoming attacks before they actually reached their destinations.
The threat that the wall was designed to combat was to help combat nomadic raiders/invasion forces from the steppes – if you ask how effective the wall was at meeting this objective, then yes, it was quite effective. The logistics of transporting an entire invasion force over any small section of the wall is ridiculous as it is, but that’s less the point. Your watcher sees an invasion force, lights a fire, and then an entire unbroken chain of signal fires are lit all the way to your capital, where you can then send out an adequate defensive force targeted at the invaders. If you had no wall, and thus no information, you could spend weeks, if not months, trying to catch this elusive invasion force whilst they wreak havoc and you spend a lot of resources to maintain supply lines to your armies.
Coming back to the enemies that China had to the north – Mongols, the Jurchen, and the Manchu – they raided outside of the wall, and to a devastating effect, however getting across the wall was another story. You could also posit the idea that the wall was built to discourage raiders and project the power of China – this wasn’t as effective, since the aforementioned nomadic peoples still did raid and attempt incursions into inner China.
However, yet another form or use of the wall was not to repel invaders so much as to discourage raiders and organised invasions. Construction of the wall allowed local peasants to operate in relative safety, and allowed many of the fertile plains south of the wall to become stable, large agricultural areas.
So, looking at it this way, the wall was effective at repelling the frequent pillagers such as the Xiongnu, Mongols, Jurchen, and Manchu – by extension, due to the result being that China would have had more stable agricultural provinces, it was effective at maintaining relative resource (and economic) stability. Bear in mind that large ‘barbarian’ forces, such as the Mongols under Genghis Khan, were once in a million type occurrences that parted from the norm – the more common threat was from raiders or brigands who forced resources into reconstruction rather than further development.
Now to the Ming dynasty – something like a good millennium and a bit passed since the Han, however the Ming still faced the threat of invasion from northern steppe nomads. Following the previous garrison expansions towards Western China in the 7th century (thanks /u/Jasfss for adding this in), the Ming, being a primarily ethnic Han dynasty and displacing the previous Mongolian Yuan dynasty, sought to take a more active, and thus more effective, approach to dealing with the problem at hand. They extended the wall, and linked existing sections together into a more continuous wall and more closely resembling the one you see today, making it more effective at what it was meant to do – recon and communication.
This, combined with other political strategies and initiatives such as moving the capital north, reinforced the wall’s strength – it only fell to the Manchu due, not to the wall, but to a general known as Wu Sangui, who let them in due to reasons that really require a further explanation. After the Manchus consolidated their forces and established the Qing dynasty, having already had a firm control over the steppe peoples, the wall fell into disuse.
So, to conclude:
The wall was effective in terms of maintaining relative internal economic stability, it was effective in deterring small raiding forces (due to being able to communicate swiftly to the capital the force of any incoming attack), despite on a grand scale being rather militarily limited. Whilst sources are far and many, I'd recommend Fairbank's Cambridge History of China as it really is the only source that deals with precise passages on certain historical events.
