When to use Oracle hints?

I'm doing some refactoring on a Oracle Schema (oracle version 10), and I see a lot of views that use hints *+ALL_ROWS*/. In others views there are also other kind of hints. Why I should use an hints? the DB doesn't make the best choice in base of the query? many thanks!

That's a good question, but there's no single answer to it because there are different categories of hint for which different advice would apply. http://docs.oracle.com/cd/E11882_01/server.112/e16638/hintsref.htm#PFGRF501

ALL_ROWS is an optimisation approach, and it's perfectly valid to specify it in order to make it clear that your goal is to get the last row of the result set as early as possible, not the first. In many cases the optimiser will deduce that from the query anyway, so it may be redundant, but you're not going to harm anything by using it correctly.

Then there are different categories, some of which might be characterised as being for testing and exploration, such as the optimizer_features_enable. Arguably the hints that affect join order, access path, and join operations are of this type as they're sometimes discouraged for use in applications. However the optimizer is not perfect, and does not have perfect information, and sometimes it will make a choice based on incomplete information that needs to be corrected.

Some hints are unquestionably useful and appropriate -- APPEND is possibly the best example, as it is the standard method for invoking direct path insert.

In the end it's really difficult to give generalised advice on this. Really every hint needs to be addressed in respect of whether it should be used in production code or not, but if you understand the optimiser and understand what the hints you are considering really do and whether there are better alternatives -- eg better statistics, different init parameters, or dynamic sampling (itself a hint) -- the you'll be able to make your own assessment and defend it if challenged.