这条SQL CBO 计算出的E-ROW 与实际A-ROW有偏差，root cause是什么呢

orasupp

SELECT
       M.STND_CITY_ID,
       M.CITY_NME,
       M.COUNTY_NME,
       M.STATE_CDE,
       M.STATE_NME,
       M.CNTRY_CDE,
       M.CNTRY_NME
  FROM CS2_MASTER_CITY M, CARRIER_OFF_CONTROL_CITY C, BOOKING_OFFICE B
WHERE C.STND_CITY_ID = M.STND_CITY_ID
   AND C.OFFICE_ID = B.BOOKING_OFFICE_ID
   AND B.SERVICE_PROVIDER_ID =27
   AND UPPER(M.CITY_NME) LIKE '%';
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name                     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN               |                          |      1 | 39097 |   6973 |00:00:05.82 |   29202 |  28380 |
|*  2 |   HASH JOIN              |                          |      1 | 38703 |   6973 |00:00:00.01 |     378 |    371 |
|*  3 |    VIEW                  | index$_join$_003         |      1 |     79 |    105 |00:00:00.01 |       4 |      0 |
|*  4 |     HASH JOIN            |                          |      1 |        |    105 |00:00:00.01 |       4 |      0 |
|*  5 |      INDEX RANGE SCAN    | BOOKING_OFFICE_IDX2      |      1 |     79 |    105 |00:00:00.01 |       1 |      0 |
|   6 |      INDEX FAST FULL SCAN| BOOKING_OFFICE_PK        |      1 |     79 |    269 |00:00:00.01 |       3 |      0 |
|   7 |    TABLE ACCESS FULL     | CARRIER_OFF_CONTROL_CITY |      1 |  39061 |  39078 |00:00:00.79 |     374 |    371 |
|*  8 |   TABLE ACCESS FULL      | CS2_MASTER_CITY          |      1 |   2267K|   2269K|00:00:13.63 |   28824 |  28009 |
------------------------------------------------------------------------------------------------------------------------

实际返回6973行，但是CBO算出来39097行，其实加hit /*+ use_nl(B,C) use_nl(C,M) */ 强制CBO走NEST LOOP，CR 和CPU TIME 都要优于上面的HASH JOIN（详细请看SQL TRACE 文件）,但是就是不清楚为什么CBO会算错，对表也都做了分析。作了SQL TRACE和10053都没看出root  cause。

附近有SQL TRACE 原始文件和PKOCRF 文件，以及10053文件。

orasupp

请忽略上面的PLAN（上面的PLAN是我加了其他HIT),以下面的这个为准,这个是原来的PLAN,问题同样是上面的问题
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN               |                             |      1 |  39097 |   6973 |00:00:00.61 |   29035 |
|*  2 |   HASH JOIN              |                             |      1 |  38703 |   6973 |00:00:00.01 |     212 |
|*  3 |    VIEW                  | index$_join$_003            |      1 |     79 |    105 |00:00:00.01 |       4 |
|*  4 |     HASH JOIN            |                             |      1 |        |    105 |00:00:00.01 |       4 |
|*  5 |      INDEX RANGE SCAN    | BOOKING_OFFICE_IDX2         |      1 |     79 |    105 |00:00:00.01 |       1 |
|   6 |      INDEX FAST FULL SCAN| BOOKING_OFFICE_PK           |      1 |     79 |    269 |00:00:00.01 |       3 |
|   7 |    INDEX FAST FULL SCAN  | CARRIER_OFF_CONTROL_CITY_PK |      1 |  39061 |  39078 |00:00:00.01 |     208 |
|*  8 |   TABLE ACCESS FULL      | CS2_MASTER_CITY             |      1 |   2267K|   2269K|00:00:00.01 |   28823 |
------------------------------------------------------------------------------------------------------------------

不了峰

数据库  optimizer_dynamic_sampling 等多少

orasupp

SQL> show parameter optimizer_dynamic_sampling

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling           integer     2

TABLE上有统计信息,PARSE时不会选择dynamic sampling,具体看10053文件

