When I became the cubmaster of the pack, I was introduced to the UC. Our pack was strong, had great family involvement, strong Den leaders, and although I was coming off a 15+ year hiatus with Scouting, I was an Eagle, Camp Program Director, etc.
The new role was daunting, but it was a comfort to know there was someone I could go to if I needed to, as it was I didn't really; but it was nice when he would follow up with me.
As stupid as it may sound, there was a comfort in knowing that the UC was completely outside of the pack structure. I would have a resource I could touch without feeling like I was letting the parents who trusted me with the leadership role down by not already knowing.
I have since taken the commissioner training, although I do not serve as a UC; I am a Roundtable Commissioner.
The first and most important think to note about the UC is that they do not have ANY authority over the unit. They are not a Leader - as David rightly points out, the UC is not a leader selected by the CO, because they are not a leader at all. They are not there to TELL leaders of the unit how to do things, they are available to answer questions about things if asked, and maybe suggest if appropriate.
Are they a spy - this may be semantics. They would not consider themselves to be a spy, why would anyone volunteer for that job. But they are a feedback mechanism to let the council determine if the Scouting program is effectively (or "correctly") being applied by the unit. No franchising organization would operate without the feedback, it would be too easy for the franchisee to damage the reputation of the entire organization without it. But mostly, they see it, as has been described above, to be a way to help the unit be better.
Some UCs are a little more aggressive in providing advice and guidance than they should be, but with the very rare exception (and those should be weeded out), they are there to help the unit be better. If the UC is too aggressive, then the unit leadership should contact the district commissioner or the district executive (as most won't know who the district commissioner is) about changing the UC, and the DC can decide if the old UC should continue in that role or not.
Are they a toady? Again, they probably do not see that as their job, but they are more likely, because they already know the unit, to be the one trying to promote friends of Scouting or other Council/District functions. If I were the UC, I would try to decline some of this, but I would also try to make sure that the unit was at least aware of functions - particularly if they were not in regular round table attendance.
I expect that most UCs actually hate the tracking tools and metrics - they would probably just rather say high, and ask if there is anything the unit want's help with. But I think they also understand the value of being able to actually track, somewhat objectively how things are going - before a unit either from membership, finances, politics, or otherwise goes belly up by surprise.
Are the UCs actually needed? This is a very circumstance specific answer. For a unit with good support and leadership, they probably do not really see a need or value in the UC and get by very well without one. Just like some other units can get by with almost no active involvement from their CO. However, many more units, particularly in Cub Scouts with the shorter tenure of leaders and rapid turnover as a result; a good UC can really be that crutch that helps new leadership accomplish more of their goals.
I expect that beyond personalities, the other area where UCs and units get off on the wrong foot, and may be part of the perception of aforementioned spying, is that as BSA policies change, without a very BSA active CO, the UCs are in the best position to make a unit aware of the change. Rightly or wrongly, the unit leadership takes this as being told what to do/or not do and does not like it. "We've been doing it this way for 30 years and we're not changing now - now go and tattle on us to Council".