When your water is on the warmer side, it will hold less oxygen. The fish and the beneficial bacteria both need oxygen to survive and thrive. It can be essential to have extra aeration if your tank or setup gets warm - 78F or higher.
If you have a fiter that has a spray bar or overflow that splashes a good deal, stirring and agitating the water, it most likely will introduce enough air into the water that all will be good. In a tank with a lot of surface area, it will be fine, but a tall, skinny tank with a small top may not be so good. In these cases, the extra air will be crucial.
Water takes in the oxygen through contact. The larger area of water that contacts the largest area of oxygen means maximum air content in the water. Loads of bubbles all have a lot of surface area to impart oxygen to the water. And the main reason for the bubbles is that they lift and move the water from what could be a stagnant part of the bottom of the tank to the surface where it can pick up still more oxygen.
Your nitrogen cycle uses oxygen as the bacteria process the ammonia into nitrates. Oxygen is a crucial ingrediant in each step of this cycle. Without sufficiant oxygen, the cycle may not be as efficiant. Most filters move water sufficiantly to the surface and back again to assure there is sufficiant oxygen in the water for the cycle, but more never hurts.
I like to place an air feature of one nature or another under the heater in a tank. This makes sure that the heated water is moved up and away from the heater and circulated through the tank more efficiently, elliminating warm or cold pockets of water. Air features can be used under hollow ornaments to eliminate stagnant pockets of water in them where toxic water could potentially develop. The fish also seem to love to play in the bubbles - they ride them to the top and swim through them - I think it feels like a whirlpool to them - massaging and tickling them.
Yes - you can get too much aeration - if every wall and area has bubbles rising. Fish do need an area where they can just float in equilibrium and sleep - I call it a "sweet spot" where they can relax and sleep. As long as you have a place like that for the fish, your tank is fine.
I leave my aeration on at all times. The fish do not seem to mind it - in fact it is simply a fact of their environment. I think my ears would be the only thing that would be bothered, but then I do not sleep in my fish room. Some air pumps are quieter than others - and I have found that if you get an over powered one - one that is far larger or stronger than needed, and then turn it down, they tend to get a lot quieter.