# stat

## 前提

优化器在计划优化阶段会自动获取基表的行数。但是一些特殊类型的表行数估算并不准确。  
我们可以通过STAT的方法，手动设置表行数，去了解变化后的表大小对执行计划的影响。

## 语法说明

/*+STAT(表名, 行数) */  
统计信息提示只能针对基表设置，视图和派生表等对象设置无效。如果表对象存在别名则必须使用别名。行数只能使用整数，或者整数+K（千），整数+M（百万），整数+G（十亿）。
行数提示设置后，统计信息的其它内容也会做相应的调整。

## 实例

原始sql
```
select
        *
from
        (
                SELECT
                        b.BRANCH_NAME2,
                        b.NOCAR_PREMIUM_VALUE NOCAR_PREMIUM_VALUE
                FROM
                        (
                                SELECT
                                        a.BRANCH_NAME2,
                                        sum(a.NOCAR_PREMIUM_VALUE) NOCAR_PREMIUM_VALUE
                                from
                                        (
                                                select
                                                        dt.BRANCH_NAME2,
                                                        pt.NOCAR_PREMIUM_VALUE
                                                from
                                                        (
                                                                select
                                                                        t33097.BRANCH_ID2 t33097BRANCH_ID2,
                                                                        t33097.BRANCH_NAME2 BRANCH_NAME2
                                                                from
                                                                        SOMEUSER.T_TABLE2 t33097,
                                                                        SOMEUSER.T_TABLE3 t16323
                                                                where
                                                                        t33097.BRANCH_NAME1 in ('xxx ')
                                                                    and t16323.TERM_DATE    >= '2021-01-01'
                                                                    and t16323.TERM_DATE    <= '2021-10-10'
                                                                    and t33097.BRANCH_ID1   in ( '1021' )
                                                                GROUP BY
                                                                        t33097.BRANCH_ID2,
                                                                        t33097.BRANCH_NAME2
                                                        )
                                                        dt
                                                left join
                                                        (
                                                                select
                                                                        t15323.BRANCH_ID2,
                                                                        0 NOCAR_PREMIUM_VALUE
                                                                from
                                                                        SOMEUSER.EA_CM_ALLCOUNT_MDATE t15323
                                                                where
                                                                        t15323.TERM_DATE >= '2021-01-01'
                                                                    and t15323.TERM_DATE <= '2021-10-10'
                                                                group by
                                                                        t15323.BRANCH_ID2
                                                        )
                                                        pt
                                                on
                                                        pt.BRANCH_ID2 = dt.t33097BRANCH_ID2
                                                
                                                UNION ALL
                                                
                                                select 
                                                        t33097.BRANCH_NAME2 BRANCH_NAME2,
                                                        t15285.NOCAR_PREMIUM_VALUE
                                                from
                                                        SOMEUSER.T_TABLE1 t15285
                                                left join SOMEUSER.T_TABLE2 t33097
                                                on
                                                        t15285.BRANCH_ID1       = t33097.BRANCH_ID1
                                                    and t15285.BRANCH_ID2       = t33097.BRANCH_ID2
                                                    and t15285.BRANCH_ID3       = t33097.BRANCH_ID3
                                                    and t15285.BRANCH_ID4       = t33097.BRANCH_ID4
                                                    and t15285.CONSULTATIONM_ID = t33097.CONSULTATIONM_ID1
                                                    and t15285.OPERATOR_NO      = t33097.OPERATOR_NO1
                                                left join SOMEUSER.T_TABLE3 t16323
                                                on
                                                        t15285.TERM_DATE = t16323.TERM_DATE
                                                where
                                                        t33097.BRANCH_NAME1 in ('xxx')
                                                    and t16323.TERM_DATE    >= '2021-01-01'
                                                    and t16323.TERM_DATE    <= '2021-10-10'
                                                    and t33097.BRANCH_ID1   in ( '1021' )
                                        )
                                        a
                                GROUP BY
                                        a.BRANCH_NAME2
                                ORDER BY
                                        a.BRANCH_NAME2
                        )
                        b
        )
where
        rownum <= 30000
```

