优化模式区别(all_rows & first_rows_n)

FIRST_ROWS优化模式以最快速度地检索出结果 集中的一行为其指导目标。当系统用户正在使用OLTP系统检索单条记录时,该 优化模式最为有效。但是该模式对于批处理密集型(batch)作业环境来说并不是最理想 的选择,在这种环境中一个查询通常需要检索许多行。FIRST_ROWS提示 一般会强制使用某些索引,而在默认环境(ALL_ROWS)中可能不采用这些索引。在使 用UPDATE和DELETE语句时FIRST_ROWS模式会被忽略,因这些DML操 作中所查询到的所有记录都会被更新或删除。另当使用以下分组语句(如GROUP BY,DISTINCT,INTERSECT,MINUS和UNION)时FIRST_ROWS模式均被ALL_ROWS模式取代,因为这些语句进行分组时必须检索所有行。当语句中有ORDER BY子句时,如果索引扫描可以进行实际的排序工作,则优化器将避免额外的排 序。当索引扫描可用并且索引处于内部表(inner table)时,优化器将更倾向于NESTED LOOPS即嵌套循环而非SORT MERGE排 序连接。

另10g中现有的FIRST_ROWS模式的变体FIRST_ROWS_N来 指定以多少行数最快返回。这个值介于10~1000之间,这个使用FIRST_ROWS_N的新方法是完全基于成本的方法,它对于N的取值较敏感,若N甚小,优化器就会产生包 括嵌套循环以及索引查找的计划。如果N值较大,优化器也可能生成由散列连接和全表扫描组 成的计划(类似于ALL_ROWS)。 又FIRST_ROW与FIRST_ROWS_N存 在不同,FIRST_ROW模式中保量了部分基于规则的代码,而FIRST_ROWS_N模式则是完完全全基于统计信息计算相应成本,如Oracle文档所述:

ALL_ROWS优化模式指导查询以最快速度检索出所 有行(最佳吞吐量)。当系统用户 处于需要大量批处理报告的环境中,该模式较理想。

在实际的SQL硬解析过程中,FIRST_ROWS_N模式将首先以ALL_ROWS模 式的方式计算一次各执行计划的具体代价,之后将我们需要的N条记录代入成本计算中代替实 际全部的候选行(CARD)以得出FIRST_ROWS_N中 的计划成本。

create table test as select  * from dba_objects;

create table testa as select * from test;

alter session set events’10053 trace name context forever,level 1′;    –使用10053事 件获取成本计算过程trace

alter session set optimizer_mode=all_rows;

select test.owner from test,testa where test.object_id=testa.object_id

alter session set events’10053 trace name context off’;

下为ALL_ROWS模式中,最佳连接方式的选 取:

NL Join

Outer table: Card: 9622.00  Cost: 35.37  Resp: 35.37  Degree: 1  Bytes: 7

Inner table: TESTA  Alias: TESTA

Access Path: TableScan

NL Join:  Cost: 318924.52  Resp: 318924.52  Degree: 0

Cost_io: 315358.00  Cost_cpu: 27736509932

Resp_io: 315358.00  Resp_cpu: 27736509932

Access Path: index (index (FFS))

Index: INDA_ID

resc_io: 5.69  resc_cpu: 1304190

ix_sel: 0.0000e+00  ix_sel_with_filters: 1

Inner table: TESTA  Alias: TESTA

Access Path: index (FFS)

NL Join:  Cost: 56375.98  Resp: 56375.98  Degree: 0

Cost_io: 54762.00  Cost_cpu: 12551800804

Resp_io: 54762.00  Resp_cpu: 12551800804

Access Path: index (AllEqJoinGuess)

Index: INDA_ID

resc_io: 1.00  resc_cpu: 8171

ix_sel: 1.0393e-04  ix_sel_with_filters: 1.0393e-04

NL Join: Cost: 9667.48  Resp: 9667.48  Degree: 1

Cost_io: 9657.00  Cost_cpu: 81507910

Resp_io: 9657.00  Resp_cpu: 81507910

Best NL cost: 9667.48

resc: 9667.48 resc_io: 9657.00 resc_cpu: 81507910

resp: 9667.48 resp_io: 9657.00 resp_cpu: 81507910

Join Card:  9622.00 = outer (9622.00) * inner (9622.00) * sel (1.0393e-04)

Join Card – Rounded: 9622 Computed: 9622.00

SM Join

Outer table:

resc: 35.37  card 9622.00  bytes: 7  deg: 1  resp: 35.37

Inner table: TESTA  Alias: TESTA

resc: 7.17  card: 9622.00  bytes: 3  deg: 1  resp: 7.17

using dmeth: 2  #groups: 1

SORT resource      Sort statistics

Sort width:          70 Area size:      131072 Max Area size:    12582912

