920的CBO还是不够'聪明'

husthxd

cbo选择的执行计划:

***********************
Join order[3]:  YL_LTXJBXX[B]#1  YL_DYXMBD[A]#3  TB_GRJBXX[A]#2  TB_DWJBXX[B]#0
Now joining: YL_DYXMBD[A]#3 *******
NL Join
  Outer table: cost: 328  cdn: 244  rcz: 20  resp:  328
  Inner table: YL_DYXMBD
    Access path: tsc  Resc: 151
    Join:  Resc:  37172  Resp:  37172
  Access path: index (unique)
      Index: SYS_C007434
  TABLE: YL_DYXMBD
      RSC_CPU: 0   RSC_IO: 1
  IX_SEL:  3.2178e-06  TB_SEL:  3.2178e-06
    Join:  resc: 450  resp: 450
  Access path: index (eq-unique)
      Index: SYS_C007434
  TABLE: YL_DYXMBD
      RSC_CPU: 0   RSC_IO: 1
  IX_SEL:  0.0000e+00  TB_SEL:  0.0000e+00
    Join:  resc: 450  resp: 450
  Best NL cost: 450  resp: 450
Join cardinality:  244 = outer (244) * inner (310767) * sel (3.2178e-06)  [flag=0]
SM Join
  Outer table:
    resc: 328  cdn: 244  rcz: 20  deg: 1  resp: 328
  Inner table: YL_DYXMBD
    resc: 151  cdn: 310767  rcz: 38  deg:  1  resp: 151
    using join:1 distribution:2 #groups:1
    SORT resource      Sort statistics
      Sort width:           14 Area size:      712704 Max Area size:      712704   Degree: 1
      Blocks to Sort:        1 Row size:           32 Rows:        244
      Initial runs:          1 Merge passes:        1 IO Cost / pass:          3
      Total IO sort cost: 2
      Total CPU sort cost: 0
      Total Temp space used: 0
    SORT resource      Sort statistics
      Sort width:           14 Area size:      712704 Max Area size:      712704   Degree: 1
      Blocks to Sort:      988 Row size:           52 Rows:     310767
      Initial runs:         23 Merge passes:        2 IO Cost / pass:       1130
      Total IO sort cost: 1624
      Total CPU sort cost: 0
      Total Temp space used: 37471000
  Merge join  Cost:  2105  Resp:  2105
SM Join (with index on outer)
  Access path: index (no sta/stp keys)
      Index: SYS_C007420
  TABLE: YL_LTXJBXX
      RSC_CPU: 0   RSC_IO: 225044
  IX_SEL:  1.0000e+00  TB_SEL:  1.0000e+00
  Outer table:
    resc: 112522  cdn: 244  rcz: 20  deg: 1  resp: 112522
  Inner table: YL_DYXMBD
    resc: 151  cdn: 310767  rcz: 38  deg:  1  resp: 151
    using join:1 distribution:2 #groups:1
    SORT resource      Sort statistics
      Sort width:           14 Area size:      712704 Max Area size:      712704   Degree: 1
      Blocks to Sort:      988 Row size:           52 Rows:     310767
      Initial runs:         23 Merge passes:        2 IO Cost / pass:       1130
      Total IO sort cost: 1624
      Total CPU sort cost: 0
      Total Temp space used: 37471000
  Merge join  Cost:  114297  Resp:  114297
HA Join
  Outer table:
    resc: 328  cdn: 244  rcz: 20  deg: 1  resp: 328
  Inner table: YL_DYXMBD
    resc: 151  cdn: 310767  rcz: 38  deg:  1  resp: 151
    using join:8 distribution:2 #groups:1
  Hash join one ptn Resc: 4   Deg: 1
      hash_area:  128 (max=128)  buildfrag:  129                probefrag:   949 ppasses:    2
  Hash join   Resc: 483   Resp: 483
