I love the sensuality of the sex scenes in Fire. It's about two women who fall in love and make choices to better their lives, despite the confines of traditional living. It's an absolutely amazing movie that I pimp out to everybody, but the surprisingly hot, heart-felt sex scenes are the icing on the cake.
Parts of Fatal Attraction were hot in a crazy, fucked up sort of way.
The "sex scenes" in Bent were so heartbreaking. Two homosexual men in a labor camp during the Holocaust fall in love and obviously can't even look at each other, let alone touch, but they have verbal sex that is surprisingly hot and not at all cheesy. Clive Owen in this movie? Awesome.
As awful as it sounds, the part in Schindler's List where Schindler is really going at it with a girlfriend right before he's informed that Stern has been taken on a train is...well, it's my favorite movie, so that absolves me of any mental hard-on I got from that scene, I think.
The lesbian love scene in But I'm A Cheerleader! is GORGEOUS. No nudity or explicit content, but it still gets the point across, and it's oh so beautiful.
The second tent scene in Brokeback Mountain. All of that hesitation, gentle guidance, and tenderness. GUH. We miss you, Heath Ledger. =(