# 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

達夢SQL優化方法stat

相關文章