Degree:               1

Blocks to Sort:      17 Row size:           14 Total Rows:           9622

Initial runs:         2 Merge passes:        1 IO Cost / pass:         10

Total IO sort cost: 27      Total CPU sort cost: 13931876

Total Temp space used: 254000

SM join: Resc: 203.62  Resp: 203.62  [multiMatchCost=0.00]

HA Join

Outer table:

resc: 35.37  card 9622.00  bytes: 7  deg: 1  resp: 35.37

Inner table: TESTA  Alias: TESTA

resc: 7.17  card: 9622.00  bytes: 3  deg: 1  resp: 7.17

using dmeth: 2  #groups: 1

Cost per ptn: 0.81  #ptns: 1

hash_area: 124 (max=3072)   Hash join: Resc: 43.35  Resp: 43.35  [multiMatchCost=0.00]

HA Join (swap)

Outer table:

resc: 7.17  card 9622.00  bytes: 3  deg: 1  resp: 7.17

Inner table: TEST  Alias: TEST

resc: 35.37  card: 9622.00  bytes: 7  deg: 1  resp: 35.37

using dmeth: 2  #groups: 1

Cost per ptn: 0.81  #ptns: 1

hash_area: 124 (max=3072)   Hash join: Resc: 43.35  Resp: 43.35  [multiMatchCost=0.00]

HA cost: 43.35

resc: 43.35 resc_io: 42.00 resc_cpu: 10480460

resp: 43.35 resp_io: 42.00 resp_cpu: 10480460

Best:: JoinMethod: Hash

Cost: 43.35  Degree: 1  Resp: 43.35  Card: 9622.00  Bytes: 10

***********************

Best so far: Table#: 0  cost: 35.3706  card: 9622.0000  bytes: 67354

Table#: 1  cost: 43.3476  card: 9622.0000  bytes: 96220

可以看到连接中二表上的候选行都是9622条,实际结果集也是9622条。

我们来看FIRST_ROWS_10情况下的trace:

alter session set events’10053 trace name context forever,level 1′;

alter session set optimizer_mode=first_rows_10;

select test.owner from test,testa where test.object_id=testa.object_id;

alter session set events’10053 trace name context off’;

Now joining: TEST[TEST]#0

***************

NL Join

Outer table: Card: 11.00  Cost: 2.00  Resp: 2.00  Degree: 1  Bytes: 3

Inner table: TEST  Alias: TEST

Access Path: TableScan

NL Join:  Cost: 368.08  Resp: 368.08  Degree: 0

Cost_io: 364.00  Cost_cpu: 31713898

Resp_io: 364.00  Resp_cpu: 31713898

Access Path: index (AllEqJoinGuess)

Index: IND_ID

resc_io: 2.00  resc_cpu: 15503

ix_sel: 1.0393e-04  ix_sel_with_filters: 1.0393e-04

NL Join (ordered): Cost: 24.02  Resp: 24.02  Degree: 1

Cost_io: 24.00  Cost_cpu: 178973

Resp_io: 24.00  Resp_cpu: 178973

Best NL cost: 24.02

resc: 24.02 resc_io: 24.00 resc_cpu: 178973

resp: 24.02 resp_io: 24.00 resp_cpu: 178973

Join Card:  11.00 = outer (11.00) * inner (9622.00) * sel (1.0393e-04)

Join Card – Rounded: 11 Computed: 11.00

SM Join

Outer table:

resc: 7.17  card 9622.00  bytes: 3  deg: 1  resp: 7.17

Inner table: TEST  Alias: TEST

resc: 35.37  card: 9622.00  bytes: 7  deg: 1  resp: 35.37

using dmeth: 2  #groups: 1

SORT resource      Sort statistics

Sort width:          70 Area size:      131072 Max Area size:    12582912

Degree:               1

Blocks to Sort:      22 Row size:           18 Total Rows:           9622

Initial runs:         2 Merge passes:        1 IO Cost / pass:         14

Total IO sort cost: 36      Total CPU sort cost: 14055006

Total Temp space used: 320000

SORT resource      Sort statistics

Sort width:          70 Area size:      131072 Max Area size:    12582912

Degree:               1

Blocks to Sort:      17 Row size:           14 Total Rows:           9622

Initial runs:         2 Merge passes:        1 IO Cost / pass:         10

Total IO sort cost: 27      Total CPU sort cost: 13931876

Total Temp space used: 254000

SM join: Resc: 109.14  Resp: 109.14  [multiMatchCost=0.00]

SM cost: 109.14

resc: 109.14 resc_io: 105.00 resc_cpu: 32173386

resp: 109.14 resp_io: 105.00 resp_cpu: 32173386

SM Join (with index on outer)

Access Path: index (FullScan)

Index: IND_ID