原执行计划
```

1   #NSET2: [702, 1, 336] 
2     #PRJT2: [702, 1, 336]; exp_num(2), is_atom(FALSE) 
3       #PRJT2: [702, 1, 336]; exp_num(2), is_atom(FALSE) 
4         #TOPN2: [702, 1, 336]; top_num(exp11)
5           #PRJT2: [702, 112, 336]; exp_num(2), is_atom(FALSE) 
6             #SORT3: [702, 112, 336]; key_num(1), is_distinct(FALSE), top_flag(0), is_adaptive(0)
7               #HAGR2: [701, 112, 336]; grp_num(1), sfun_num(1); slave_empty(0) keys(A.BRANCH_NAME2) 
8                 #PRJT2: [699, 225, 336]; exp_num(2), is_atom(FALSE) 
9                   #UNION ALL: [699, 225, 336]
10                    #PRJT2: [693, 112, 336]; exp_num(2), is_atom(FALSE) 
11                      #NEST LOOP LEFT JOIN2: [693, 112, 336]; join condition(PT.BRANCH_ID2 = DT.T33097BRANCH_ID2)[with var] partition_keys_num(0) ret_null(0)
12                        #PRJT2: [21, 112, 240]; exp_num(2), is_atom(FALSE) 
13                          #HAGR2: [21, 112, 240]; grp_num(2), sfun_num(0); slave_empty(0) keys(T33097.BRANCH_ID2, T33097.BRANCH_NAME2) 
14                            #NEST LOOP SEMI JOIN2: [18, 32459, 240];  
15                              #BLKUP2: [1, 112, 192]; I_BRANCHTEMP1_LH(T33097)
16                                #SSEK2: [1, 112, 192]; scan_type(ASC), I_BRANCHTEMP1_LH(T_TABLE2 as T33097), scan_range[('xxx ','1021'),('xxx ','1021')]
17                              #SSEK2: [1, 288, 48]; scan_type(ASC), PK_T_TABLE3(T_TABLE3 as T16323), scan_range['2021-01-01','2021-10-10']
18                        #PRJT2: [5, 381, 96]; exp_num(2), is_atom(FALSE) 
19                          #SAGR2: [5, 381, 96]; grp_num(1), sfun_num(0) slave_empty(0) keys(T15323.BRANCH_ID2) 
20                            #SSEK2: [3, 26891, 96]; scan_type(ASC), I_ALLMDATE_ID2LH(EA_CM_ALLCOUNT_MDATE as T15323), scan_range[(var2,'2021-01-01'),(var2,'2021-10-10')]
21                    #PRJT2: [5, 112, 798]; exp_num(2), is_atom(FALSE) 
22                      #SLCT2: [5, 112, 798]; (T16323.TERM_DATE >= '2021-01-01' AND T16323.TERM_DATE <= '2021-10-10')
23                        #NEST LOOP INDEX JOIN2: [5, 112, 798] 
24                          #SLCT2: [4, 112, 750]; (T15285.BRANCH_ID1 = '1021' AND T15285.TERM_DATE >= '2021-01-01' AND T15285.TERM_DATE <= '2021-10-10' AND T15285.BRANCH_ID1 = T33097.BRANCH_ID1)
25                            #NEST LOOP INDEX JOIN2: [4, 112, 750] 
26                              #BLKUP2: [1, 112, 384]; I_BRANCHTEMP1_LH(T33097)
27                                #SSEK2: [1, 112, 384]; scan_type(ASC), I_BRANCHTEMP1_LH(T_TABLE2 as T33097), scan_range[('xxx','1021'),('xxx','1021')]
28                              #BLKUP2: [1, 1, 0]; I_MDATESDV_LH(T15285)
29                                #SSEK2: [1, 1, 0]; scan_type(ASC), I_MDATESDV_LH(T_TABLE1 as T15285), scan_range[(T33097.BRANCH_ID2,T33097.BRANCH_ID3,T33097.BRANCH_ID4,T33097.CONSULTATIONM_ID1,T33097.OPERATOR_NO1),(T33097.BRANCH_ID2,T33097.BRANCH_ID3,T33097.BRANCH_ID4,T33097.CONSULTATIONM_ID1,T33097.OPERATOR_NO1)]
30                          #SSEK2: [1, 1, 0]; scan_type(ASC), PK_T_TABLE3(T_TABLE3 as T16323), scan_range[T15285.TERM_DATE,T15285.TERM_DATE]
```

根据执行计划以及分段分析，提取慢的部分SQL  
```
select
        t33097.BRANCH_NAME2 BRANCH_NAME2,
        t15285.NOCAR_PREMIUM_VALUE
from
        SOMEUSER.T_TABLE1 t15285
left join SOMEUSER.T_TABLE2 t33097
on
        t15285.BRANCH_ID1       = t33097.BRANCH_ID1
    and t15285.BRANCH_ID2       = t33097.BRANCH_ID2
    and t15285.BRANCH_ID3       = t33097.BRANCH_ID3
    and t15285.BRANCH_ID4       = t33097.BRANCH_ID4
    and t15285.CONSULTATIONM_ID = t33097.CONSULTATIONM_ID1
    and t15285.OPERATOR_NO      = t33097.OPERATOR_NO1
left join SOMEUSER.T_TABLE3 t16323
on
        t15285.TERM_DATE = t16323.TERM_DATE
where
        t33097.BRANCH_NAME1 in ('XXX')
    and t16323.TERM_DATE    >= '2021-01-01'
    and t16323.TERM_DATE    <= '2021-10-10'
    and t33097.BRANCH_ID1   in ( '1021' )
```

