A lot of the places that sound normal are.. not. lol
You get campers, people who do nothing but fake dancing (clubs are like.. nuts), etc. A lot of what could exist in hampered by Linden making shit too expensive for most people to run their own sims, that.. and the near total lack of real "game mechanics". I mean, sure, its not an FPS, but then, neither was things like Uru, but.. those games are made so you have clean ways to animate things like climbing. At best, you can sort of fake it, kind of. But, if you want a world to be a world, you need to be able to do these things in a way that is sensible, not as a hack job.
So, you get hunts, like Twisted, where you end up seeing some really nicely designed things, and.. maybe don't get ignored so much, but.. that one isn't still too social. Or, you look at the other one - MADPea. Now, that one you do get people working with you to solve, if you want to, and, its got a story to it. Or, you go to an RP sim, but.. they are expecting you to actually RP, not just wander around. To be in theme. To know, or at least try to know, what you are doing. My own place, when I can get on for that is City of Lost Angels. But, sometimes its a wash, because the group I RP as a member of doesn't have a lot of people on when I am able to get online. I haven't fed much into the RP with other factions. And, I don't do the, as you say, slightly creepy sex stuff that is sometimes, indeed, part of this all adult chain of sims.
But, yeah, if all you do is wander around, you won't find much. So much died when the prices went up, but the ability of people to pay for it went down. To find anything decent, any more, you need to be "in" one of the places where that stuff happens, or in a group that does those things. Maybe, showing up at the online burning man festival, or some of the highlight places Linden points to, when they first open, and people are really looking at them, instead of later, when the people that bother with them have already seen it, and done that.
SL could still be great. Some people design incredible things, even the ones that work for Linden themselves. But, so much of it is just people that have no other place to be, no idea what they want to do, etc. Because, those people have the time, and the money, to waste standing around virtually, in virtual clothing, doing absolutely nothing.
And, there is far, far, far, too much of that BS in SL now. One hopes they fix, in SL 2.0, some of the pain in the butt problems, like the lack of a clean animation system, which can let you do what real games do, if you need to. And, other things that are missing. Right now.. it can't decide if it wants to be a shop, but then, why does anyone need a shop, without things to do with what they bought, on a RP environment, except, its missing things that would make that easier. And so on. When its good, its amazing, but.. so, so, so, much of it is... like walking down a street, in the slums, in the real world, and completely missing the obscure art shop, one block over. :p