resc_io: 167.00  resc_cpu: 5134300

ix_sel: 1  ix_sel_with_filters: 1

Cost: 167.66  Resp: 167.66  Degree: 1

Outer table:

resc: 167.66  card 11.00  bytes: 7  deg: 1  resp: 167.66

Inner table: TESTA  Alias: TESTA

resc: 7.17  card: 9622.00  bytes: 3  deg: 1  resp: 7.17

using dmeth: 2  #groups: 1

SORT resource      Sort statistics

Sort width:          70 Area size:      131072 Max Area size:    12582912

Degree:               1

Blocks to Sort:      17 Row size:           14 Total Rows:           9622

Initial runs:         2 Merge passes:        1 IO Cost / pass:         10

Total IO sort cost: 27      Total CPU sort cost: 13931876

Total Temp space used: 254000

SM join: Resc: 203.62  Resp: 203.62  [multiMatchCost=0.00]

HA Join

Outer table:

resc: 35.37  card 9622.00  bytes: 7  deg: 1  resp: 35.37

Inner table: TESTA  Alias: TESTA

resc: 7.17  card: 9622.00  bytes: 3  deg: 1  resp: 7.17

using dmeth: 2  #groups: 1

Cost per ptn: 0.81  #ptns: 1

hash_area: 124 (max=3072)   Hash join: Resc: 43.35  Resp: 43.35  [multiMatchCost=0.00]

HA Join (swap)

Outer table:

resc: 7.17  card 9622.00  bytes: 3  deg: 1  resp: 7.17

Inner table: TEST  Alias: TEST

resc: 2.00  card: 11.00  bytes: 7  deg: 1  resp: 2.00

using dmeth: 2  #groups: 1

Cost per ptn: 0.69  #ptns: 1

hash_area: 124 (max=3072)   Hash join: Resc: 9.85  Resp: 9.85  [multiMatchCost=0.00]

HA cost: 9.85

resc: 9.85 resc_io: 9.00 resc_cpu: 6646477

resp: 9.85 resp_io: 9.00 resp_cpu: 6646477

First K Rows: copy A one plan, tab=TESTA

Best:: JoinMethod: Hash

Cost: 9.85  Degree: 1  Resp: 9.85  Card: 9622.00  Bytes: 17

***********************

Best so far: Table#: 0  cost: 2.0012  card: 11.0000  bytes: 77

Table#: 1  cost: 9.8546  card: 9622.0000  bytes: 163574

可以看到此次计算中代入了用户希望最先返回的结果 条数11(为10+1),通过设 置连接对象的候选结果集(Card)以到达相关优化目的,相应的COST均有所下降。

下为FIRST_ROWS_1000的情况:

alter session set events’10053 trace name context forever,level 1′;

alter session set optimizer_mode=first_rows_1000;

select test.owner from test,testa where test.object_id=testa.object_id;

alter session set events’10053 trace name context off’;

NL Join

Outer table: Card: 1000.00  Cost: 5.04  Resp: 5.04  Degree: 1  Bytes: 7

Inner table: TESTA  Alias: TESTA

Access Path: TableScan

NL Join:  Cost: 33147.66  Resp: 33147.66  Degree: 0

Cost_io: 32777.00  Cost_cpu: 2882616819

Resp_io: 32777.00  Resp_cpu: 2882616819

Access Path: index (index (FFS))

Index: INDA_ID

resc_io: 5.69  resc_cpu: 1304190

ix_sel: 0.0000e+00  ix_sel_with_filters: 1

Inner table: TESTA  Alias: TESTA

Access Path: index (FFS)

NL Join:  Cost: 5861.74  Resp: 5861.74  Degree: 0

Cost_io: 5694.00  Cost_cpu: 1304492819

Resp_io: 5694.00  Resp_cpu: 1304492819

Access Path: index (AllEqJoinGuess)

Index: INDA_ID

resc_io: 1.00  resc_cpu: 8171

ix_sel: 1.0393e-04  ix_sel_with_filters: 1.0393e-04

NL Join: Cost: 1006.09  Resp: 1006.09  Degree: 1

Cost_io: 1005.00  Cost_cpu: 8474019

Resp_io: 1005.00  Resp_cpu: 8474019

Best NL cost: 1006.09

resc: 1006.09 resc_io: 1005.00 resc_cpu: 8474019

resp: 1006.09 resp_io: 1005.00 resp_cpu: 8474019

Join Card:  1000.00 = outer (1000.00) * inner (9622.00) * sel (1.0393e-04)

Join Card – Rounded: 1000 Computed: 1000.00

SM Join

Outer table:

resc: 35.37  card 9622.00  bytes: 7  deg: 1  resp: 35.37

Inner table: TESTA  Alias: TESTA

resc: 7.17  card: 9622.00  bytes: 3  deg: 1  resp: 7.17

