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