In a metal situation I could totally see doing a biamp rig with some distortion on the highs.
None of my own bands have been metal, though, and I never got hired to do any real metal as a bassist.
For most rock, I like a moderate amount of tube amp OD. I used SVTs for years and in a loud band their drive tone was great.
One of the best bass tones I ever got was using my old Boogie Mk II and a solid state Sunn power amp running off the slave out. I'd push one speaker with the Boogie and the other with the big solid state amp. This was in a three-piece band; I could kick on the lead channel for extra drive to fill more space when the power chords dropped out, and once in a while to emphasize a bass line I wanted to stand out. Lead channel in the Mk II has its own bright switch which added some extra presence when it was on.
This was back in the late 80s when every soundman wanted a direct feed straight off the bass. Sometimes had to push to get them to mic my cab. Luckily, since there was only one guitar, there was always an extra mic onstage - just a matter of convincing them to use it. A number of those same guys came up afterwards to ask, "HOW are you getting that sound?!"
Later, when I was working as a hired gun, sometimes I didn't even know what the bass amp would be. Eventually started using a Sansamp programmable bass driver.It has three presets, OD with a clean balance knob, and a Cannon out to feed the PA. With that I could pretty much take my own tone anywhere. It sounded good (though I occasionally wished the programmable EQ included a midrange control), fit in the pocket of a gigbag, and was utterly reliable for more than a decade.
As pointed out upthread, though, context is everything.
There have been plenty of situations where I didn't want any drive at all.
But for most rock, tube style overdrive really does it for me.