VDB29.1版本执行计划的基值严重低估,统计信息最新且准确,做过distribution(30,30)操作,过滤因子通过公式计算准确。
请高手指点,如何解决?
截取部分执行计划(多个基值严重低估,从第9开始以上部分基值低估,第11以下基值准确,),具体如下:
为了便于高手分析,附上表信息,select count(*) 和表card统计信息一致。
表信息:
tabname CARD NPAGES
------------------------------ ----------------- --------------------
ODS_RESRUN.Q7 6402 19
ODS_SECP.Q3 1402459 4886
ODS_RESCONT.Q4 856071 1730
ODS_RESGROUP.Q8 42 1
ODS_RESCONTDEF.Q5 516300 1266
ODS_RESAGGR.Q6 1054489 4256
DIM_DATE.Q2 7440 22
执行计划如下:
5003.21
HSJOIN
( 9)
9189.61
5999
/---------+--------
857877 5907.99
TBSCAN NLJOIN
( 10) ( 11)
2791.68 6190.64
1734 4265
| /--------+-------
857877 1 5907.99
TABLE: MRODS ^NLJOIN TBSCAN
ODS_RESCONT ( 12) ( 15)
Q4 15.2765 6175.36
2 4263
/-----+----- |
1 1 1.0566e+06
TBSCAN IXSCAN TABLE: MRODS
( 13) ( 14) ODS_RESAGGR
7.67543 7.60109 Q6
1 1
| |
42 7440
TABLE: MRODS INDEX: MRRPT
ODS_RESGROUP XPK_DIM_DATE
Q8 Q2
9) HSJOIN: (Hash Join)
Cumulative Total Cost: 9189.61
Cumulative CPU Cost: 4.47702e+09
Cumulative I/O Cost: 5999
Cumulative Re-Total Cost: 9189.61
Cumulative Re-CPU Cost: 4.47702e+09
Cumulative Re-I/O Cost: 5999
Cumulative First Row Cost: 9189.61
Estimated Bufferpool Buffers: 1734
Arguments:
---------
BITFLTR : (Hash Join Bit Filter used)
5836
EARLYOUT: (Early Out flag)
NONE
HASHCODE: (Hash Code Size)
24 BIT
JN INPUT: (Join input leg)
INNER
TEMPSIZE: (Temporary Table Page Size)
32768
Predicates:
----------
4) Predicate used in Join
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.000134409
Predicate Text:
--------------
(Q4."DATA_DATE" = Q2."CALENDAR_DATE")
30) Predicate used in Join
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 9.87151e-07
Predicate Text:
--------------
(Q6."AGGRID" = Q4."AGGRID")
Input Streams:
-------------
8) From Operator #10
Estimated number of rows: 857877
Number of columns: 4
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q4."AMOUNT"+Q4."DATA_DATE"+Q4."CONTDEFID"
+Q4."AGGRID"
16) From Operator #11
Estimated number of rows: 5907.99
Number of columns: 16
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2."CALENDAR_DATE"+Q6."AGGR9"+Q6."AGGR8"
+Q6."AGGR7"+Q6."AGGR6"+Q6."AGGR5"+Q6."AGGR4"
+Q6."AGGR3"+Q6."AGGR2"+Q6."AGGR1"
+Q6."DATA_DATE"+Q6."AGGRID"+Q6."RUNID"
+Q8."CODE"+Q8."DATA_DATE"+Q8."GROUPID"
Output Streams:
--------------
17) To Operator #7
Estimated number of rows: 5003.21
Number of columns: 20
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2."CALENDAR_DATE"+Q4."AMOUNT"+Q4."DATA_DATE"
+Q4."CONTDEFID"+Q4."AGGRID"+Q6."AGGR9"
+Q6."AGGR8"+Q6."AGGR7"+Q6."AGGR6"+Q6."AGGR5"
+Q6."AGGR4"+Q6."AGGR3"+Q6."AGGR2"+Q6."AGGR1"
+Q6."DATA_DATE"+Q6."AGGRID"+Q6."RUNID"
+Q8."CODE"+Q8."DATA_DATE"+Q8."GROUPID"
收起