It only becomes unwieldy if you need to actually interact with it. And that is not so uncommon. After all - simply the act of seeing the URL is interacting with it already!
But let's say for example I want to translate a webpage and I need to put the URL into the translation engine. Not everyone is a great copy and paste user so imagine having to type it out (and flip back and forth between the two web pages at the same time).
Maybe someone has printed out a webpage with the URL at the top and given it to me and I want to type it in. This happens - a teacher at my daughter's school gave us a printed sheet of 30 or so online educational resources. (Yes, I suggested she emailed it to us!)
Long URLs usable? Put it this way - they don't improve usability compared to short URLs!
Flickr (yahoo) and amazon are bad at this - I've seen them use URLs well over 600 characters or so!
And I think it's another little lost opportunity to present something memorable to the user. A long URL and the website domain is lost in the mix. A short URL and the domain is more noticeable and might be remembered better. Of course Amazon and Yahoo will arrogantly claim everyone knows their domain so they've nothing to lose by using long URLs...
At the other end of the spectrum - URL shorteners provide too much obscurity and lack the transparency that let you see where you are or where you are going.
There is a happy medium between short URLs and cryptic URL shortening - as the BBC have come to realise. The BBC website has probably millions of webpages and they had a need to reconsider their entire structure and that obviously had an impact on URLS.
As Mike Atherton's talk 'Beyond the Polar Bear' (for which you can See slides on slideshare) mentions, the BBC's domain driven design of the sites content (a massive ongoing task) suggested URLs like 'http://www.bbc.co.uk/bbcone/sherlock/series1/episode1.shtml' but what they actually did was this 'http://www.bbc.co.uk/programmes/b00t8wp0'
In fact I'll quote a bit of what Mike has to say about URLs as it's important here:
URL design is a part of User Experience
URLs. We see them every day on business cards, buses, and boxes. We
share them via email and social media. They are the connecting nodes
that make up the web itself. Yet too often they are overlooked as part
of user experience design. Effective URL design should balance three
main principles: They should be hackable (so that
foo.com/products/gangly_wrench can be chopped back to
foo.com/products) thus serving as a form of orientation and
navigation. They should be human-readable, since this makes them
memorable and easier to promote, and above all they should be
Persistence means your URLs never change. After all, the web is made
of links, and if the URL of a resource changes, it effectively becomes
disconnected from the wider web. Your URL is an implicit contract with
those who have linked to you or bookmarked you, so presenting users
with a 404 is as heinous as changing your telephone number without
telling anyone. But designing for persistence has implications. To
truly future-proof your URLs, you need to remove anything that is
subject to change. Do all your URLs end in .php? Then you’d better not
be planning to move to ASP any time soon. Got something like
‘…/music/artists/Prince’? Good luck managing that when he decides to
change identity again.
When the BBC designed their URL schema for Programme pages, they had
to accommodate the inherent volatility in TV production. One-off
dramas can morph into series. Series routinely hop from one network to
another. Even programme names themselves can change on the long road
from planning to broadcast. It was a tough call for a project aiming
to provide a persistent URL for every BBC programme broadcast, and the
solution was a URL format whose only human-readable component was the
one thing they knew for sure: it was a programme. They opted for
bbc.co.uk/programmes/:pid (a unique alphanumeric ID to identify each
programme). The use of unique identifiers doesn’t exactly make for
human-readable URLs, but you can be damned sure they’re not subject to
Persistence should be supported by pointability; allowing your content
to be referenced uniquely via its URL. Employ a ‘one thing per page’
policy, describing a single topic at a single URL. This strategy has
been instrumental in Wikipedia’s ubiquity, since it encourages linking
to that topic page whenever the topic is referenced elsewhere. If your
documents and media assets have URLs they can be pointed at, otherwise
they can’t. It’s that simple.