Join result: cost: 450  cdn: 244  rcz: 58
Now joining: TB_GRJBXX[A]#2 *******
NL Join
  Outer table: cost: 450  cdn: 244  rcz: 58  resp:  450
  Inner table: TB_GRJBXX
    Access path: tsc  Resc: 2470
    Join:  Resc:  603130  Resp:  603130
  Access path: index (iff)
      Index: IDX_TB_GRJBXX_GRBH_RYZT
  TABLE: TB_GRJBXX
      RSC_CPU: 0   RSC_IO: 410
  IX_SEL:  0.0000e+00  TB_SEL:  1.0000e+00
  Inner table: TB_GRJBXX
    Access path: iff  Resc: 410
    Join:  Resc:  100490  Resp:  100490
  Access path: index (unique)
      Index: SYS_C007670
  TABLE: TB_GRJBXX
      RSC_CPU: 0   RSC_IO: 2
  IX_SEL:  6.1623e-07  TB_SEL:  6.1623e-07
    Join:  resc: 694  resp: 694
  Access path: index (join stp)
      Index: IDX_TB_GRJBXX_DWBH
  TABLE: TB_GRJBXX
      RSC_CPU: 0   RSC_IO: 613
  IX_SEL:  0.0000e+00  TB_SEL:  2.5295e-03
    Join:  resc: 75236  resp: 75236
  Access path: index (index-only)
      Index: IDX_TB_GRJBXX_GRBH_RYZT
  TABLE: TB_GRJBXX
      RSC_CPU: 0   RSC_IO: 2
  IX_SEL:  1.5588e-09  TB_SEL:  1.5588e-09
    Join:  resc: 694  resp: 694
  Access path: index (eq-unique)
      Index: SYS_C007670
  TABLE: TB_GRJBXX
      RSC_CPU: 0   RSC_IO: 2
  IX_SEL:  0.0000e+00  TB_SEL:  0.0000e+00
    Join:  resc: 694  resp: 694
  Best NL cost: 694  resp: 694
Join cardinality:  3 = outer (244) * inner (4105) * sel (3.2178e-06)  [flag=0]
SM Join
  Outer table:
    resc: 450  cdn: 244  rcz: 58  deg: 1  resp: 450
  Inner table: TB_GRJBXX
    resc: 6  cdn: 4105  rcz: 22  deg:  1  resp: 6
    using join:1 distribution:2 #groups:1
    SORT resource      Sort statistics
      Sort width:           14 Area size:      712704 Max Area size:      712704   Degree: 1
      Blocks to Sort:        2 Row size:           74 Rows:        244
      Initial runs:          1 Merge passes:        1 IO Cost / pass:          4
      Total IO sort cost: 3
      Total CPU sort cost: 0
      Total Temp space used: 0
    SORT resource      Sort statistics
      Sort width:           14 Area size:      712704 Max Area size:      712704   Degree: 1
      Blocks to Sort:        9 Row size:           35 Rows:       4105
      Initial runs:          1 Merge passes:        1 IO Cost / pass:         11
      Total IO sort cost: 10
      Total CPU sort cost: 0
      Total Temp space used: 0
  Merge join  Cost:  470  Resp:  470
HA Join
  Outer table:
    resc: 450  cdn: 244  rcz: 58  deg: 1  resp: 450
  Inner table: TB_GRJBXX
    resc: 6  cdn: 4105  rcz: 22  deg:  1  resp: 6
    using join:8 distribution:2 #groups:1
  Hash join one ptn Resc: 1   Deg: 1
      hash_area:  128 (max=128)  buildfrag:  129                probefrag:   9 ppasses:    2
  Hash join   Resc: 458   Resp: 458
