Shower curtain rod is very heavy

You need to buy some shower curtain brackets. Those can be screwed into the wall and they hold the ends of you showere curtain.

Could you nail a strip of wood under each rod and set the rod on it? Paint it to match your decor. If not I would suggest liquid nails, not Elmer's, to glue it up. The rod should have some kind of bracket with holes for screws.

Attach some sort of bracket to the wall and place the shower curtain rod into that. Should solve the problem.

I was going to give Alice’s answer. With brackets, you can easily remove the shower bar if you need to. Be sure to measure the height for a nice shower carts in. You can get to the shower curtain to wash it. Also, install a smaller pair of brackets to hold a smaller shower bar for the curtain liner inside. Liner inside, curtain outside. One more tip: If you don’t want to drill into the tile, you can use waterproof Gorilla Glue. But you MUST let the glue and brackets cure for at least 24 hours before you hang the curtain, liner and bars. I would love to see photos of your work, starting with “before”, posted on Hometalk. Best wishes ☺️

You could add a brace under the ends of the curtain rod to help support them, like wood painted or metal supports. Just an idea.

If you want to continue using your tension rod I would suggest you take it down and scrub the vinyl caps on the end with a piece of rough sand paper. Some vinyls and plastics get brittle with age and roughing it up will expose some new material that may have more friction against your walls. Perhaps you could increase the tension while you have it down. I would replace the rod before using glue.

Don't glue you will have a mess. Install a rod instead of using a tension rod.