using dmeth: 2  #groups: 1

SORT resource      Sort statistics

Sort width:          70 Area size:      131072 Max Area size:    12582912

Degree:               1

Blocks to Sort:      22 Row size:           18 Total Rows:           9622

Initial runs:         2 Merge passes:        1 IO Cost / pass:         14

Total IO sort cost: 36      Total CPU sort cost: 14055006

Total Temp space used: 320000

SORT resource      Sort statistics

Sort width:          70 Area size:      131072 Max Area size:    12582912

Degree:               1

Blocks to Sort:      17 Row size:           14 Total Rows:           9622

Initial runs:         2 Merge passes:        1 IO Cost / pass:         10

Total IO sort cost: 27      Total CPU sort cost: 13931876

Total Temp space used: 254000

SM join: Resc: 109.14  Resp: 109.14  [multiMatchCost=0.00]

SM cost: 109.14

resc: 109.14 resc_io: 105.00 resc_cpu: 32173386

resp: 109.14 resp_io: 105.00 resp_cpu: 32173386

SM Join (with index on outer)

Access Path: index (FullScan)

Index: IND_ID

resc_io: 167.00  resc_cpu: 5134300

ix_sel: 1  ix_sel_with_filters: 1

Cost: 167.66  Resp: 167.66  Degree: 1

Outer table:

resc: 167.66  card 1000.00  bytes: 7  deg: 1  resp: 167.66

Inner table: TESTA  Alias: TESTA

resc: 7.17  card: 9622.00  bytes: 3  deg: 1  resp: 7.17

using dmeth: 2  #groups: 1

SORT resource      Sort statistics

Sort width:          70 Area size:      131072 Max Area size:    12582912

Degree:               1

Blocks to Sort:      17 Row size:           14 Total Rows:           9622

Initial runs:         2 Merge passes:        1 IO Cost / pass:         10

Total IO sort cost: 27      Total CPU sort cost: 13931876

Total Temp space used: 254000

SM join: Resc: 203.62  Resp: 203.62  [multiMatchCost=0.00]

HA Join

Outer table:

resc: 35.37  card 9622.00  bytes: 7  deg: 1  resp: 35.37

Inner table: TESTA  Alias: TESTA

resc: 7.17  card: 9622.00  bytes: 3  deg: 1  resp: 7.17

using dmeth: 2  #groups: 1

Cost per ptn: 0.81  #ptns: 1

hash_area: 124 (max=3072)   Hash join: Resc: 43.35  Resp: 43.35  [multiMatchCost=0.00]

HA Join (swap)

Outer table:

resc: 7.17  card 9622.00  bytes: 3  deg: 1  resp: 7.17

Inner table: TEST  Alias: TEST

resc: 5.04  card: 1000.00  bytes: 7  deg: 1  resp: 5.04

using dmeth: 2  #groups: 1

Cost per ptn: 0.70  #ptns: 1

hash_area: 124 (max=3072)   Hash join: Resc: 12.91  Resp: 12.91  [multiMatchCost=0.00]

HA cost: 12.91

resc: 12.91 resc_io: 12.00 resc_cpu: 7038524

resp: 12.91 resp_io: 12.00 resp_cpu: 7038524

First K Rows: copy A one plan, tab=TESTA

Best:: JoinMethod: Hash

Cost: 12.91  Degree: 1  Resp: 12.91  Card: 9622.00  Bytes: 17

***********************

Best so far: Table#: 0  cost: 5.0389  card: 1000.0000  bytes: 7000

Table#: 1  cost: 12.9051  card: 9622.0000  bytes: 163574

可以看到此处代入了1000为某一连接对象的候选行数。

