Optimizer 的选择

1.2.4 Which Optimizer to Use?

If you are currently using the rule-based optimizer, I strongly recommend that you transfer to the cost-based optimizer. Here is a list of the reasons why:

• The time spent coding is reduced.

• Coders do not need to be aware of the rules.

• There are more features, and far more tuning tools, available for the cost-based optimizer.

• The chances of third-party packages performing well has been improved considerably. Many third-party packages are written to run on DB2, Informix, and SQL*Server, as well as on Oracle. The code has not been written to suit the rule-based optimizer; it has been written in a generic fashion.

• End users can develop tuned code without having to learn a large set of optimizer rules.

• The cost-based optimizer has improved dramatically from one version of Oracle to the next. Development of the rule-based optimizer is stalled.

• There is less risk from adding new indexes.

    • There are many features that are available only with the cost-based optimizer. These features include recognition of 

     Materialized views, star transformation, the use of function indexes, and so on. The number of such features is huge, and as

     time goes on, the gap between cost and rule will widen.

    

      • Oracle has introduced features such as the DBMS_STATS package and outlines to get around known problems with the inconsistency of the cost-based optimizer across environments.

 

    选择使用那个优化器

       如果你当前使用RBO,我强烈推荐你赶快换成 CBO. 在这里有为什么要换的原因:

• 花费在Coding 的时间减少
• 编码者不要记住那么多规则
• 很多优化工具都是为CBO 提供的
•   第三方的优化包在提高性能上起到相当的作用,许多第三方工具包在DB2,informix,Sql server 上 和在Oracle 上一样好.这些代码都不是用 RBO 写的,他们都是采用通用方式.
• 终端用户需要优化Code 不需要学习很多的优化规则
• CBO 被Oracle从一个版本到另一个版本一直沿用下来,而对RBO 的支持已经停止.
• 对增加新的Indexes 减少风险.
•  
• 对CBO 提供了很多特性.包括Materialized views,star transformation,使用Function Indexes等等.由于这些新的特性的提供,随着时间的推移,Rule 和Cost的差距会越来越大.
•  Oracle 已经提出如 dbms_stats 包和概要为了得到与CBO 访问环境的之间的矛盾.

 

1.3 Rule-Based Optimizer Problems and Solutions

The rule-based optimizer provides a good deal of scope for tuning. Because its behavior is predictable, and governed by the 20 condition rankings presented earlier in Table 1-1, we are easily able to manipulate its choices.

I have been tracking the types of problems that occur with both optimizers as well as the best way of fixing the problems. The major causes of poor rule-based optimizer performance are shown in Table 1-2.

Table 1-2. Common rule-based optimizer problems
Problem	% Cases
1. Incorrect driving table	40%
2. Incorrect index	40%
3. Incorrect driving index	10%
4. Using the ORDER BY index and not the WHERE index	10%

 

Each problem, along with its solution, is explained in detail in the following sections.

1.3.1 Problem 1: Incorrect Driving Table

If the table driving a join is not optimal, there can be a significant increase in the amount of time required to execute a query. Earlier, in Section 1.2.1.6, I discussed what decides the driving table. Consider the following example, which illustrates the potential difference in runtimes:

In this example, if ACCT_NAME represents a unique key index and COST_CENTER represents a single column non-unique index, the unique key index would make the ACCT table the driving table.

 

1.3.1 Problem 1: Incorrect Driving Table

If the table driving a join is not optimal, there can be a significant increase in the amount of time required to execute a query. Earlier, in Section 1.2.1.6, I discussed what decides the driving table. Consider the following example, which illustrates the potential difference in runtimes:

 

如例: 如果acct_name 代表一个唯一index 并且 cost_center 代表一个Column 的非唯一index.

所以唯一 index 使 table acct 为 driving table.

 

If both COST_CENTER and ACCT_NAME were single column, non-unique indexes, the rule-based optimizer would select the TRANS table as the driving table, because it is listed last in the FROM clause. Having the TRANS table as the driving table would likely mean a longer response time for a query, because there is usually only one ACCT row for a selected account name but there are likely to be many transactions for a given cost center.

 

如果cost_center 和 acct_name 都是一个Column 的非唯一index,基于RBO 就会选择Trans table 作为Driving table (即最右边的table 最为驱动table).因为Table Trans 排在from 子句的最后面.

将Trans 作为driving table 的响应时间较长,是因为只有一行不同acct_name的值 在Acct table 中,但是在给出的 Cost_center 条件下在Transactions table 中有多笔记录.

 

 

With the rule-based optimizer, if the index rankings are identical for both tables, Oracle simply executes the statement in the order in which the tables are parsed. Because the parser processes table names from right to left, the table name that is specified last (e.g., DEPT in the example above) is actually the first table processed (the driving table).

 基于RBO,如果Index 的范围在所有table 中一致.oracle 将简单以一个解析过的table的顺序执行SQL 语句.因为解析table的顺序是从右到左,某个table 在From的的最后位置将被第一个解析(作为driving table).

 

SELECT COUNT(*)

FROM acct a, trans b

WHERE b.cost_center = 'MASS' AND

a.acct_name = 'MGA' AND a.acct_name = b.acct_name;

Response = 19.722 seconds

 

The response time following the re-ordering of the tables in the FROM clause is as follows:

 

如果重新对table排序,如下:

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/34596/viewspace-799583/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/34596/viewspace-799583/