What situations does it work the best, and how?
Depends on the type chemistry of the specific fluid because there are some significant differences between brands ( someone like Mola would be better suited to into those details than me)- I can speak on the testing side because in my years teaching and certifying NIMS, NCCER , Red Seal and others this subject comes up a lot and many vendors have interesting tests and rigs set up to illustrate these things when it comes to fasteners and various chemicals.
In gross general terms- they basically have some form of lubricant/displacement agent, some form of solvent and maybe some additives of various things depending on the brand.
They all work as advertised in terms of lubricating and cleaning ( to the level of the recipe- some lube more than clean, others clean more etc.)
Problem is, they have to physically get in there to work and that's the problem. They all must have a gap and that gap must allow the fluid to enter while any trapped air or other substance can get out. ( that's 2 different scenarios because if sludge is in there- the thinner lube has a hard time displacing it unless it can literally inject or percolate)
Start with the fit class ( see Machinery's Handbook)- basic threads are around 75% ( leaving a 25% gap)- it takes a good bit to clog them up so that's where it works best. The lower the class- the lesser the gap ( and the easier to clog it)
Then theres the run ( length/count of threads engaged)- if theres a mechanical defect, do the best you can because no unicorn oil will negate that. Over the run- the fluid has to meander and work down to the base.
The insurmountable issue comes when either the crud is so packed/condensed that its hard as cement or you have damaged/closed geometry creating a seal like a valve plate.
As said, if the chemical cant get in there- it cant do anything. If it can get in there- they will all make a significant difference.
At the end of the day, a human has to evaluate the joint scenario and pick the best course of action based on the specifics of that individual case.
Flash heating and cooling can break/crack stuff creating a path and a thermal capillary action like solder, mechanical tapping can ( not to the level of damaging thread or other components), mechanical working back and forth can too. ( or any combination)