If Mario makes contact with it, he will start running around with his backside being on fire. This will make Mario nearly uncontrollable and quickly drain about half of his health. Mario will also only be able to make tiny hops while being on fire.
As a Stage Hazard/Locations
While fire can mainly be found in the lower floors of Peach's Castle as a wall decoration mounted to solid stone walls, this decoration will actually hurt Mario if he comes in contact with it. (mainly via jumping and ending up in the fire by sheer accident or when chasing MIPS the rabbit)
As a Projectile Attack/Enemies
But as mentioned in the top entry, fire does also serve as a projectile attack to some enemies.
The most notable of them is probably none other than the King of the Koopas' himself. When Mario fights him, Bowser can occasionally be seen taking a deep breath, which then goes to him breathing fire in Mario's general direction as he breathes out.
While the initial Bowser battle only has the aforementioned version of Bowser using fire against Mario, the second and third battles also have him leaving small flames behind after breathing fire at Mario or even enable him to let it rain flames in the third battle.
But besides Bowser using his fiery breath, there are also black orbs scattered around in various levels of the game.
These are known as Kuromame (aka "Black Bean") in the Japanese version of Super Mario 64, but don't have a name in the international versions.
The Kuromame act as fire dispensers. They inflate and shrink when Mario draws too close, releasing a single flame that follows Mario over a short distance.
Mario can't destroy or stop these fire dispensers, though they need a small timeframe to recharge themselves before releasing another flame, allowing Mario to safely leave their area and advance through the current level.
Other Characters and Objects using Fire