firstrowsn和allrows在性能上的区别.docx

firstrowsn和allrows在性能上的区别.docx

《firstrowsn和allrows在性能上的区别.docx》由会员分享，可在线阅读，更多相关《firstrowsn和allrows在性能上的区别.docx（20页珍藏版）》请在冰豆网上搜索。

firstrowsn和allrows在性能上的区别

在收集数据做另一个ppt的时候，需要first_rows（n）跟all_rows的一些对比数据，我直接把原数据整理了一下，大家有兴趣可以看下。

最后有结论。

。

。

——————————————————————————————实验分隔线———————————————————————————————————————

createtabletasselect*fromdba_objects;

createtablet1asselect*fromt;

createindexind_object_idont（object_id）computestatistics;

createindexind_t1_object_idont1（object_id）computestatistics;

analyzetabletcomputestatisticsfortableforallcolumns;

analyzetablet1computestatisticsfortableforallcolumns;

准备好测试表和索引后来看看测试脚本

all_rows模式:

altersessionsetevents’10053tracenamecontextforever,level1′;

altersessionsetoptimizer_mode=all_rows;

selectt.ownerfromt,t1wheret.object_id=t1.object_id;

altersessionsetevents’10053tracenamecontextoff’;

first_rows_1模式:

altersessionsetevents’10053tracenamecontextforever,level1′;

altersessionsetoptimizer_mode=first_rows_1;

selectt.ownerfromt,t1wheret.object_id=t1.object_id;

altersessionsetevents’10053tracenamecontextoff’;

first_rows_10模式:

altersessionsetevents’10053tracenamecontextforever,level1′;

altersessionsetoptimizer_mode=first_rows_10;

selectt.ownerfromt,t1wheret.object_id=t1.object_id;

altersessionsetevents’10053tracenamecontextoff’;

first_rows_100模式:

altersessionsetevents’10053tracenamecontextforever,level1′;

altersessionsetoptimizer_mode=first_rows_100;

selectt.ownerfromt,t1wheret.object_id=t1.object_id;

altersessionsetevents’10053tracenamecontextoff’;

由于篇幅太长,所以把10053的trace文件简化了一下,只留下join这一部分的内容,并把mergejoin的部分去除了

测试环境是10gr2

all_rows:

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

GENERALPLANS

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

Consideringcardinality-basedinitialjoinorder.

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

Joinorder[1]:

T[T]#0T1[T1]#1

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

Nowjoining:

T1[T1]#1

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

NLJoin

Outertable:

Card:

51986.00Cost:

164.59Resp:

164.59Degree:

1Bytes:

9

Innertable:

T1Alias:

T1

AccessPath:

TableScan

NLJoin:

Cost:

8493121.71Resp:

8493121.71Degree:

0

Cost_io:

8358538.00Cost_cpu:

839658589661

Resp_io:

8358538.00Resp_cpu:

839658589661

AccessPath:

index（index（FFS））

Index:

IND_T1_OBJECT_ID

resc_io:

25.16resc_cpu:

7056806

ix_sel:

0.0000e+00ix_sel_with_filters:

1

Innertable:

T1Alias:

T1

AccessPath:

index（FFS）

NLJoin:

Cost:

1366740.53Resp:

1366740.53Degree:

0

Cost_io:

1307937.00Cost_cpu:

366871247240

Resp_io:

1307937.00Resp_cpu:

366871247240

AccessPath:

index（AllEqJoinGuess）

Index:

IND_T1_OBJECT_ID

resc_io:

1.00resc_cpu:

8371

ix_sel:

1.9239e-05ix_sel_with_filters:

1.9239e-05

NLJoin:

Cost:

52220.34Resp:

52220.34Degree:

1

Cost_io:

52148.00Cost_cpu:

451348998

Resp_io:

52148.00Resp_cpu:

451348998

BestNLcost:

52220.34

resc:

52220.34resc_io:

52148.00resc_cpu:

451348998

resp:

52220.34resp_io:

52148.00resp_cpu:

451348998

JoinCard:

51982.00=outer（51986.00）*inner（51986.00）*sel（1.9234e-05）

JoinCard-Rounded:

51982Computed:

51982.00

HAJoin

Outertable:

resc:

164.59card51986.00bytes:

9deg:

1resp:

164.59

Innertable:

T1Alias:

T1

resc:

28.13card:

51986.00bytes:

4deg:

1resp:

28.13

usingdmeth:

2#groups:

1

Costperptn:

2.58#ptns:

1

hash_area:

0（max=0）Hashjoin:

Resc:

195.30Resp:

195.30[multiMatchCost=0.00]

HAJoin（swap）

Outertable:

resc:

