ImageVerifierCode 换一换
格式:DOCX , 页数:20 ,大小:20.02KB ,
资源ID:2646334      下载积分:1 金币
快捷下载
登录下载
邮箱/手机:
温馨提示:
快捷下载时,用户名和密码都是您填写的邮箱或者手机号,方便查询和重复下载(系统自动生成)。 如填写123,账号就是123,密码也是123。
特别说明:
请自助下载,系统不会自动发送文件的哦; 如果您已付费,想二次下载,请登录后访问:我的下载记录
支付方式: 支付宝    微信支付   
验证码:   换一换

加入VIP,免费下载
 

温馨提示:由于个人手机设置不同,如果发现不能下载,请复制以下地址【https://www.bingdoc.com/d-2646334.html】到电脑端继续下载(重复下载不扣费)。

已注册用户请登录:
账号:
密码:
验证码:   换一换
  忘记密码?
三方登录: 微信登录   QQ登录  

下载须知

1: 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。
2: 试题试卷类文档,如果标题没有明确说明有答案则都视为没有答案,请知晓。
3: 文件的所有权益归上传用户所有。
4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
5. 本站仅提供交流平台,并不能对任何下载内容负责。
6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。

版权提示 | 免责声明

本文(firstrowsn和allrows在性能上的区别.docx)为本站会员(b****1)主动上传,冰点文库仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对上载内容本身不做任何修改或编辑。 若此文所含内容侵犯了您的版权或隐私,请立即通知冰点文库(发送邮件至service@bingdoc.com或直接QQ联系客服),我们立即给予删除!

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

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

copyright@ 2008-2023 冰点文库 网站版权所有

经营许可证编号:鄂ICP备19020893号-2