The idea of a famous "secret agent" has perplexed people since at least the 1920s. Obviously the most famous example would be James Bond, who isd as far from the mousy, undistinguished unnoticeable real spy as you could get.
I suppose it might be better to think of Bond (and Diana Prince here) as "counter-spies rather than spies as such. Instead of spending months or years eavesdropping or opening safes to get information, they mostly operate against other agents.