A good sex scene is like a good fight scene. It can be exciting, thrilling, arousing, and all that, but it's also showing you something about the characters involved. There's a back-and-forth that communicates who these people are and how they relate to one another.
Sure, there are perfectly fun fight or sex scenes that don't really communicate much about the characters, but when I think about the best ones in movies or fiction, the ones I remember long afterward, there's a pattern in which ones I remember best.
I think that's part of why the first Matrix movie feels more satisfying to rewatch than the second or third. Every fight scene feels like a key point in Neo's character arc. The sequels have a lot more action scenes that are just cool fights. Ip Man feels really good in that regard too.
I'd like to offer examples of similarly recognizable sex scenes, but our whole cultural hang-ups about sex vs violence make that kind of impossible. As a consequence, almost all my favorite sex scenes are limited to online fics that have been read by up to hundreds of people.