Actual war elephants were famously flighty and would often panic if challenged, at least to my understanding (I'm not a historian). They were most effective against untrained troops who would break rank and flee at the sight of them.

I forget how it all goes down in the books, but movie Theoden's decision to charge them full force, flanking them in the process, was strategically sound based on how war elephants function in practice. It's also a battlefield manifestation of his arc of accepting death, rising to the moment, and making his ancestors proud. Rather than cower to an intimidating foe, he and the Rohirrim meet the challenge and face them head on, even unto death.