2群基础群 适合刚入门的同学，会共享最佳入门实践和资料 QQ群 # QQ群号:171092051 # 已经升级到 500人的超级群，空位多多，无需面试
优化模式区别(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://youyus.com/wp-content/uploads/resource/40178_rbo_rip.doc"]
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://youyus.com/wp-content/uploads/resource/40178_rbo_rip.doc"]
posted on 2009-11-12 13:20 Oracle和MySQL 阅读(437) 评论(0) 编辑 收藏 举报
