I've just spent a whole weekend moving house so I might not be very coherent here, but I'll give it a shot.
I was going to say "yes" to all three questions but now I am not so sure. I think most good sex scenes are erotic but when I think about it, there are some sex scenes which are not necessarily erotic but perhaps are more poignant, sad, whatever. But I do think that a good sex scene has to create some kind of emotional response from the reader (and that may or may not be sexual or erotic) otherwise its just a sex scene. However, I would have to say that most of the sex scenes that stay in my mind ARE erotic. I suppose we do have to define erotic because we may have different conceptions about what that means. Also, an author could use their own sexual response to judge the effectiveness of a sex scene but again, people might have different ideas about what is erotic though I'm sure that there are some very common themes. Also, as Sara has pointed out in earlier entries, there are also other things at stake in a sex scene ... ie. character development etc. I would use some examples right now but am too tired so will just leave it at that.

I'm going to say "no" to all three questions. Here's my thinking:

Erotica is about getting people off. But that's not what we're talking about here. We're talking about a good sex scene defined as "a scene that furthers characterization and plot". By that definition, I don't think there really is such a thing as a good "sex scene". There aren't such things as SEX scenes at all. Just scenes that need to be there and scenes that don't.

So, by way of an example— I've written very few sex scenes, but the one I like best is between a dishwasher and a performance artist in her apartment in Los Angeles. During the scene she uses one of those hoist bars that people in wheelchairs use to get in and out of bed and the protagonist (the dishwasher) gets distracted by the tattoo on her stomach while they're having sex. I don't spend much time on the actual sex—the sex isn't the point. The point is the sex itself tells you things about them (she's melodramatic, he's highly internalized, etc). In an earlier scene the performance artist finishes off a bottle of whiskey while they're drinking on the roof of her building, then throws it at a billboard across the street. He leaves his job with his shift-beer in his hand. Her bathroom's messy and she kicks him out after sex. None of that is there to get the reader hot and bothered, but I think it's a great sex scene.

A more obvious example: consider that great scene in the movie Porky's, when Kim Cattrall starts howling. Good sex scene? One of the best. Sexy? Well. Probably not.

Well, as to the first question...I think about the oft-mentioned sex scene you wrote between Liam Kirby and Jemima Southern, from Lake in the Clouds. When I first read this scene I was shocked at how potent and powerful it was; excellently written and different from, well, if I may so, everything of yours I had read before. It was exciting and vital and visceral and shocking and wrong, and at the same time, not something that particuarly AROUSED me, if we are using that word. But I did find it erotic; I think your second and third questions are right on the money, what's erotic for one person can quite often be not erotic at all to others. A lot of relationships fizzle out due to that disparity!

I think that when you break it down to the simplest terms, however, it matches up with your question of, "What is this sex scene doing for the story?" and if it's well-written, it will be erotic and arousing to some readers who happen to find those particular scenarios the characters find themselves in erotic and arousing. That doesn't really make any sense...hmmm...not enough coffee today...

I think that it's easy to forget how widely varied human sexuality really is. Pretty much every body part, body type, or bodily function can be (and has been) eroticized by SOMEONE. So, "hot" for one person may be tepid or repulsive for someone else. It also depends on the life experience of the person who reads it: when I was twelve, I read a romance novel in which the hero has sex with the heroine while she's a) in a whorehouse (by accident!) and b) so high on laudanum that she's barely functional (again, by accident). Nowadays, I'd consider that icky date rape, but at the time it made sense and I found it romantic. So, "hotness" is way too subjective to be used as a standard of judgment. I hold that character development is absolutely key to any successful sex scene. My favorite sex scene, to this day, is in Joan D. Vinge's novel "The Summer Queen." It's pretty vanilla and vaguely worded, but the context makes all the difference: it comes after 600 pages of yearning, yearning, I-know-not-when-I-will-see-you-again-but-my-love-for-you-is-eternal stuff, and when the two characters actually consummate their relationship after all that pain, it's revelatory, and, yes, hot.

lunadatura -- I agree that the topic is the ultimate in subjective, and that we should keep that in mind.

On the other hand, sometimes a writer just fails to get across what s/he is trying to accomplish in a sex scene, and it might be useful to talk about how the failure came about, and might be turned around. Or so it seems to me.