SQL优化案例（谓词越界）

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150

SQL Monitoring Report    SQL Text ------------------------------ SELECT TACTIC_DET_ID,        CALC_ID,        ORG_NO,        CONS_ID,        CONS_NO,        ESTI_PQ,        ESTI_AMT,        YM,        YMD,        COLL_TIME,        ESTI_DATE,        ACT_AMT,        BASE_COMP_TIME,        BASE_TACTIC_NO,        OVERDRAFT_VALUE,        CONS_STATUS,        DIRECTIVE_ID,        EXECUTE_TYPE,        EXECUTE_SCHEME,        EXECUTE_DATE,        EXECUTE_STATUS,        TRANSIT_HANDLE_ID,        TRANSIT_STEP,        MR_SECT_NO,        REMARK,        CP_NO,        CP_STATUS,        CP_PRIO,        CHANNEL_TYPE   FROM A_RCA_TACTIC_DET A  WHERE A.CONS_NO = :B5    AND A.ORG_NO LIKE :B4 || '%'    AND A.YM BETWEEN SUBSTR(:B2, 1, 6) AND :B3    AND A.YMD >= :B2    AND A.YMD <= :B1  ORDER BY A.ESTI_DATE DESC    Global Information ------------------------------  Status              :  EXECUTING                                                                   Instance ID         :  2                                                                           Session             :  xxx (1709:38751)                                                           SQL ID              :  cu1ktstbq4axt                                                               SQL Execution ID    :  33555216                                                                    Execution Started   :  07/03/2020 14:28:18                                                         First Refresh Time  :  07/03/2020 14:28:24                                                         Last Refresh Time   :  07/03/2020 14:30:29                                                         Duration            :  131s                                                                        Module/Action       :  ro.cons.service.ConsViewService.getRcaTacticInfo/TH45-@dyxepm1_1:0703142819  Service             :  app1                                                                        Program             :  JDBC Thin Client                                                              Binds ======================================================================================================================== | Name | Position |     Type      |                                       Value                                        | ======================================================================================================================== | :B5  |        1 | VARCHAR2(32)  | 6xxxxxxxx7                                                                         | | :B4  |        2 | VARCHAR2(128) | 3xxxxx0                                                                            | | :B2  |        3 | VARCHAR2(32)  | 2xxxxxxx3                                                                           | | :B3  |        4 | VARCHAR2(32)  | 2xxxx8                                                                             | | :B2  |        5 | VARCHAR2(32)  | 2xxxxxxxx3                                                                           | | :B1  |        6 | VARCHAR2(32)  | 2xxxxxxxx3                                                                           | ========================================================================================================================    Global Stats ================================================================================== | Elapsed |   Cpu   |    IO    | Concurrency | Cluster  | Buffer | Read  | Read  | | Time(s) | Time(s) | Waits(s) |  Waits(s)   | Waits(s) |  Gets  | Reqs  | Bytes | ================================================================================== |     131 |    5.78 |      118 |        0.00 |     7.91 |   261K | 19313 | 302MB | ==================================================================================    SQL Plan Monitoring Details (Plan Hash Value=3175721642) =============================================================================================================================================================================================== | Id   |               Operation                |           Name           |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read  | Read  | Activity |       Activity Detail        | |      |                                        |                          | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs  | Bytes |   (%)    |         (# samples)          | =============================================================================================================================================================================================== |    0 | SELECT STATEMENT                       |                          |         |      |           |        |     1 |          |       |       |          |                              | |    1 |   SORT ORDER BY                        |                          |       1 |   32 |           |        |     1 |          |       |       |          |                              | |    2 |    FILTER                              |                          |         |      |           |        |     1 |          |       |       |          |                              | |    3 |     PARTITION RANGE ITERATOR           |                          |       1 |   31 |           |        |     1 |          |       |       |          |                              | | -> 4 |      TABLE ACCESS BY LOCAL INDEX ROWID | A_RCA_TACTIC_DET         |       1 |   31 |       135 |     +1 |     2 |        0 | 15181 | 237MB |    70.23 | gc cr grant 2-way (4)        | |      |                                        |                          |         |      |           |        |       |          |       |       |          | Cpu (4)                      | |      |                                        |                          |         |      |           |        |       |          |       |       |          | db file sequential read (84) | | -> 5 |       INDEX RANGE SCAN                 | IDX_A_RCA_TACTIC_DET_YMD |       1 |   31 |       133 |     +3 |     2 |     848K |  4231 |  66MB |    29.77 | gc cr grant 2-way (3)        | |      |                                        |                          |         |      |           |        |       |          |       |       |          | Cpu (1)                      | |      |                                        |                          |         |      |           |        |       |          |       |       |          | db file sequential read (35) | =============================================================================================================================================================================================== e-row和a-rows差别很大，一般都是由于统计信息不准确导致。（还有可能是cost计算方式不合理） select num_rows,blocks,last_analyzed from dba_tables where table_name='A_RCA_TACTIC_DET'; 300090470   9391916 2019/11/21 4:42:00    SELECT * FROM DBA_TAB_COL_STATISTICS WHERE TABLE_NAME='A_RCA_TACTIC_DET'; CONS_NO 11736064 YMD     1236       存在直方图信息    select BLEVEL,distinct_keys,leaf_blocks,clustering_factor from dba_indexes where index_name in('IDX_A_RCA_TACTIC_DET_YMD','LOC_A_RCA_TACTIC_DET_CONSNO') 2       11736064    2558042 295677239 2       1236        1516690 9088619    直接做个10053trace Column (#9): YMD( AvgLen: 9 NDV: 1236 Nulls: 687 Density: 0.000024 Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 85 Using prorated density: 0.000000 of col #9 as selectvity of out-of-range/non-existent value pred    我们发现YMD的是越界的，不在列统计信息high_value,low_value之间，导致执行计划评估不正确。 （如果想知道cost的具体计算方式可以参SQL优化核心思想这本书， cost = blevel +ceiling(leaf_blocks * effective index selectivity) +ceiling(clustering_factor * effective table selectivity) 且选择性计算方法是和high_value,low_value等值存在关联）。    解决方案：    重新收集统计信息。   删除YMD列上的索引。（本身列的选择性就不高）    profile绑定执行计划(具体操作：https://www.cnblogs.com/muzisanshi/p/11889727.html)    select * from table(dbms_xplan.display_awr('cu1ktstbq4axt'))     Plan hash value: 948946192             ------------------------------------------------------------------------------------------------------------------------------------     | Id  | Operation                            | Name                        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |     ------------------------------------------------------------------------------------------------------------------------------------     |   0 | SELECT STATEMENT                     |                             |       |       |    34 (100)|          |       |       |     |   1 |  SORT ORDER BY                       |                             |     1 |   219 |    34   (3)| 00:00:01 |       |       |     |   2 |   FILTER                             |                             |       |       |            |          |       |       |     |   3 |    PARTITION RANGE ITERATOR          |                             |     1 |   219 |    33   (0)| 00:00:01 |   KEY |   KEY |     |   4 |     TABLE ACCESS BY LOCAL INDEX ROWID| A_RCA_TACTIC_DET            |     1 |   219 |    33   (0)| 00:00:01 |   KEY |   KEY |     |   5 |      INDEX RANGE SCAN                | LOC_A_RCA_TACTIC_DET_CONSNO |     8 |       |    31   (0)| 00:00:01 |   KEY |   KEY |     ------------------------------------------------------------------------------------------------------------------------------------        Plan hash value: 3175721642             ---------------------------------------------------------------------------------------------------------------------------------     | Id  | Operation                            | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |     ---------------------------------------------------------------------------------------------------------------------------------     |   0 | SELECT STATEMENT                     |                          |       |       |    32 (100)|          |       |       |     |   1 |  SORT ORDER BY                       |                          |     1 |   226 |    32   (4)| 00:00:01 |       |       |     |   2 |   FILTER                             |                          |       |       |            |          |       |       |     |   3 |    PARTITION RANGE ITERATOR          |                          |     1 |   226 |    31   (0)| 00:00:01 |   KEY |   KEY |     |   4 |     TABLE ACCESS BY LOCAL INDEX ROWID| A_RCA_TACTIC_DET         |     1 |   226 |    31   (0)| 00:00:01 |   KEY |   KEY |     |   5 |      INDEX RANGE SCAN                | IDX_A_RCA_TACTIC_DET_YMD |     1 |       |    31   (0)| 00:00:01 |   KEY |   KEY |     ---------------------------------------------------------------------------------------------------------------------------------