The ALL_ROWS optimizer mode uses a cost-based approach to determine the best access path to access your tables. It does this by calculating the estimated cost of all possible access path options and then using the access path that has the lowest estimated cost in terms of resources to get all of your data. In the majority of cases, this is the best option if you are analyzing your tables and indexes on a regular basis. Yes, I agree it may not work well for all queries or SQL. For some queries, another optimizer mode might be better. In those situations, you can always add a hint to your SQL to use that mode instead of the default ALL_ROWS.
Unfortunately, I am not that familiar with Oracle Applications and choosing which mode to use requires a great deal of application knowledge in how an application works. Therefore, I would always go with what Oracle recommends and not changing it before speaking with Oracle first.
I would also recommend that you read Metalink document 35934.1: Cost Based Optimizer -- Common Misconceptions and Issues. There is a lot of useful information in this document about the CBO. Hope this helps.
This was first published in May 2007