Problem solve Get help with specific problems with your technologies, process and projects.

Pros and cons of ALL_ROWS optimizer

What are the pluses and minuses of going back to the tried and true ALL_ROWS optimizer?

How do you know the default 10g optimizer ALL_ROWS is for you? The rule-based optimizer is gone -- boo hoo. She served me well in the past 20 years. Sometimes I needed her. Oracle Applications makes you choose hidden parameters mandatorily to change back to the cost-based optimizer from ALL_ROWS (reference Oracle Metalink Note:216205.1). What are the pluses and minuses of going back to the tried and true ALL_ROWS optimizer?

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

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.