It's really hit or miss. I don't know if the computer calculates a percentage chance each time a fire arrow hits it or what not, but if so the odds have to be way over 1 in a 100. If you really need to take out siege equipment, and you're in the gunpowder age, go for mortars.

It's still not a waste to use fire arrows on the walls though. Supposing you have two units of archers, you might not cause tons of casualties with your arrows, but you will indeed cause huge morale penalties, which helps a ton when you mob the units trying to shove through your gates.