根据执行计划分析：  
1.T_TABLE2先和T_TABLE1进行nest loop  
2.步骤1的结果和T_TABLE3进行nest loop
```
21                    #PRJT2: [5, 112, 798]; exp_num(2), is_atom(FALSE) 
22                      #SLCT2: [5, 112, 798]; (T16323.TERM_DATE >= '2021-01-01' AND T16323.TERM_DATE <= '2021-10-10')
23                        #NEST LOOP INDEX JOIN2: [5, 112, 798] 
24                          #SLCT2: [4, 112, 750]; (T15285.BRANCH_ID1 = '1021' AND T15285.TERM_DATE >= '2021-01-01' AND T15285.TERM_DATE <= '2021-10-10' AND T15285.BRANCH_ID1 = T33097.BRANCH_ID1)
25                            #NEST LOOP INDEX JOIN2: [4, 112, 750] 
26                              #BLKUP2: [1, 112, 384]; I_BRANCHTEMP1_LH(T33097)
27                                #SSEK2: [1, 112, 384]; scan_type(ASC), I_BRANCHTEMP1_LH(T_TABLE2 as T33097), scan_range[('xxx','1021'),('xxx','1021')]
28                              #BLKUP2: [1, 1, 0]; I_MDATESDV_LH(T15285)
29                                #SSEK2: [1, 1, 0]; scan_type(ASC), I_MDATESDV_LH(T_TABLE1 as T15285), scan_range[(T33097.BRANCH_ID2,T33097.BRANCH_ID3,T33097.BRANCH_ID4,T33097.CONSULTATIONM_ID1,T33097.OPERATOR_NO1),(T33097.BRANCH_ID2,T33097.BRANCH_ID3,T33097.BRANCH_ID4,T33097.CONSULTATIONM_ID1,T33097.OPERATOR_NO1)]
30                          #SSEK2: [1, 1, 0]; scan_type(ASC), PK_T_TABLE3(T_TABLE3 as T16323), scan_range[T15285.TERM_DATE,T15285.TERM_DATE]
```
其中：  
SOMEUSER.T_TABLE1 800w数据量   
SOMEUSER.T_TABLE2 3w数据量  
SOMEUSER.T_TABLE3 2000数据量

解决方案分析：   
1.T_TABLE3只有2000的数据量，所以优先关联进行nest loop会提高sql执行速度，降低打中的数据量级。  
2.T_TABLE2只有3w，满足nest loop，但很大的影响速度，提高T_TABLE2表的数据量，让执行计划优化处理数据量少的T_TABLE3。

## 解决
加上 hint  
```
union all
select   /*+stat(t33097 10M)*/
        t33097.BRANCH_NAME2 BRANCH_NAME2,
        t15285.NOCAR_PREMIUM_VALUE
```

改进后执行计划 （6s提升到1s内）  
1.T_TABLE3优先和最小表T_TABLE3进行nest loop关联。  
2.步骤1的结果和T_TABLE2再进行hash关联。  
```
21                    #PRJT2: [86, 10350346, 798]; exp_num(2), is_atom(FALSE) 
22                      #HASH2 INNER JOIN: [86, 10350346, 798];  KEY_NUM(6); KEY(T33097.BRANCH_ID1=T15285.BRANCH_ID1 AND T33097.BRANCH_ID2=T15285.BRANCH_ID2 AND T33097.BRANCH_ID3=T15285.BRANCH_ID3 AND T33097.BRANCH_ID4=T15285.BRANCH_ID4 AND T33097.CONSULTATIONM_ID1=T15285.CONSULTATIONM_ID AND T33097.OPERATOR_NO1=T15285.OPERATOR_NO) KEY_NULL_EQU(0, 0, 0, 0, 0, 0)
23                        #BLKUP2: [1, 33019, 384]; I_BRANCHTEMP1_LH(T33097)
24                          #SSEK2: [1, 33019, 384]; scan_type(ASC), I_BRANCHTEMP1_LH(T_TABLE2 as T33097), scan_range[('xxx','1021'),('xxx','1021')]
25                        #SLCT2: [65, 71469, 414]; (T15285.TERM_DATE >= '2021-01-01' AND T15285.TERM_DATE <= '2021-10-10')
26                          #NEST LOOP INDEX JOIN2: [65, 71469, 414] 
27                            #SSEK2: [1, 288, 48]; scan_type(ASC), PK_T_TABLE3(T_TABLE3 as T16323), scan_range['2021-01-01','2021-10-10']
28                            #BLKUP2: [12, 248, 0]; I_MDATESDV_LH2(T15285)
29                              #SSEK2: [12, 248, 0]; scan_type(ASC), I_MDATESDV_LH2(T_TABLE1 as T15285), scan_range[('1021',T16323.TERM_DATE),('1021',T16323.TERM_DATE)]
```

## 总结

合理使用STAT，调整表大小，以便调整执行计划，预估执行时间。

DM 武汉达梦数据库股份有限公司
24小时免费服务热线：400 991 6599
达梦技术社区：https://eco.dameng.com

ITPub博客

达梦SQL优化方法stat

最新文章