Please note that I'm answering primarily from a practical perspective here; as per the question:
More specifically focusing on here, not the after life.
I am answering primarily about reality in this world, not Biblically.
First, we must define "sexual sin"; I'm first going to assume we mean fairly "light" things, such as masturbation, consensual sex of unmarried persons, contraception, same-sex sex, sex acts between a couple that can't lead to procreation, etc.
Well, here the expected results are pretty much... minimal. I would be making assumptions in here such as safe-sex to avoid unwanted physical effects such as STDs (if promiscuous), or an undesired pregnancy. You aren't going to be struck blind, get hairy hands, or outright die from masturbation. The most "immoral" cities are not smited (smote?). The most likely outcomes are:
- an increase in adrenaline, dopamine and serotonin - "feel good" hormones
- emotional closeness; for example, I "lived in sin" with my now-wife for many years before we got married; we've been together nearly 20 years now, but many of those years would have been judged "sinful". This was part of our relationship, and was very emotionally valid
- as an aside, we no longer live in small villages, where marriage options are limited; likewise, human personalities are deeply complex. I could not find it in me to recommend any couple get married without living together first, to see that they are compatible. There's a difference between seeing someone a few times a week for a few hours on best behaviour, and living with someone full time, warts and all.
- equally, I see marriage as a partnership of equals, not a master/subordinate thing; this change (gradually over the last 50? 60? years) has had a significant impact on changing the dynamic of relationships, much for the better in my opinion - but with two strong individuals there is more chance of friction (or more correctly: one partner, typically the woman, isn't expected to silently "put up and shut up")
- realisation of self, for those that cannot find attraction/happiness in a heterosexual relationship - a stable homosexual relationship can be deeply rewarding and fulfilling, the same as a stable heterosexual relationship
Moving on, we get into areas such as pornography and adultery (just as example); the first is deeply complex, and frankly I'm not qualified to comment - the are, of both genders, proponents and opponents, people who say it enslaves and people (including women) who say it liberates. Those that say it harms society and objectifies the participants, and those who say the opposite. I'm not going to wade into that, but: access to pornography (in an Internet age) is at an all-time high, but sexual crime has not increased to match (indeed, is generally reported as falling). I make no analysis of that, due to lack of qualification.
Adultery, these days, is not likely to get you stoned to death in the village square. In the majority of cases this is done as a secret and clandestine thing, which can (when discovered) negatively impact (or destroy) a relationship, and otherwise is likely to (as a side-effect) cause a combination of excitement (adrenalin etc), but other emotions too (guilt, furtiveness, stress). In some more.... "open" relationships, it is not a betrayal of trust, obviously. I'll let people decide on what their own relationship is.
I'm not going to go into sexual crime as sin, as the effects in this world are well known.
Does it make the person feel emotionally oppressed on some level or does it just separate them from God spiritually without any effect here?
For the former; quite the opposite. Recognising one's own sexuality is liberating. It is the denial of sexuality that is oppression. Feeling guilt at masturbation is infantile; feeling shame at recognising (say) same-sex attraction causes harm, both in loss of happiness, and (quite often) harm to others when those feelings don't go away, and lead, quite often, to a "need" to pursue those inner drives, but often in more secretive ways, which is harmful psychologically, and even more harmful if it becomes public knowledge later. We all know of the many "upsets" caused when even some of the most vocal anti-homosexual voices have been exposed in secret homosexual liaisons. This demonstrably points to massive cognitive dissonance, guilt, shame, repression, etc. Not healthy. And equally, there are some very religious and good people who would be classified as sexual sinners because they acknowledge the reality of their feelings.
For the latter (separation from God), I am not well-placed to comment.
Sex, in a lot of animals (especially primates), not just man, is a lot more complex than purely procreation.
tl;dr; no evidence of Biblical style punishment in this world is evident (nobody struck down, no cities destroyed). However, like must things in life, if we lie and cheat (etc) that can harm our relationships.
