The film "Shortbus" is pretty much all about sex and people's sexuality in one way or another, so the sex scenes are indeed meaningful. There are other films where, similarly, these things help with plot, character or mood.
On the other hand, sex in other movies is just mostly pointless, or is used to crudely make a point better made in another way. Most Hollywood films with sex fall into this category, though honestly, I don't see many with actual sex - just fade to black moments. Except in comedies, when you see someone hilariously interrupted during sex; also pointless.
In fact, come to think of it, comedies are probably king of pointless sex scenes.