As a minor counterpoint the the existing answers, it is also possible for realistic ammo to ruin a scene. The audience is willing to assume that certain events that don't impact the narrative occurs offstage. As a classic example, the eating habits of the characters of Middle Earth are explained in great detail, but no one ever needs to relieve themselves afterwards. It's something that we all recognize probably need to happen, but including this aspect of realism would only detract from our engagement. The audience is often willing to believe that a new clip was loaded while we were paying attention to something more important.
My other example contains spoilers for the 1985 film Alien Outlaw. Consider yourself warned.
At the climax of the movie, the sharpshooting heroine is fighting against the last remaining alien. The directory takes great pains to inform the audience that she is down to three bullets. She can't shoot at the alien, because she might miss, and she only has three bullets. She needs to move to a better vantage point, because she only has three bullets. 900
Of course, having reached this vantage point, it would be unsatisfying for her to shoot the alien and say "I guess I have some extra ammo left over", so the first shot misses. Nothing has changed about our protagonist's situation, but the film believed that the tension has ramped up, because she now has two bullets, which is an even smaller number than three. Having discovered this tension building device, the movie immediately pulls it again with another failed shot, leaving her with one bullet. Unsurprisingly, this final bullet succeeds where the other two failed and kills the alien. If this paragraph was painful to read, I assure you that the scene was more painful to watch.
Now, I'm not saying that limited ammo can't create tension. Plenty of examples have been given about how this can work. I'm just providing a reminder that limited ammo doesn't automatically create tension. When every bullet matters, the limits are tense. When only the final bullet matters, the limits are an accounting exercise. 900