MOS上有一个著名的《MIGRATING TO THE COST-BASED OPTIMIZER》教材,详细介绍了RBO和CBO的区别:
[gview file=”http://askmac.cn/wp-content/uploads/resource/40178_rbo_rip.doc”]


Posted

in

by

Tags:

Comments

3 responses to “优化模式区别(all_rows & first_rows_n)”

  1. maclean Avatar

    Fast response optimization (FIRST_ROWS variants)
    PURPOSE
    ——-

    This document will explain the different ways to implement fast response
    optimization in Oracle9i.

    SCOPE & APPLICATION
    ——————-

    This document is intended for DBA and application developers.

    Fast response optimization (FIRST_ROWS variants)
    ————————————————

    Fast response optimization is suitable for Online Transaction Processing users.
    Typically, OLTP users are interested in seeing the first few rows, and they seldom
    are interested in seeing the entire query result, especially when the
    result size is large. For such users, it makes sense to optimize the query
    to produce the first few rows as fast as possible, even if the time to
    produce the entire query result is not minimized.

    The CBO employes two different fast response optimizations. The ‘old’ method
    is used when the FIRST_ROWS hint or parameter value is enabled. This method
    was introduced in the initial CBO release (Oracle7 onwards). The new method
    is enabled by the FIRST_ROWS_n hint or parameter value (available in Oracle9i
    onwards).

    1. FIRST_ROWS
    ————-

    The optimizer uses a mix of costs and heuristics to find the best plan for
    fast delivery of the first few rows. The heuristic sometimes leads the CBO
    to generate a plan whose cost is significantly larger than the cost of a plan
    without applying the heuristic. FIRST_ROWS is available for backward
    compatibility and plan stability.

    Some of the heuristics are:

    a. The full table scans are considered to have an infinite cost when comparing
    it with other plans. Therefore, plans that contain full table scans will only
    be used if there are no alternative plans that use indexes instead. Be aware
    that the optimizer does not consider all indexes on the table, but only the
    one that is induced by the query (see note:67522.1)

    b. Any ORDER BY clause can induce the index access

    When using an index access in the query can avoid a final sort, then this
    plan will be used.

    This heuristics can be fine tuned by setting the sort_elimination_cost_ratio
    init.ora. This cost ratio dictates how expensive a plan with ORDER BY sort
    elimination can be before it is rejected in favor of a cheaper plan which uses
    a sort. For example, a value of 5 means that a plan that avoids a sort may not be
    more than 5 times more expensive than a plan that does not avoid it. The default
    is 0 (no value) which means a plan with ORDER BY sort elimination will be chosen
    even if it is infinitely more expensive.

    c. The OR expansions are disabled. It is the same as if the hint NO_EXPAND is
    specified.

    2. FIRST_ROWS_N
    —————

    The optimizer uses a cost based approach, regardless of the presence of
    statistics, and optimizes with a goal of best response time to return the
    first n rows (where n=1, 10, 100, 1000). The new method is totally based
    on costs, and it is sensitive to the value of n. The CBO explores different
    plans and, for each one, computes the cost to produce the first n rows. It picks
    the plan that produces the first N rows with the lowest cost. Therefore,
    the explain plan will show that the expected number of rows is 10, 100, 1000
    in the first tuple CARDINALITY column of the PLAN_TABLE.

    The query is decomposed in a number of table accesses that are joined together.
    The number of rows in each access/join is estimated (named the CARDINALITY,
    found in the PLAN_TABLE) following the standard ALL_ROWS model. If we estimate
    that 3239 rows will be retrieved if the whole query was performed, we then reduce
    the cardinality of each access in proportion (multiply them by 100/3239
    if FIRST_ROWS_100 is chosen): e.g if an intermediate join estimation says
    that we will retrieve 100000 rows, we will change this estimation to
    100000*100/3239 (only in some cases, sometimes a minimum value is taken).
    We then recalculate all the costs in function of those new cardinality
    estimations, and we choose the best plan.

    With small values of n, the CBO tends to generate plans that consist of
    nested loops joins with index lookups. With large values of n, the CBO tends
    to generate plans that consist of hash joins and full table scans.

    The value of n should be chosen based on the OLTP user requirement.
    It depends on how the result is displayed to the user.

    Remember that with fast response optimization, a plan that produces the first
    n rows with the lowest cost might not be the optimal plan to produce the
    entire result. If the requirement is to obtain the entire result of a query,
    then fast response optimization should not be used. Instead the ALL_ROWS
    parameter value or hint should be set.

    3. How to configure it
    ———————-

    You can set it up the following way:

    a. at the instance level by setting the init.ora parameter OPTIMIZER_MODE, e.g.

    OPTIMIZER_MODE = FIRST_ROWS
    OPTIMIZER_MODE = FIRST_ROWS_100
    OPTIMIZER_MODE = FIRST_ROWS_1000

    b. at the session level by altering the session, e.g.

    ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_100

    c. at the query level by using hints, e.g.

    SELECT /*+ FIRST_ROWS(10) */ ….

  2. maclean Avatar

    Why is my index not used?
    * The table is indexed isn’t it? 🙂

    * Why SHOULD the index be used?

    * Are the indexed columns/leading column of the index supplied in the where clause of the query (predicate list) as a single table (non-join) predicate?

    No:
    At least the leading column of an index is required in the predicate list to use an index in a query (but see Skip Scan below.)

    Example:
    You have defined index EMPNO_I1 on single column EMP.EMPNO, and defined concatenated index EMPNO_DEPT_I2 on columns EMP.EMPNO and EMP.DEPT (note, EMP.EMPNO is leading column).
    You must use the column EMP.EMPNO in the predicate list (WHERE clause) in order for the optimizer to consider either index:

    Select ename, sal, deptno from emp where empno<100;

    Exceptions:
    + CBO can use a Index Fast Full Scan (INDEX_FFS) as long as the index contains all the columns that are needed for the query, and at least one column in the index key has the NOT NULL constraint. The leading column of an index is not required for an INDEX_FFS to be performed. Note that the use of an INDEX_FFS does not necessarily return the rows in sorted order. Ordering is dependent on the order that the index blocks are read and rows are only guaranteed to be returned in a sorted order if an 'order by' clause is used. See: Note 344135.1 Ordering of Result Data
    Note:70135.1 Index Fast Full Scan Usage To Avoid Full Table Scans
    + CBO can use an Index Skip Scan (INDEX_SS). The leading column of an index is not required for an INDEX_SS to be performed.
    See Note:212391.1 Index Skip Scan Feature
    + CBO can choose to use an index to avoid sorting. The indexed columns would need to be in the order by clause for this to happen.
    See Note:67409.1, and Note:10577.1

    Are the indexed columns part of join predicates? e.g. emp.deptno= dept.deptno
    *

    If Yes then:

    What type of join is used?

    Only a Nested loops join can allow index lookups on the inner table that are based solely on the join column(s):

    Hash / Sort Merge Join:
    With Hash joins and Sort Merge joins, information from the outer table is not available at join time to enable row look ups on the inner table; rather both tables are accessed separately and then the resultant data is joined. The inner table of a Hash or Sort Merge cannot be probed solely using an index based on the join columns . This is an inherent limitation of the implementation mechanisms used by these join types. Nested Loops joins are different in as much as they allow index lookups on the join columns.

    Nested Loops Join:
    Nested loop joins work by reading the outer table and then using the information gathered to probe the inner table. This algorithm allows index lookups to occur on the inner table.

    Does the join order allow index usage?

    Due to this limitation, the join order of the tables is important. The outer table of a nested loops join must have been vistied BEFORE an index can be used on the inner table. Check the explain plan for the query to determine which access path has been used. For example: If there is an index on EMP.DEPTNO and, assuming ther eare no other predicates that relate to EMP.DEPTNO int the query, EMP is visited before DEPT, then no values are present that can be used to lookup rows in the EMP.DEPTNO index. The only way the index could be used is with a full index scan or a index fast full scan. In this case it is possible that a Full Table Scan (FTS) will cost less and be chosen instead. The RBO would not even consider using the index.

    * Are the indexed columns part of an IN list or multiple OR's? e.g. emp.deptno IN (10,23,34,….)
    It is possible that the query has been transformed in to something that cannot use an index. See Note:62153.1

    * Are the indexed columns modified by functions?
    Indexes cannot be used on columns modified by functions. Oracle 8i adds function based indexes.
    Is implicit type conversion going on?
    *
    If the datatypes of two values being compared are different, then Oracle has to implement type conversion on one of the values to enable comparisons to be made. This is called implicit type conversion. Typically this causes problems when developers store numbers in character columns. At runtime oracle is forced to convert one of the values and (due to fixed rules) places a to_number around the indexed character column. Adding any function to an indexed column prevents use of the index. The fact that Oracle has to do this type conversion is an indication of a design problem with the application. Because conversion is performed on EVERY ROW RETRIEVED, this will also result in a performance hit.
    Is it semantically impossible to use an index?
    *
    Because of cost considerations on the query as a whole, a plan may have been chosen that means that the use of an index at a lower level is now not possible. The index may have been considered in other join orders/methods but the method with the lowest cost makes the index unusable. Because of the way the query has been executed (i.e. join orders/methods) it is now 'semantically impossible' to use an index.
    Is the 'wrong type' of index scan made? e.g. Index fast full scan as opposed to index range scan
    *
    It is possible that the optimizer has chosen the desired index but a different scan method would be preferable to the user. In this case utilise the INDEX_FFS, INDEX_ASC and INDEX_DESC hints to force the scan type that you require.
    See Note:62339.1 for more information.

    @ PAA content management, action #23093

    Since Oracle8i indexes alternatively can be defined with ascending or decending sort order. Oracle treats descending indexes as if it were function-based indexes and therefore a different execution plan might be used compared to that used for a default ascending sort order. By examine the execution plan you do not see whether the default ascending order or the descending sort order will be used therefore additionally check the 'DESCEND' column of view DBA_IND_COLUMNS.
    Are Accurate and Appropriate statistics in place?
    *
    The CBO relies on accurate, up to date and complete statistics to enable it to determine the optimal access plan for a particular query. Ensure that statistics have been gathered if the intention is to use the CBO. Using CBO with no statistics will force the CBO to use predefined defaults which are unlikely to produce a good plan or promote index usage with your application. See Note:35934.1 for more details.

    Remember that the CBO may choose a different index because the costs indicate that this is appropriate. The RBO uses a rigid system of rules to determine what access method to use.

    In addition to basic table and index statistics, column statistics should be gathered for columns with a non-uniform data distribution. For advice on gathering statistics see Note:44961.1 TECH: Comments on Frequency of using ANALYZE

    @ PAA content management, action #23093

    In general, new statistics should be gathered after a schema object’s data or structure are modified in ways that make the previous statistics inaccurate. For example, after loading a significant number of rows into a table, collect new statistics on the number of rows. It is also recommended to gather new statistics information after having installed a new patchset. The table access works best when the statistics have been generated by the same version as currently executing. More information about "Why is my querries slow since upgrading the database" can be found in note <>
    Does the index have the same rank or cost as another index?
    *
    If there is a choice between equally ranked access methods, then the RBO uses the order in the row cache to decide on which index to use (with equally ranked tables it uses from clause order from right to left). With equally costed indexes, the CBO uses various ways to break the tie e.g. alphabetical order of index name, bigger NDK for fully matched indexes (not for fast full scans) or index with lower number of leaf blocks. Note that this is unlikely to be a common occurrence.
    See Note:73167.1 for more information.
    Is the index unselective?
    *
    The index is unselective
    It may not be a good idea to use it anyway… The column data does not have a uniform distribution
    The CBO assumes that column data is not skewed and is uniformly distributed. If this is not the case then the statistics may not reflect the actuality and indexes may not be chosen for some selective values because of the unselective nature of the column as a whole. If this is the case then consideration should be given to the creation of histograms to record a more accurate picture of column data distribution or alternatively use hints. The optimizer statistics are inadequate making indexes appear unselective
    Possible workarounds:
    gather more accurate stats. See Note:44961.1 TECH: Comments on Frequency of using ANALYZE
    consider gathering column statistics where column data is not uniform
    use hints. See Note:29236.1 QREF: SQL Statement HINTS and Note:50607.1 How to specify an INDEX Hint
    Are the indexed columns NULLable?
    *
    Indexes do NOT store NULL values unless the index is concatenated (i.e. multi-column indexes), or it is a Bitmap index.
    For concatenated indexes NULLs are only stored if at least one of the indexed columns is filled. Trailing NULLs in concatenated indexes are stored. Rows are not stored if all the indexed columns are NULL. Operations that need to return the NULL values (such as count) may be prevented from using the index because of the lack of NULL values in the index. This is because the optimizer cannot guarantee that it can retrieve the necessary information using the index alone. There are also considerations with using NOT IN predicates and NULL values. See Note 28934.1
    Bitmap indexes are allowed to store NULLs. Therefore, they are considered NULLable and the optimizer may use them whether they are NULL safe or not. Indexing of nulls can be useful for some types of SQL statements, such as queries with the aggregate function COUNT. Example: SELECT COUNT(*) FROM EMP; For more information on Bitmap indexes See Note 70067.1 All about Bitmap Indexes, or Oracle Concepts Database Concepts Manual.
    Are views/subqueries involved?
    *
    Queries involving these structures are likely to be rewritten which may result in indexes not being used (even though one of the goals of the rewrite is to open up additional access paths). This rewrite is known as merging. See Note.199070.1 Optimizing statements that contain views or subqueries
    Are any of the tables remote?
    *
    Often indexes are not used against remote tables. Index usage in distributed queries is dependant on the query that is sent to the remote site. Often, with RBO, the query sent to the remote site does not contain indexed predicates. this can often result in a FTS. The CBO costs the remote access and will evaluates and compare the costs with and without indexed predicates sent to the remote site. Thus the CBO should make a more informed decision about index usage on remote tables. Building a view on the remote site containing relevant predicates to force index usage and then referencing that in your local query can often help. See Note:68809.1 Distributed Queries
    Is Parallel Execution (PX) involved?
    *
    The index access paths available under Parallel Execution are more restricted than under serial execution. A quick test is to disable parallelism for the query and see if this enables the index to be used.
    Is the query an update with a subquery?
    *
    There may be cases, due to cost considerations why an index is not chosen because it depends on values returned from a subquery. It may be possible to force the index to be used by implementing hints. See Note:68084.1 Using hints to optimize an Update with a subquery that is not using an index on the updated table.
    Does the query use bind variables?
    *
    The CBO cannot generate accurate cost figures for like or range predicates against bind variables. This may result in indexes not being chosen. See Note:68992.1 Predicate Selectivity
    Index hints don’t work
    *
    Remember to use table aliases. See Note:69992.1 Why is my hint ignored? and Note:50607.1 How to specify an INDEX Hint
    Useful hints: Also See Note:29236.1 QREF: SQL Statement HINTS

    FIRST_ROWS Likely to promote the use of indexes
    ORDERED Forces the join order of a query
    INDEX Forces an Index scan. Disables use of FAST mode (INDEX_FFS)
    INDEX_FFS Forces an Index to be scanned in FAST mode
    INDEX_ASC Forces an Ascending Index Range Scan
    INDEX_DESC Forces a Descending Index Range Scan

    * Deletes do not necessarily free up allocated index space
    Reorganization, Truncation or Deletion of data may or may not have cost implications for queries. Remember that deletes do not necessarily free up allocated space from objects. In addition, deletes do not reset the highwatermark for a table. Truncate does. Empty blocks may make indexes/tables appear more expensive than they potentially could be. Dropping and recreating the object will reorganise the structure and may potentially help (or may hinder). This problem is usually most noticeable when comparing the query performance of two different systems with the same data.
    Are partition views involved?
    *
    Is the partition view setup correctly? See Note:28426.1 Partition Views and the use of Indexes (7.1 & 7.2) and Note:43194.1 Partition Views in 7.3: Examples and Tests
    Is NLS_SORT set to BINARY?
    *
    If NLS_SORT is not set to BINARY, indexes will not be used. This is because indexes are built according to a binary order of keys (pre-sorted using binary values). Setting NLS_SORT to anything other than BINARY causes a sort to use a full table scan, regardless of the path chosen by the optimizer. For more detail on NLS_SORT and index use please reference: Note 30779.1 Init.ora Parameter “NLS_SORT” Reference, and Note 227335.1 Linguistic Sorting – Frequently Asked Questions (section 4.)

  3. maclean Avatar

    Setting OPTIMIZER_MODE = ALL_ROWS on Oracle9i Release 2 for Siebel 7.8
    Applies to:
    Product Release: V7 (Enterprise)
    Version: 7.8.2 [19213] PTG Com/Med
    Database: Oracle 9.2.0.6
    Application Server OS: Microsoft Windows 2003 Server
    Database Server OS: Sun Solaris 9

    This document was previously published as Siebel SR 38-3079465263.
    Symptoms
    Hello,

    We have several Siebel 7.8 environments.
    Our Development and Test environments have Oracle Database Server set with OPTIMIZER_MODE = CHOOSE.
    Production environment has this option set to ALL_ROWS.

    We have discussed this with our DBA Team and they recommend using OPTIMIZER_MODE = ALL_ROWS.
    According to Technical Note 582 from Siebel SupportWeb and other documents, it is recommended to have OPTIMIZER_MODE = CHOOSE when using Oracle 9.2 Database Server.

    Please could you help us define the difference between one and the other parameter setup and indicate the correct configuration?

    Thanks!
    Solution
    Message 1
    For the benefit of other readers:

    Customer wants to know the differences between the values CHOOSE and ALL_ROWS for Oracle 9.2 Database Server OPTIMIZER_MODE initialization parameter.

    Here are some useful Oracle Online Documentation references for additional information on OPTIMIZER_MODE.
    Please review Oracle9i Database Reference Release 2 (9.2) Part Number A96536-02 > Chapter 1 – Initialization Parameters > Section OPTIMIZER_MODE:

    “…
    choose

    The optimizer chooses between a cost-based approach and a rule-based approach based on whether statistics are available.
    If the data dictionary contains statistics for at least one of the accessed tables, then the optimizer uses a cost-based approach and optimizes with a goal of best throughput. If the data dictionary contains only some statistics, then the cost-based approach is used, and the optimizer must guess the statistics for the subjects without any statistics. This can result in sub-optimal execution plans. If the data dictionary contains no statistics for any of the accessed tables, then the optimizer uses a rule-based approach.

    all_rows

    The optimizer uses a cost-based approach for all SQL statements in the session regardless of the presence of statistics and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).
    …”

    [CONT 1/2…]
    Message 2
    [… CONT 2/2]

    The difference is that setting OPTIMIZER_MODE parameter to ALL_ROWS always causes Oracle to use the Cost-Based Optimizer (CBO), while having it set to CHOOSE causes Oracle to decide using the Rule-Based Optimizer (RBO) in case none of the involved objects is analyzed.
    If there are statistics available for at least one table, both values will cause Oracle to use CBO.
    If no statistics are available for any of the tables involved in the SQL statement, CHOOSE will cause Oracle to use RBO while ALL_ROWS will force the use of CBO by guessing which execution path will provide the best throughput.

    From Siebel perspective, this parameter will not cause major impacts for Application Object Managers, since they always set OPTIMIZER_MODE to FIRST_ROWS_10 at session level before running any queries.
    However, some Server Components use the value specified at instance level, and this must be set to CHOOSE.

    The only value supported by Siebel Systems for OPTIMIZER_MODE parameter on Oracle9i is CHOOSE.
    The ALL_ROWS value should only be used on Oracle10g.

Leave a Reply

Your email address will not be published. Required fields are marked *