Maclean Liu(刘相兵

我们以 10053 trace中的 best plan 为讨论的基准

1. ============
   
2. Plan Table
   
3. ============
   
4. ---------------------------------------------------------------+-----------------------------------+
   
5. | Id  | Operation                 | Name                       | Rows  | Bytes | Cost  | Time      |
   
6. ---------------------------------------------------------------+-----------------------------------+
   
7. | 0   | SELECT STATEMENT          |                            |       |       |  7587 |           |
   
8. | 1   |  HASH JOIN                |                            |   39K | 2428K |  7587 |  00:02:34 |
   
9. | 2   |   HASH JOIN               |                            |   38K |  687K |    34 |  00:00:01 |
   
10. | 3   |    VIEW                   | index$_join$_003           |   105 |   840 |     3 |  00:00:01 |
    
11. | 4   |     HASH JOIN             |                            |       |       |       |           |
    
12. | 5   |      INDEX RANGE SCAN     | BOOKING_OFFICE_IDX2        |   105 |   840 |     1 |  00:00:01 |
    
13. | 6   |      INDEX FAST FULL SCAN | BOOKING_OFFICE_PK          |   105 |   840 |     1 |  00:00:01 |
    
14. | 7   |    INDEX FAST FULL SCAN   | CARRIER_OFF_CONTROL_CITY_PK|   38K |  381K |    30 |  00:00:01 |
    
15. | 8   |   TABLE ACCESS FULL       | CS2_MASTER_CITY            | 2215K |   97M |  7543 |  00:02:34 |
    
16. ---------------------------------------------------------------+-----------------------------------+

复制代码

Maclean Liu(刘相兵

NL Join
join cost = cost of accessing outer table
+ (cardinality of outer table * cost of accessing inner table )


SM Join
join cost = (cost of accessing outer table + outer sort cost)
+ (cost of accessing inner table + inner sort cost)

HA Join
join cost = (cost of accessing outer table)
+ (cost of building hash table)
+ (cost of accessing inner table )

10053 trace显示 hard parse时 optimzier 考虑了多种JOIN Order 下的NL Join 、 SM join和 HASH Join 的Cost

选择 其中最好的一个 Join Order 来看：

Join order[1]:  BOOKING_OFFICE[B]#0  CARRIER_OFF_CONTROL_CITY[C]#1  CS2_MASTER_CITY[M]#2
***************
Now joining: CARRIER_OFF_CONTROL_CITY[C]#1
***************
NL Join
  Outer table: Card: 105.00  Cost: 2.76  Resp: 2.76  Degree: 1  Bytes: 8
  Inner table: CARRIER_OFF_CONTROL_CITY  Alias: C
  Access Path: TableScan
    NL Join:  Cost: 10565.90  Resp: 10565.90  Degree: 0
      Cost_io: 10526.25  Cost_cpu: 986507495
      Resp_io: 10526.25  Resp_cpu: 986507495
  Access Path: index (index (FFS))
    Index: CARRIER_OFF_CONTROL_CITY_PK
    resc_io: 28.72  resc_cpu: 5444233
    ix_sel: 0.0000e+00  ix_sel_with_filters: 1
  Inner table: CARRIER_OFF_CONTROL_CITY  Alias: C
  Access Path: index (FFS)
    NL Join:  Cost: 3041.73  Resp: 3041.73  Degree: 0
      Cost_io: 3018.25  Cost_cpu: 584245128
      Resp_io: 3018.25  Resp_cpu: 584245128
  Access Path: index (IndexOnly)
    Index: CARRIER_OFF_CONTROL_CITY_PK
    resc_io: 2.00  resc_cpu: 143293
    ix_sel: 0.016393  ix_sel_with_filters: 0.016393
    NL Join: Cost: 213.36  Resp: 213.36  Degree: 1
      Cost_io: 212.25  Cost_cpu: 27646453
      Resp_io: 212.25  Resp_cpu: 27646453
  Best NL cost: 213.36
          resc: 213.36 resc_io: 212.25 resc_cpu: 27646453
          resp: 213.36 resp_io: 212.25 resp_cpu: 27646453
Join Card:  39061.00 = outer (105.00) * inner (39061.00) * sel (0.0095238)
Join Card - Rounded: 39061 Computed: 39061.00
SM Join
  Outer table:
    resc: 2.76  card 105.00  bytes: 8  deg: 1  resp: 2.76
  Inner table: CARRIER_OFF_CONTROL_CITY  Alias: C
    resc: 30.22  card: 39061.00  bytes: 10  deg: 1  resp: 30.22
    using dmeth: 2  #groups: 1
    SORT resource      Sort statistics
      Sort width:        1841 Area size:     1048576 Max Area size:   322119680
      Degree:               1
      Blocks to Sort:       1 Row size:           19 Total Rows:            105
      Initial runs:         1 Merge passes:        0 IO Cost / pass:          0
      Total IO sort cost: 0      Total CPU sort cost: 24910968
      Total Temp space used: 0
    SORT resource      Sort statistics
      Sort width:        1841 Area size:     1048576 Max Area size:   322119680
      Degree:               1
      Blocks to Sort:     101 Row size:           21 Total Rows:          39061
      Initial runs:         1 Merge passes:        0 IO Cost / pass:          0
      Total IO sort cost: 0      Total CPU sort cost: 51723377
      Total Temp space used: 0
  SM join: Resc: 36.06  Resp: 36.06  [multiMatchCost=0.00]
  SM cost: 36.06
     resc: 36.06 resc_io: 32.25 resc_cpu: 94679278
     resp: 36.06 resp_io: 32.25 resp_cpu: 94679278
HA Join
  Outer table:
    resc: 2.76  card 105.00  bytes: 8  deg: 1  resp: 2.76
  Inner table: CARRIER_OFF_CONTROL_CITY  Alias: C
    resc: 30.22  card: 39061.00  bytes: 10  deg: 1  resp: 30.22
    using dmeth: 2  #groups: 1
    Cost per ptn: 0.66  #ptns: 1
    hash_area: 256 (max=78643)   Hash join: Resc: 33.63  Resp: 33.63  [multiMatchCost=0.00]
  HA cost: 33.63
     resc: 33.63 resc_io: 32.25 resc_cpu: 34406386
     resp: 33.63 resp_io: 32.25 resp_cpu: 34406386
Best:: JoinMethod: Hash
       Cost: 33.63  Degree: 1  Resp: 33.63  Card: 39061.00  Bytes: 18
***************
Now joining: CS2_MASTER_CITY[M]#2
***************
NL Join
  Outer table: Card: 39061.00  Cost: 33.63  Resp: 33.63  Degree: 1  Bytes: 18
  Inner table: CS2_MASTER_CITY  Alias: M
  Access Path: TableScan
    NL Join:  Cost: 294613761.04  Resp: 294613761.04  Degree: 0
      Cost_io: 292488801.25  Cost_cpu: 52867310206286
      Resp_io: 292488801.25  Resp_cpu: 52867310206286
  Access Path: index (UniqueScan)
    Index: CS2_MASTER_CITY_PK
    resc_io: 2.00  resc_cpu: 16653
    ix_sel: 4.4096e-07  ix_sel_with_filters: 4.4096e-07
    NL Join: Cost: 78181.78  Resp: 78181.78  Degree: 1
      Cost_io: 78154.25  Cost_cpu: 684884532
      Resp_io: 78154.25  Resp_cpu: 684884532
  Access Path: index (AllEqUnique)
    Index: CS2_MASTER_CITY_PK
    resc_io: 2.00  resc_cpu: 16653
    ix_sel: 4.4545e-07  ix_sel_with_filters: 4.4545e-07
    NL Join: Cost: 78181.78  Resp: 78181.78  Degree: 1
      Cost_io: 78154.25  Cost_cpu: 684884532
      Resp_io: 78154.25  Resp_cpu: 684884532
  Best NL cost: 78181.78
          resc: 78181.78 resc_io: 78154.25 resc_cpu: 684884532
          resp: 78181.78 resp_io: 78154.25 resp_cpu: 684884532
Join Card:  39458.69 = outer (39061.00) * inner (2267766.00) * sel (4.4545e-07)
Join Card - Rounded: 39459 Computed: 39458.69
SM Join
  Outer table:
    resc: 33.63  card 39061.00  bytes: 18  deg: 1  resp: 33.63
  Inner table: CS2_MASTER_CITY  Alias: M
    resc: 7543.40  card: 2267766.00  bytes: 45  deg: 1  resp: 7543.40
    using dmeth: 2  #groups: 1
    SORT resource      Sort statistics
      Sort width:        1841 Area size:     1048576 Max Area size:   322119680
      Degree:               1
      Blocks to Sort:     144 Row size:           30 Total Rows:          39061
      Initial runs:         2 Merge passes:        1 IO Cost / pass:         78
      Total IO sort cost: 222      Total CPU sort cost: 55269521
      Total Temp space used: 2221000
    SORT resource      Sort statistics
      Sort width:        1841 Area size:     1048576 Max Area size:   322119680
      Degree:               1
      Blocks to Sort:   16659 Row size:           60 Total Rows:        2267766
      Initial runs:         2 Merge passes:        1 IO Cost / pass:       9026
      Total IO sort cost: 25685      Total CPU sort cost: 2592290507
      Total Temp space used: 292578000
  SM join: Resc: 33590.45  Resp: 33590.45  [multiMatchCost=0.00]
  SM cost: 33590.45
     resc: 33590.45 resc_io: 33428.25 resc_cpu: 4035420647
     resp: 33590.45 resp_io: 33428.25 resp_cpu: 4035420647
HA Join
  Outer table:
    resc: 33.63  card 39061.00  bytes: 18  deg: 1  resp: 33.63
  Inner table: CS2_MASTER_CITY  Alias: M
    resc: 7543.40  card: 2267766.00  bytes: 45  deg: 1  resp: 7543.40
    using dmeth: 2  #groups: 1
    Cost per ptn: 9.85  #ptns: 1
    hash_area: 256 (max=78643)   Hash join: Resc: 7586.88  Resp: 7586.88  [multiMatchCost=0.00]
  HA cost: 7586.88
     resc: 7586.88 resc_io: 7521.25 resc_cpu: 1632935972
     resp: 7586.88 resp_io: 7521.25 resp_cpu: 1632935972
Best:: JoinMethod: Hash
       Cost: 7586.88  Degree: 1  Resp: 7586.88  Card: 39458.69  Bytes: 63


BOOKING_OFFICE Nest Join  CARRIER_OFF_CONTROL_CITY  的最优成本是 213.36
(card 105 cost:2.76 )   NL        (CARRIER_OFF_CONTROL_CITY_PK  cost 2.00)

=> Nested Loop Cost=   2.76 +  2 * 105 =  213

NL cost 公式为
Cost of acquiring data from first table +
Cardinality of result from first table * Cost of single visit to second table
而

BOOKING_OFFICE  Hash Join CARRIER_OFF_CONTROL_CITY 的 最优成本是  33.63
(card 105 cost:2.76 )       Hash  Join ( FFS CARRIER_OFF_CONTROL_CITY_PK  card: 39061.00 cost:30.22)

==>  Hash Join Cost  = 2.76 + 30.22 +  (cost of building hash table)= 33.63

HA Join
join cost = (cost of accessing outer table)
+ (cost of building hash table)
+ (cost of accessing inner table )


所以 BOOKING_OFFICE join  CARRIER_OFF_CONTROL_CITY 的最佳计划 是


BOOKING_OFFICE  Hash Join CARRIER_OFF_CONTROL_CITY(FFS CARRIER_OFF_CONTROL_CITY_PK)

成本为 33.63   card  ， join后的card 为39061.00

Maclean Liu(刘相兵

之后再 Join  CS2_MASTER_CITY[M]#2 表

Now joining: CS2_MASTER_CITY[M]#2
***************
NL Join
  Outer table: Card: 39061.00  Cost: 33.63  Resp: 33.63  Degree: 1  Bytes: 18
  Inner table: CS2_MASTER_CITY  Alias: M
  Access Path: TableScan
    NL Join:  Cost: 294613761.04  Resp: 294613761.04  Degree: 0
      Cost_io: 292488801.25  Cost_cpu: 52867310206286
      Resp_io: 292488801.25  Resp_cpu: 52867310206286
  Access Path: index (UniqueScan)
    Index: CS2_MASTER_CITY_PK
    resc_io: 2.00  resc_cpu: 16653
    ix_sel: 4.4096e-07  ix_sel_with_filters: 4.4096e-07
    NL Join: Cost: 78181.78  Resp: 78181.78  Degree: 1
      Cost_io: 78154.25  Cost_cpu: 684884532
      Resp_io: 78154.25  Resp_cpu: 684884532
  Access Path: index (AllEqUnique)
    Index: CS2_MASTER_CITY_PK
    resc_io: 2.00  resc_cpu: 16653
    ix_sel: 4.4545e-07  ix_sel_with_filters: 4.4545e-07
    NL Join: Cost: 78181.78  Resp: 78181.78  Degree: 1
      Cost_io: 78154.25  Cost_cpu: 684884532
      Resp_io: 78154.25  Resp_cpu: 684884532
  Best NL cost: 78181.78
          resc: 78181.78 resc_io: 78154.25 resc_cpu: 684884532
          resp: 78181.78 resp_io: 78154.25 resp_cpu: 684884532


若使用 NL join CS2_MASTER_CITY 最佳的 cost为 78181.78

其成本的计算过程为

NL join Cost= 33.63 + 39061*   2.00= 78189.26 约等于  78181.78

这里的 2.00 是  一次 Index: CS2_MASTER_CITY_PK AllEqUnique的成本

Join Card:  39458.69 = outer (39061.00) * inner (2267766.00) * sel (4.4545e-07)
Join Card - Rounded: 39459 Computed: 39458.69

这里 NL 算得的最后 card 是 39458.69， 基于索引选择率 sel (4.4545e-07)

注意这个sel (4.4545e-07) 是由   Density: 4.4545e-07获得的


Table Stats::
  Table: CS2_MASTER_CITY  Alias:  M
    #Rows: 2267766  #Blks:  27648  AvgRowLen:  120.00
  Column (#1): STND_CITY_ID(NUMBER)
    AvgLen: 6.00 NDV: 2244910 Nulls: 0 Density: 4.4545e-07 Min: 528 Max: 2554187

这里的 密度  Density: 4.4545e-07 = 1/NDV= 1/2244910  



而HASH Join  CS2_MASTER_CITY的最佳成本是 7586.88


HA Join
  Outer table:
    resc: 33.63  card 39061.00  bytes: 18  deg: 1  resp: 33.63
  Inner table: CS2_MASTER_CITY  Alias: M
    resc: 7543.40  card: 2267766.00  bytes: 45  deg: 1  resp: 7543.40
    using dmeth: 2  #groups: 1
    Cost per ptn: 9.85  #ptns: 1
    hash_area: 256 (max=78643)   Hash join: Resc: 7586.88  Resp: 7586.88  [multiMatchCost=0.00]
  HA cost: 7586.88
     resc: 7586.88 resc_io: 7521.25 resc_cpu: 1632935972
     resp: 7586.88 resp_io: 7521.25 resp_cpu: 1632935972
Best:: JoinMethod: Hash
       Cost: 7586.88  Degree: 1  Resp: 7586.88  Card: 39458.69  Bytes: 63

其成本的计算过程为

HASH Join  Cost =  33.63+ 7543.40+ (cost of building hash table) =  7586.88

***********************
Best so far: Table#: 0  cost: 2.7565  card: 105.0000  bytes: 840
             Table#: 1  cost: 33.6329  card: 39061.0000  bytes: 703098
             Table#: 2  cost: 7586.8846  card: 39458.6900  bytes: 2485917


所以最后oracle优化器 optimizer选择了  具体最佳best cost 的 plan

BOOKING_OFFICE( INDEX RANGE SCAN     | BOOKING_OFFICE_IDX2)  HASH JOIN (CARRIER_OFF_CONTROL_CITY INDEX FAST FULL SCAN   | CARRIER_OFF_CONTROL_CITY_PK ) HASH JOIN (CS2_MASTER_CITY FULL TABLE SCAN)这样的执行计划




考虑收集 CARRIER_OFF_CONTROL_CITY  表上 OFFICE_ID  STND_CITY_ID 列的 histogram 和  CS2_MASTER_CITY  STND_CITY_ID的histogram

orasupp

对BOOKING_OFFICE  CARRIER_OFF_CONTROL_CITY NESTLOOP有疑问:
Best NL cost: 213.36
          resc: 213.36 resc_io: 212.25 resc_cpu: 27646453
          resp: 213.36 resp_io: 212.25 resp_cpu: 27646453
Join Card:  39061.00 = outer (105.00) * inner (39061.00) * sel (0.0095238)-----------39061的cardinality 显然算错了,这个selectivity怎么得来的?这应该是CBO选错PLAN的根本原因吧?

  Access Path: index(AllEqUnique) --------- AllEqUniquek这个是什么意思呢?

orasupp

从TRACE 文件中可以看出,加了/*+use_nl(B,C) use_nl(C,M) */ PLAN 要比CBO 自己算出来的BEST PLAN COST 要低.


Best plan in 10053:(actualplan)


call
count
cpu
elapsed
disk
query
current
rows
------- ------
-------- ---------- ---------- --------------------
----------
Parse
1
0.00
0.00
0
0
0
0
Execute
1
0.00
0.00
0
0
0
0
Fetch
466
1.83
7.78
14110
29036
0
6973
------- ------
-------- ---------- ---------- --------------------
----------
total
468
1.83
7.78
14110
29036
0
6973


Misses in library cacheduring parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS


Rows
Row Source Operation
-------
---------------------------------------------------

6973
HASHJOIN
(cr=29036 pr=14110 pw=0time=3419985 us)

6973
HASH JOIN
(cr=212 pr=203 pw=0time=54761 us)

105
VIEW
index$_join$_003 (cr=4 pr=3pw=0 time=26448 us)

105
HASH JOIN
(cr=4 pr=3 pw=0time=26342 us)

105
INDEX RANGE SCAN BOOKING_OFFICE_IDX2 (cr=1 pr=1 pw=0 time=13695us)(object id 85978)

269
INDEX FAST FULL SCAN BOOKING_OFFICE_PK (cr=3 pr=2 pw=0 time=11235us)(object id 102402)

39078
INDEX FAST FULL SCAN CARRIER_OFF_CONTROL_CITY_PK (cr=208 pr=200 pw=0time=27169 us)(object id 102649)
2269666
TABLE ACCESS FULL CS2_MASTER_CITY (cr=28824pr=13907 pw=0 time=6831275 us)




Added hint to force usingNESTLOOP:


call
count
cpu
elapsed
disk
query
current
rows
------- ------
-------- ---------- ---------- --------------------
----------
Parse
1
0.00
0.01
0
0
0
0
Execute
1
0.00
0.00
0
0
0
0
Fetch
466
0.33
6.66
1704

28589
0
6973
------- ------
-------- ---------- ---------- --------------------
----------
total
468
0.34
6.67
1704
28589
0
6973


Misses in library cacheduring parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS


Rows
Row Source Operation
-------
---------------------------------------------------

6973
NESTED LOOPS
(cr=28589 pr=1704pw=0 time=3272542 us)

6973
NESTED LOOPS
(cr=696 pr=0 pw=0time=14948 us)

105
VIEW
index$_join$_003 (cr=45 pr=0pw=0 time=2556 us)

105
HASH JOIN
(cr=45 pr=0 pw=0time=992 us)

105
INDEX RANGE SCAN BOOKING_OFFICE_IDX2 (cr=1 pr=0 pw=0 time=112 us)(objectid 85978)

269
INDEX FAST FULL SCAN BOOKING_OFFICE_PK (cr=44 pr=0 pw=0 time=35us)(object id 102402)

6973
INDEX RANGE SCAN CARRIER_OFF_CONTROL_CITY_PK (cr=651 pr=0 pw=0time=14525 us)(object id 102649)

6973
TABLE ACCESS BY INDEX ROWID CS2_MASTER_CITY (cr=27893 pr=1704 pw=0time=6634821 us)

6973
INDEX UNIQUE SCAN CS2_MASTER_CITY_PK (cr=20919 pr=796 pw=0 time=2703629us)(object id 53896)

Maclean Liu(刘相兵

C.OFFICE_ID = B.BOOKING_OFFICE_ID


CARRIER_OFF_CONTROL_CITY C.OFFICE_ID                                    NDV: 61
BOOKING_OFFICE                           B.BOOKING_OFFICE_ID               NDV: 266  ==>  NDV : 105

Join Selectivity =
((num_rows(t1) - num_nulls(t1.c1)) / num_rows(t1)) *
((num_rows(t2) - num_nulls(t2.c2)) / num_rows(t2)) /
greater(num_distinct(t1.c1), num_distinct(t2.c2))


=  1/  greater  (   B.BOOKING_OFFICE_ID NDV, C.OFFICE_ID NDV: 61) =  1/greater( 105, 61) = 1/105 = 0.0095238

orasupp

明白了CBO怎么算出来这个结果,但是实际上C.OFFICE_ID = B.BOOKING_OFFICE_ID join 的 cardinality应该是6973,有什么方法可以纠正CBO呢?按照建议收集了histogram,cardinality依然不对
/*
考虑收集 CARRIER_OFF_CONTROL_CITY  表上 OFFICE_ID  STND_CITY_ID 列的 histogram 和  CS2_MASTER_CITY  STND_CITY_ID的histogram
*/
SQL> SELECT count(*)
  2    FROM CARRIER_OFF_CONTROL_CITY C, BOOKING_OFFICE B
  3   WHERE C.OFFICE_ID = B.BOOKING_OFFICE_ID
  4     AND B.SERVICE_PROVIDER_ID = 27;

  COUNT(*)
----------
      6973