The notorious archive of Linus rants on [0] starts with "The thing that has always disturbed me about O_DIRECT is that the whole interface is just stupid, and was probably designed by a deranged monkey on some serious mind-controlling substances". It gets better afterwards, though I'm not clear whether his articulated vision is implemented yet.
As a database example, there are major classes of optimization that require perfect visibility into the state of the entire page cache with virtually no overhead and strict control over every change of state that occurs. O_DIRECT allows you to achieve this. The optimizations are predicated on the impossibility of an external process modifying state. It requires perfect control of the schedule which is invalidated if the kernel borrows part of the page cache. Whether or not the kernel asks nicely doesn't matter, it breaks a design invariant.
The Linus rant is from a long time ago. Given the existence of things like io_uring which explicitly enables this type of behavior almost to the point of encouraging it, Linus may understand the use cases better now.