Q

Pros and cons of ALL_ROWS optimizer

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

This Content Component encountered an error
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.

This was first published in May 2007

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close