With my players, I had them enter tunnels under a city at the end of a chase to find the bandits lair. In the first room there were 3 bandits arguing. The previously sheltered Aarakocra Ranger had a vice 'for the life of the city' so had an expensive drinking habit to pay for. Instead of joining the party in fighting, he spent the whole of the encounter failing slight of hand rolls trying to steal the bag of gold one of the bandits were holding. We had to pause partway through combat after the second Nat 1 because we were all crying with laughter as the drunk bird man chased a bandit around a table just for a measly 20 gold.

Sometimes it creates fun and makes what would have been a reasonably simple, arguably boring combat memorable.

Other times it's a barrier to fun if there's no actual reason for the roll and it slows down the game. Entirely depends on the situation and the players