28.13card51986.00bytes:

4deg:

1resp:

28.13

Innertable:

TAlias:

T

resc:

164.59card:

51986.00bytes:

9deg:

1resp:

164.59

usingdmeth:

2#groups:

1

Costperptn:

2.58#ptns:

1

hash_area:

0（max=0）Hashjoin:

Resc:

195.30Resp:

195.30[multiMatchCost=0.00]

HAcost:

195.30

resc:

195.30resc_io:

189.00resc_cpu:

39324090

resp:

195.30resp_io:

189.00resp_cpu:

39324090

Best:

:

JoinMethod:

Hash

Cost:

195.30Degree:

1Resp:

195.30Card:

51982.00Bytes:

13

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

Bestsofar:

Table#:

0cost:

164.5888card:

51986.0000bytes:

467874

Table#:

1cost:

195.3030card:

51982.0000bytes:

675766

计算第一种join顺序的成本值,T做驱动表,T1做内部表,

Best:

:

JoinMethod:

Hash

Cost:

195.30Degree:

1Resp:

195.30Card:

51982.00Bytes:

13

在这里可以看到最优join方式是hashjoin,

最终的成本是195.30,返回结果集记录数是51982

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

Joinorder[2]:

T1[T1]#1T[T]#0

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

Nowjoining:

T[T]#0

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

NLJoin

Outertable:

Card:

51986.00Cost:

28.13Resp:

28.13Degree:

1Bytes:

4

Innertable:

TAlias:

T

AccessPath:

TableScan

NLJoin:

Cost:

8492985.25Resp:

8492985.25Degree:

0

Cost_io:

8358403.00Cost_cpu:

839649495148

Resp_io:

8358403.00Resp_cpu:

839649495148

AccessPath:

index（AllEqJoinGuess）

Index:

IND_OBJECT_ID

resc_io:

2.00resc_cpu:

15913

ix_sel:

1.9239e-05ix_sel_with_filters:

1.9239e-05

NLJoin（ordered）:

Cost:

104132.73Resp:

104132.73Degree:

1

Cost_io:

103999.00Cost_cpu:

834303785

Resp_io:

103999.00Resp_cpu:

834303785

BestNLcost:

104132.73

resc:

104132.73resc_io:

103999.00resc_cpu:

834303785

resp:

104132.73resp_io:

103999.00resp_cpu:

834303785

JoinCard:

51982.00=outer（51986.00）*inner（51986.00）*sel（1.9234e-05）

JoinCard-Rounded:

51982Computed:

51982.00

HAJoin

Outertable:

resc:

28.13card51986.00bytes:

4deg:

1resp:

28.13

Innertable:

TAlias:

T

resc:

164.59card:

51986.00bytes:

9deg:

1resp:

164.59

usingdmeth:

2#groups:

1

Costperptn:

2.58#ptns:

1

hash_area:

0（max=0）Hashjoin:

Resc:

195.30Resp:

195.30[multiMatchCost=0.00]

HAcost:

195.30

resc:

195.30resc_io:

189.00resc_cpu:

39324090

resp:

195.30resp_io:

189.00resp_cpu:

39324090

Joinorderaborted:

cost>bestplancost

计算第二种join顺序的成本值,T1做驱动表,T做内部表,

Joinorderaborted:

cost>bestplancost

第二种join顺序被放弃,因为成本大于已经第一种join顺序的最优成本

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

（newjo-stop-1）k:

0,spcnt:

0,perm:

2,maxperm:

2000

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

Numberofjoinpermutationstried:

2

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

（newjo-save）[10]

Final-AllRowsPlan:

Bestjoinorder:

1

Cost:

195.3030Degree:

1Card:

51982.0000Bytes:

675766

Resc:

195.3030Resc_io:

189.0000Resc_cpu:

39324090

Resp:

195.3030Resp_io:

189.0000Resc_cpu:

39324090

在AllRows模式下最终优化器选择了Bestjoinorder:

1,Cost:

195.3030,

尝试了2种join顺序（Numberofjoinpermutationstried:

2）

first_rows_1模式:

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

GENERALPLANS

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

Consideringcardinality-basedinitialjoinorder.

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

Joinorder[1]:

T[T]#0T1[T1]#1

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

Nowjoining:

T1[T1]#1

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

NLJoin

Outertable:

Card:

51986.00Cost:

164.59Resp:

164.59Degree:

1Bytes:

9

Innertable:

T1Alias:

T1

AccessPath:

TableScan

NLJoin:

Cost:

8493121.71Resp:

8493121.71Degree:

0

Cost_io:

8358538.00Cost_cpu:

839658589661

Resp_io:

8358538.00Resp_cpu:

