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.
Dig Deeper on Oracle and SQL
Related Q&A from Phillip Bracken
A user complains that every time he tries to access a table (select only), it takes more than two hours to get the results. There are no DML ... Continue Reading
I have a table with 1.5 million records that needs to have a column updated based on a correlated subselect. This update is currently sitting at four... Continue Reading
I have always used TKPROF to do SQL tuning in previous versions of Oracle (7,8 and 9). Can I still use TKPROF in Oracle 10g R2? Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.