Would this look weird if it’s used just as a statue, not a fountain? Or is the water hole (ahem, sorry) kind of huge and obvious?

Where the water comes out is discretely pointed at a downwards angle. So it just depends on how high you place the statue. Mine is at ground level. If you had it at eye level, you'd definitely think there's something not right with that boy.

can i use this by a pool with chlorinated water?

Yes, as long as the pump is submerged in the pool water to recirculate. The pump kit is sold separately and can be found here: https://www.amazon.com/Design-Toscano-UL-listed-indoor-outdoor/dp/B00UIAD4U0/ref=sr_1_1?ie=UTF8&qid=1528481685&sr=8-1&keywords=dr290+pump

I plan to use this in a fish pond. Will it survive with the base being partially submerged?

Without being properly fastened chances are it will fall over. We have had ours submerged and mounted with stainless nuts and bolts to a rather large piece of slate it is submerged about 4 in. And has had zero problems as of now. Beware prior to purchasing this is much smaller than it looks and you will have issues with it leaking water inside where it goes out his penis I have tried to address this and the only fix I could find was put it in the pond so I'm not losing any water.