1、firstrowsn和allrows在性能上的区别在收集数据做另一个ppt的时候，需要first_rows(n)跟all_rows的一些对比数据，我直接把原数据整理了一下，大家有兴趣可以看下。最后有结论。实验分隔线create table t as select * from dba_objects;create table t1 as select * from t;create index ind_object_id on t(object_id) compute statistics;create index ind_t1_object_id on t1(object_id) compu
2、te statistics;analyze table t compute statistics for table for all columns;analyze table t1 compute statistics for table for all columns;准备好测试表和索引后来看看测试脚本all_rows模式:alter session set events10053 trace name context forever,level 1;alter session set optimizer_mode=all_rows;select t.owner from t,t1 whe
3、re t.object_id = t1.object_id; alter session set events10053 trace name context off;first_rows_1模式:alter session set events10053 trace name context forever,level 1;alter session set optimizer_mode=first_rows_1;select t.owner from t,t1 where t.object_id = t1.object_id; alter session set events10053 t
4、race name context off;first_rows_10模式:alter session set events10053 trace name context forever,level 1;alter session set optimizer_mode=first_rows_10;select t.owner from t,t1 where t.object_id = t1.object_id; alter session set events10053 trace name context off;first_rows_100模式:alter session set eve
5、nts10053 trace name context forever,level 1;alter session set optimizer_mode=first_rows_100;select t.owner from t,t1 where t.object_id = t1.object_id; alter session set events10053 trace name context off;由于篇幅太长,所以把10053的trace文件简化了一下,只留下join这一部分的内容,并把merge join的部分去除了测试环境是10g r2all_rows:*GENERAL PLANS
6、*Considering cardinality-based initial join order.*Join order1: TT#0 T1T1#1*Now joining: T1T1#1*NL Join Outer table: Card: 51986.00 Cost: 164.59 Resp: 164.59 Degree: 1 Bytes: 9 Inner table: T1 Alias: T1 Access Path: TableScan NL Join: Cost: 8493121.71 Resp: 8493121.71 Degree: 0 Cost_io: 8358538.00 C
7、ost_cpu: 839658589661 Resp_io: 8358538.00 Resp_cpu: 839658589661 Access Path: index (index (FFS) Index: IND_T1_OBJECT_ID resc_io: 25.16 resc_cpu: 7056806 ix_sel: 0.0000e+00 ix_sel_with_filters: 1 Inner table: T1 Alias: T1 Access Path: index (FFS) NL Join: Cost: 1366740.53 Resp: 1366740.53 Degree: 0
8、Cost_io: 1307937.00 Cost_cpu: 366871247240 Resp_io: 1307937.00 Resp_cpu: 366871247240 Access Path: index (AllEqJoinGuess) Index: IND_T1_OBJECT_ID resc_io: 1.00 resc_cpu: 8371 ix_sel: 1.9239e-05 ix_sel_with_filters: 1.9239e-05 NL Join: Cost: 52220.34 Resp: 52220.34 Degree: 1 Cost_io: 52148.00 Cost_cp
9、u: 451348998 Resp_io: 52148.00 Resp_cpu: 451348998 Best NL cost: 52220.34 resc: 52220.34 resc_io: 52148.00 resc_cpu: 451348998 resp: 52220.34 resp_io: 52148.00 resp_cpu: 451348998Join Card: 51982.00 = outer (51986.00) * inner (51986.00) * sel (1.9234e-05)Join Card - Rounded: 51982 Computed: 51982.00
10、HA Join Outer table: resc: 164.59 card 51986.00 bytes: 9 deg: 1 resp: 164.59 Inner table: T1 Alias: T1 resc: 28.13 card: 51986.00 bytes: 4 deg: 1 resp: 28.13 using dmeth: 2 #groups: 1 Cost per ptn: 2.58 #ptns: 1 hash_area: 0 (max=0) Hash join: Resc: 195.30 Resp: 195.30 multiMatchCost=0.00HA Join (sw
11、ap) Outer table: resc: 28.13 card 51986.00 bytes: 4 deg: 1 resp: 28.13 Inner table: T Alias: T resc: 164.59 card: 51986.00 bytes: 9 deg: 1 resp: 164.59 using dmeth: 2 #groups: 1 Cost per ptn: 2.58 #ptns: 1 hash_area: 0 (max=0) Hash join: Resc: 195.30 Resp: 195.30 multiMatchCost=0.00 HA cost: 195.30
12、resc: 195.30 resc_io: 189.00 resc_cpu: 39324090 resp: 195.30 resp_io: 189.00 resp_cpu: 39324090Best: JoinMethod: Hash Cost: 195.30 Degree: 1 Resp: 195.30 Card: 51982.00 Bytes: 13*Best so far: Table#: 0 cost: 164.5888 card: 51986.0000 bytes: 467874 Table#: 1 cost: 195.3030 card: 51982.0000 bytes: 675
13、766 计算第一种join顺序的成本值,T做驱动表,T1做内部表,Best: JoinMethod: Hash Cost: 195.30 Degree: 1 Resp: 195.30 Card: 51982.00 Bytes: 13在这里可以看到最优join方式是hash join,最终的成本是195.30,返回结果集记录数是51982*Join order2: T1T1#1 TT#0*Now joining: TT#0*NL Join Outer table: Card: 51986.00 Cost: 28.13 Resp: 28.13 Degree: 1 Bytes: 4 Inner ta
14、ble: T Alias: T Access Path: TableScan NL Join: Cost: 8492985.25 Resp: 8492985.25 Degree: 0 Cost_io: 8358403.00 Cost_cpu: 839649495148 Resp_io: 8358403.00 Resp_cpu: 839649495148 Access Path: index (AllEqJoinGuess) Index: IND_OBJECT_ID resc_io: 2.00 resc_cpu: 15913 ix_sel: 1.9239e-05 ix_sel_with_filt
15、ers: 1.9239e-05 NL Join (ordered): Cost: 104132.73 Resp: 104132.73 Degree: 1 Cost_io: 103999.00 Cost_cpu: 834303785 Resp_io: 103999.00 Resp_cpu: 834303785 Best NL cost: 104132.73 resc: 104132.73 resc_io: 103999.00 resc_cpu: 834303785 resp: 104132.73 resp_io: 103999.00 resp_cpu: 834303785Join Card: 5
16、1982.00 = outer (51986.00) * inner (51986.00) * sel (1.9234e-05)Join Card - Rounded: 51982 Computed: 51982.00HA Join Outer table: resc: 28.13 card 51986.00 bytes: 4 deg: 1 resp: 28.13 Inner table: T Alias: T resc: 164.59 card: 51986.00 bytes: 9 deg: 1 resp: 164.59 using dmeth: 2 #groups: 1 Cost per
17、ptn: 2.58 #ptns: 1 hash_area: 0 (max=0) Hash join: Resc: 195.30 Resp: 195.30 multiMatchCost=0.00 HA cost: 195.30 resc: 195.30 resc_io: 189.00 resc_cpu: 39324090 resp: 195.30 resp_io: 189.00 resp_cpu: 39324090Join order aborted: cost best plan cost计算第二种join顺序的成本值,T1做驱动表,T做内部表,Join order aborted: cost
18、 best plan cost第二种join顺序被放弃,因为成本大于已经第一种join顺序的最优成本*(newjo-stop-1) k:0, spcnt:0, perm:2, maxperm:2000*Number of join permutations tried: 2*(newjo-save) 1 0 Final - All Rows Plan: Best join order: 1 Cost: 195.3030 Degree: 1 Card: 51982.0000 Bytes: 675766 Resc: 195.3030 Resc_io: 189.0000 Resc_cpu: 3932
19、4090 Resp: 195.3030 Resp_io: 189.0000 Resc_cpu: 39324090在All Rows模式下最终优化器选择了Best join order: 1,Cost: 195.3030,尝试了2种join 顺序(Number of join permutations tried: 2)first_rows_1模式:*GENERAL PLANS*Considering cardinality-based initial join order.*Join order1: TT#0 T1T1#1*Now joining: T1T1#1*NL Join Outer t
20、able: Card: 51986.00 Cost: 164.59 Resp: 164.59 Degree: 1 Bytes: 9 Inner table: T1 Alias: T1 Access Path: TableScan NL Join: Cost: 8493121.71 Resp: 8493121.71 Degree: 0 Cost_io: 8358538.00 Cost_cpu: 839658589661 Resp_io: 8358538.00 Resp_cpu: 839658589661 Access Path: index (index (FFS) Index: IND_T1_
21、OBJECT_ID resc_io: 25.16 resc_cpu: 7056806 ix_sel: 0.0000e+00 ix_sel_with_filters: 1 Inner table: T1 Alias: T1 Access Path: index (FFS) NL Join: Cost: 1366740.53 Resp: 1366740.53 Degree: 0 Cost_io: 1307937.00 Cost_cpu: 366871247240 Resp_io: 1307937.00 Resp_cpu: 366871247240 Access Path: index (AllEq
22、JoinGuess) Index: IND_T1_OBJECT_ID resc_io: 1.00 resc_cpu: 8371 ix_sel: 1.9239e-05 ix_sel_with_filters: 1.9239e-05 NL Join: Cost: 52220.34 Resp: 52220.34 Degree: 1 Cost_io: 52148.00 Cost_cpu: 451348998 Resp_io: 52148.00 Resp_cpu: 451348998 Best NL cost: 52220.34 resc: 52220.34 resc_io: 52148.00 resc
23、_cpu: 451348998 resp: 52220.34 resp_io: 52148.00 resp_cpu: 451348998Join Card: 51982.00 = outer (51986.00) * inner (51986.00) * sel (1.9234e-05)Join Card - Rounded: 51982 Computed: 51982.00HA Join Outer table: resc: 164.59 card 51986.00 bytes: 9 deg: 1 resp: 164.59 Inner table: T1 Alias: T1 resc: 28
24、.13 card: 51986.00 bytes: 4 deg: 1 resp: 28.13 using dmeth: 2 #groups: 1 Cost per ptn: 2.58 #ptns: 1 hash_area: 0 (max=0) Hash join: Resc: 195.30 Resp: 195.30 multiMatchCost=0.00HA Join (swap) Outer table: resc: 28.13 card 51986.00 bytes: 4 deg: 1 resp: 28.13 Inner table: T Alias: T resc: 164.59 car
25、d: 51986.00 bytes: 9 deg: 1 resp: 164.59 using dmeth: 2 #groups: 1 Cost per ptn: 2.58 #ptns: 1 hash_area: 0 (max=0) Hash join: Resc: 195.30 Resp: 195.30 multiMatchCost=0.00 HA cost: 195.30 resc: 195.30 resc_io: 189.00 resc_cpu: 39324090 resp: 195.30 resp_io: 189.00 resp_cpu: 39324090Best: JoinMethod
26、: Hash Cost: 195.30 Degree: 1 Resp: 195.30 Card: 51982.00 Bytes: 13*Best so far: Table#: 0 cost: 164.5888 card: 51986.0000 bytes: 467874 Table#: 1 cost: 195.3030 card: 51982.0000 bytes: 675766*Number of join permutations tried: 1*(newjo-save) 1 0 Final - All Rows Plan: Best join order: 1 Cost: 195.3
27、030 Degree: 1 Card: 51982.0000 Bytes: 675766 Resc: 195.3030 Resc_io: 189.0000 Resc_cpu: 39324090 Resp: 195.3030 Resp_io: 189.0000 Resc_cpu: 39324090kkoipt: Query block SEL$1 (#0)* UNPARSED QUERY IS *SELECT /*+ NO_STAR_TRANSFORMATION NO_EXPAND */ “T”.”OWNER” “OWNER” FROM “TEST”.”T” “T”,”TEST”.”T1 “T1
28、 WHERE “T”.”OBJECT_ID”=”T1.”OBJECT_ID”kkoqbc-end : call(in-use=32712, alloc=49112), compile(in-use=35284, alloc=36696)First K Rows: K/N ratio = 0.000019237428341, qbc=0905f2620First K Rows: Setup end* 在FIRST_ROWS_1模式下,oracle会先按ALL_ROWS模式计算一种join顺序(Number of join permutations tried: 1),得到返回结果集的大小,从而计
29、算出FIRST_ROWS_1中的1条记录和所有结果集记录的一个比率值,Join Card - Rounded: 51982 Computed: 51982.00First K Rows: K/N ratio = 1/51982=0.000019237428341通过这个K/N ratio,oracle会重新计算join costSINGLE TABLE ACCESS PATH (First K Rows) Table: T Alias: T Card: Original: 2 Rounded: 2 Computed: 2.00 Non Adjusted: 2.00 Access Path: TableScan Cost: 2.00 Resp: 2.00 Degree: 0 Cost_io: 2.00 Cost_cpu: 7541 Resp_io: 2.00 Resp_cpu: 7541 Best: AccessPath: TableScan Cost: 2.00 Degree: 1 Resp: 2.0