Join result: cost: 458  cdn: 3  rcz: 80
Now joining: TB_DWJBXX[B]#0 *******
NL Join
  Outer table: cost: 458  cdn: 3  rcz: 80  resp:  458
  Inner table: TB_DWJBXX
    Access path: tsc  Resc: 35
    Join:  Resc:  562  Resp:  562
  Access path: index (iff)
      Index: IDX_TB_DWJBXX_DWBH_DWZT
  TABLE: TB_DWJBXX
      RSC_CPU: 0   RSC_IO: 3
  IX_SEL:  0.0000e+00  TB_SEL:  1.0000e+00
  Inner table: TB_DWJBXX
    Access path: iff  Resc: 3
    Join:  Resc:  466  Resp:  466
  Access path: index (index-only)
      Index: IDX_TB_DWJBXX_DWBH_DWZT
  TABLE: TB_DWJBXX
      RSC_CPU: 0   RSC_IO: 1
  IX_SEL:  8.8472e-05  TB_SEL:  8.8472e-05
    Join:  resc: 459  resp: 459
  Best NL cost: 459  resp: 459
Outer join cardinality:  3 = max ( outer (3) , (outer (3) * inner (1) * sel (8.8472e-05) )  [flag=16]
SM Join
  Outer table:
    resc: 458  cdn: 3  rcz: 80  deg: 1  resp: 458
  Inner table: TB_DWJBXX
    resc: 1  cdn: 1  rcz: 11  deg:  1  resp: 1
    using join:1 distribution:2 #groups:1
    SORT resource      Sort statistics
      Sort width:           14 Area size:      712704 Max Area size:      712704   Degree: 1
      Blocks to Sort:        1 Row size:           23 Rows:          1
      Initial runs:          1 Merge passes:        1 IO Cost / pass:          3
      Total IO sort cost: 2
      Total CPU sort cost: 0
      Total Temp space used: 0
  Merge join  Cost:  460  Resp:  460
HA Join
  Outer table:
    resc: 458  cdn: 3  rcz: 80  deg: 1  resp: 458
  Inner table: TB_DWJBXX
    resc: 1  cdn: 1  rcz: 11  deg:  1  resp: 1
    using join:8 distribution:2 #groups:1
  Hash join one ptn Resc: 1   Deg: 1
      hash_area:  128 (max=128)  buildfrag:  129                probefrag:   1 ppasses:    2
  Hash join   Resc: 460   Resp: 460
Join result: cost: 459  cdn: 3  rcz: 91
Best so far: TABLE#: 1  CST:        328  CDN:        244  BYTES:       4880
Best so far: TABLE#: 3  CST:        450  CDN:        244  BYTES:      14152
Best so far: TABLE#: 2  CST:        458  CDN:          3  BYTES:        240
Best so far: TABLE#: 0  CST:        459  CDN:          3  BYTES:        273
***********************

husthxd

跟rule一致的执行计划应该是:

Join order[8]:  TB_GRJBXX[A]#2  YL_DYXMBD[A]#3  YL_LTXJBXX[B]#1  TB_DWJBXX[B]#0
Now joining: YL_LTXJBXX[B]#1 *******
NL Join
  Outer table: cost: 190  cdn: 4105  rcz: 60  resp:  190
  Inner table: YL_LTXJBXX
    Access path: tsc  Resc: 328
    Join:  Resc:  1346630  Resp:  1346630
  Access path: index (unique)
      Index: SYS_C007420
  TABLE: YL_LTXJBXX
      RSC_CPU: 0   RSC_IO: 1
  IX_SEL:  3.2126e-06  TB_SEL:  3.2126e-06
    Join:  resc: 2242  resp: 2242
  Access path: index (eq-unique)
      Index: SYS_C007420
  TABLE: YL_LTXJBXX
      RSC_CPU: 0   RSC_IO: 1
  IX_SEL:  0.0000e+00  TB_SEL:  0.0000e+00
    Join:  resc: 2242  resp: 2242
  Best NL cost: 2242  resp: 2242
Join cardinality:  3 = outer (4105) * inner (244) * sel (3.2178e-06)  [flag=0]
SM Join
  Outer table:
    resc: 190  cdn: 4105  rcz: 60  deg: 1  resp: 190
  Inner table: YL_LTXJBXX
    resc: 328  cdn: 244  rcz: 20  deg:  1  resp: 328
    using join:1 distribution:2 #groups:1
    SORT resource      Sort statistics
      Sort width:           14 Area size:      712704 Max Area size:      712704   Degree: 1
      Blocks to Sort:        1 Row size:           32 Rows:        244
      Initial runs:          1 Merge passes:        1 IO Cost / pass:          3
      Total IO sort cost: 2
      Total CPU sort cost: 0
      Total Temp space used: 0
  Merge join  Cost:  520  Resp:  520
HA Join
  Outer table:
    resc: 190  cdn: 4105  rcz: 60  deg: 1  resp: 190
  Inner table: YL_LTXJBXX
    resc: 328  cdn: 244  rcz: 20  deg:  1  resp: 328
    using join:8 distribution:2 #groups:1
  Hash join one ptn Resc: 4   Deg: 1   (sides swapped)
      hash_area:  128 (max=128)  buildfrag:  129                probefrag:   1 ppasses:    2
  Hash join   Resc: 522   Resp: 522
  Outer table:
    resc: 328  cdn: 244  rcz: 20  deg: 1  resp: 328
  Inner table: YL_DYXMBD
    resc: 190  cdn: 4105  rcz: 60  deg:  1  resp: 190
    using join:8 distribution:2 #groups:1
  Hash join one ptn Resc: 1   Deg: 1
      hash_area:  128 (max=128)  buildfrag:  129                probefrag:   19 ppasses:    2
  Hash join   Resc: 518   Resp: 518
***********************

fenghaojie

rbo是可以清楚明白的应用在你熟悉的业务上,但是这需要dba的大量工作量为保证,这不适合工业化大生产,这也就是说得看在什么条件下,1个dba管1个库,而且自己对业务很清楚,那么要cbo干吗啊,但如果这个条件不成立,1个人管多个库,而且业务很多,很复杂,你给我都来个rbo试试看.并不是说CBO不聪明,其实CBO要比rbo不知聪明多少倍,之所以cbo不能采用效率更高的执行计划的原因是由于没有给cbo正确反映db实际的数据,而且还有很多参数影响着cbo的执行计划的选择,所以cbo要比rbo要麻烦一些.让他做到任何时候都那么可靠,就得多做些工作,很多地方还要dba参与

husthxd

1.统计信息不能说最新但至少是近期的(一个星期收集一次)
2.技术的进步更多的是提高生产效率,而不是需要更多的人力物力.如果想获取聪明的cbo就需要更聪明的dba来参与的话,那这种进步体现在何处?而且只需要把优化器模式改为rule就可以达到我想达到的目的,那么又何苦劳心劳力的说服客户修改sql并且需要花更大的力气才能调整的目的?

yangtingkun

可以试试收集完全的统计信息包括INDEXED COLUMNS。
看看CBO能否得出正确的判断。

hendytai

个人感觉相对于rbo,cbo确实有进步之处，比如如果查询返回的数量量比较大，在cbo下用hash算法一般效率会比较高
但cbo确实比较复杂，尤其在目录还不是很完善的情况下，所以有时感觉用rbo更方便，因为rbo的规则相对简单，没有cbo复杂

所以，我认为只是想解决某些问题，rbo能实现的就用rbo
但要想你的sql能在各种条件下都能高效运行，则cbo是必要的,因rbo太单一

husthxd

最初由 yangtingkun 发布
[B]可以试试收集完全的统计信息包括INDEXED COLUMNS。
看看CBO能否得出正确的判断。 [/B]

呵呵,已经不在现场了.
使用了'执行计划稳定性'

yangtingkun

感觉outline好用吗？
我也想试试呢。

husthxd

发现使用outline是一种不需要改变sql但又能控制sql执行计划的一种方法.
没有详细研究.