839658589661

AccessPath:

index（index（FFS））

Index:

IND_T1_OBJECT_ID

resc_io:

25.16resc_cpu:

7056806

ix_sel:

0.0000e+00ix_sel_with_filters:

1

Innertable:

T1Alias:

T1

AccessPath:

index（FFS）

NLJoin:

Cost:

1366740.53Resp:

1366740.53Degree:

0

Cost_io:

1307937.00Cost_cpu:

366871247240

Resp_io:

1307937.00Resp_cpu:

366871247240

AccessPath:

index（AllEqJoinGuess）

Index:

IND_T1_OBJECT_ID

resc_io:

1.00resc_cpu:

8371

ix_sel:

1.9239e-05ix_sel_with_filters:

1.9239e-05

NLJoin:

Cost:

52220.34Resp:

52220.34Degree:

1

Cost_io:

52148.00Cost_cpu:

451348998

Resp_io:

52148.00Resp_cpu:

451348998

BestNLcost:

52220.34

resc:

52220.34resc_io:

52148.00resc_cpu:

451348998

resp:

52220.34resp_io:

52148.00resp_cpu:

451348998

JoinCard:

51982.00=outer（51986.00）*inner（51986.00）*sel（1.9234e-05）

JoinCard-Rounded:

51982Computed:

51982.00

HAJoin

Outertable:

resc:

164.59card51986.00bytes:

9deg:

1resp:

164.59

Innertable:

T1Alias:

T1

resc:

28.13card:

51986.00bytes:

4deg:

1resp:

28.13

usingdmeth:

2#groups:

1

Costperptn:

2.58#ptns:

1

hash_area:

0（max=0）Hashjoin:

Resc:

195.30Resp:

195.30[multiMatchCost=0.00]

HAJoin（swap）

Outertable:

resc:

28.13card51986.00bytes:

4deg:

1resp:

28.13

Innertable:

TAlias:

T

resc:

164.59card:

51986.00bytes:

9deg:

1resp:

164.59

usingdmeth:

2#groups:

1

Costperptn:

2.58#ptns:

1

hash_area:

0（max=0）Hashjoin:

Resc:

195.30Resp:

195.30[multiMatchCost=0.00]

HAcost:

195.30

resc:

195.30resc_io:

189.00resc_cpu:

39324090

resp:

195.30resp_io:

189.00resp_cpu:

39324090

Best:

:

JoinMethod:

Hash

Cost:

195.30Degree:

1Resp:

195.30Card:

51982.00Bytes:

13

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

Bestsofar:

Table#:

0cost:

164.5888card:

51986.0000bytes:

467874

Table#:

1cost:

195.3030card:

51982.0000bytes:

675766

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

Numberofjoinpermutationstried:

1

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

（newjo-save）[10]

Final-AllRowsPlan:

Bestjoinorder:

1

Cost:

195.3030Degree:

1Card:

51982.0000Bytes:

675766

Resc:

195.3030Resc_io:

189.0000Resc_cpu:

39324090

Resp:

195.3030Resp_io:

189.0000Resc_cpu:

39324090

kkoipt:

QueryblockSEL$1（#0）

*******UNPARSEDQUERYIS*******

SELECT/*+NO_STAR_TRANSFORMATIONNO_EXPAND*/“T”.”OWNER”“OWNER”FROM“TEST”.”T”“T”,”TEST”.”T1″“T1″WHERE“T”.”OBJECT_ID”=”T1″.”OBJECT_ID”

kkoqbc-end

:

call（in-use=32712,alloc=49112）,compile（in-use=35284,alloc=36696）

FirstKRows:

K/Nratio=0.000019237428341,qbc=0×905f2620

FirstKRows:

Setupend

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

在FIRST_ROWS_1模式下,oracle会先按ALL_ROWS模式计算一种join顺序（Numberofjoinpermutationstried:

1）

得到返回结果集的大小,

从而计算出FIRST_ROWS_1中的1条记录和所有结果集记录的一个比率值,

JoinCard-Rounded:

51982Computed:

51982.00

FirstKRows:

K/Nratio=1/51982=0.000019237428341

通过这个K/Nratio,oracle会重新计算joincost

SINGLETABLEACCESSPATH（FirstKRows）

Table:

TAlias:

T

Card:

Original:

2Rounded:

2Computed:

2.00NonAdjusted:

2.00

AccessPath:

TableScan

Cost:

2.00Resp:

2.00Degree:

0

Cost_io:

2.00Cost_cpu:

7541

Resp_io:

2.00Resp_cpu:

7541

Best:

:

AccessPath:

TableScan

Cost:

2.00Degree:

1Resp:

2.0