How effective is it to show ratings?

I mean it, ratings can either intimidate and opponent, or make an opponent underestimate you, so is it worth looking at during tournaments?

it's effective in choosing an adequate opponent.

Well, if you are 1000 points higher rated than your opponent, but he is playing well, you might show a little contempt factor and intentionally accept a disadvantage if the best line is a draw.

You get the most evenly matched opponents. You will have to play all of your tournament games, so it doesn't matter.

but then there's the open section where you play unrated  people, that's the most intimidating to me

There is always the old saying: "play the position not the opponent" that will take your mind off the player's rating.