Personally, I am very alert throughout the day if I sleep between 6-7 hours. Past the 7 hours threshold I am get into a worse headspace (brainfog) throughout the day proportionally to the amount of extera sleep.

Sleeping for longer than 8 hours frequently gives me hypnic headaches and terrible migraines.

I recently had a baby and the lack of sleep has reduced my headache frequency dramatically!

Different processes happen during different sleep stages. Typically people go from light sleep to deeper sleep stages (slow wave), to REM in a cyclical pattern. We go through each of the stages roughly every 90 minutes of "quality sleep". There's usually more deep sleep at the start of the night, and more REM towards the end of the night.

If you end up with too much deep sleep (e.g. after being very physically exhausted for a while), you will have little or no REM sleep and your sleep quality will suffer.

It's not just "too much deep sleep" that could fuck things up, being woken up multiple times can also mess up your sleep cycle, whether it's by crying children or sleep apnea.