核心系统表关联顺序错误导致的低效SQL

同事发来一个核心业务库的TOP SQL,占据并浪费了大量的服务器CPU资源,此SQL执效率信息如下:

SELECT a.ID,
       a.SEG_FLIGHT_ID,
       a.SEG_DEP_STATION_CODE,
       a.SEG_ARR_STATION_CODE,
       a.SEG_CLASS_SEQUENCE,
       a.SEG_TRAFFIC_RESTRICTION_CODE,
       a.SEG_DEP_TERMINAL,
       a.SEG_ARR_TERMINAL,
       a.SEG_OC_TYPE,
       a.SEG_OC_MAP_NUMBER,
       a.SEG_OC_CLASS_MAPPING,
       a.SEG_DATA_SOURCE
FROM T_SEGMENT a,
  (SELECT id
   FROM
     (SELECT t.id
      FROM T_FLIGHT t,
           T_TASK ta
      WHERE 1=1
        AND ta.id IN ('11008',
                      '11003',
                      '11014',
                      '11012',
                      '14001',
                      '11001')
        AND t.FLT_AIRLINE_CODE = ta.FLT_AIRLINE_CODE
        AND t.FLT_FLIGHT_NO = ta.FLT_FLIGHT_NO
        AND t.FLT_FLIGHT_DATE = ta.FLT_FLIGHT_DATE
        AND t.FLT_OC_FLIGHT_ID IS NULL
        AND t.IS_VALID = 1
        AND nullif('AAAA',t.FLT_OFFICE) IS NOT NULL )
   LEFT JOIN t_dep_schema s ON id = s.flt_id
   WHERE s.flt_id IS NULL
     OR (s.flt_id IS NOT NULL
         AND (s.FLT_IN_TCARD_PROCESS_FLAG ='N'
              OR s.FLT_IN_TCARD_PROCESS_FLAG IS NULL))
     OR (s.flt_id IS NOT NULL
         AND (s.FLT_IN_INIT_PROCESS_FLAG IS NULL
              OR s.FLT_IN_INIT_PROCESS_FLAG ='N')) ) f
WHERE 1=1
  AND a.SEG_FLIGHT_ID = f.ID
  AND a.IS_VALID = 1

SQL执行计划如下:

从执行计划中看到,估算行数与实际行数差别巨大,实际行数只是899行,但是NESTED LOOP之后估算结果集为46K,并且TASK表与S表选择了HASH JOIN,在未收集个性化扩展统计信息的情况,针对这种复杂情况,估算不准的情况是比较常见的,通常需要利用HINT来调整表连接顺序来优化。此SQL的涉及表都不存在索引缺失的情况,只是行数估算错误导致表连接顺序有误,从而选择了低效的执行计划。

向应用要来了测试库,进行实际测试,计划利用

1.TA与T表进行NESTED LOOP

2.(1)的结果集与s表(t_dep_schema)做NESTED LOOP关联,最后再与T_SEGMENT关联即可。

用HINT表示为:/*+ use_nl(t) use_nl(ta) use_nl(s) use_nl(a) leading(ta t s a) index(a IDX_T_SEGMENT_01) */

测试库数据量较小,理论上表越大,优化效果越明显:

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  65wd95frgnafs, child number 0
-------------------------------------

Plan hash value: 2283617204

----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                     | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                          |      1 |        |    22 (100)|          |      7 |00:00:00.01 |    1199 |
|*  1 |  FILTER                         |                          |      1 |        |            |          |      7 |00:00:00.01 |    1199 |
|   2 |   NESTED LOOPS OUTER            |                          |      1 |     14 |    22   (0)| 00:00:01 |      7 |00:00:00.01 |    1199 |
|*  3 |    HASH JOIN                    |                          |      1 |     14 |    21   (0)| 00:00:01 |      7 |00:00:00.01 |    1194 |
|   4 |     NESTED LOOPS                |                          |      1 |     17 |    11   (0)| 00:00:01 |     16 |00:00:00.01 |    1163 |
|   5 |      NESTED LOOPS               |                          |      1 |     49 |    11   (0)| 00:00:01 |     29 |00:00:00.01 |    1142 |
|*  6 |       TABLE ACCESS FULL         | T_FLIGHT                 |      1 |     49 |    10   (0)| 00:00:01 |   1067 |00:00:00.01 |      30 |
|*  7 |       INDEX UNIQUE SCAN         | UNI_INIT_TASK_01         |   1067 |      1 |     0   (0)|          |     29 |00:00:00.01 |    1112 |
|*  8 |      TABLE ACCESS BY INDEX ROWID| T_TASK                   |     29 |      1 |     1   (0)| 00:00:01 |     16 |00:00:00.01 |      21 |
|*  9 |     TABLE ACCESS FULL           | T_SEGMENT                |      1 |   1108 |    10   (0)| 00:00:01 |   1112 |00:00:00.01 |      31 |
|  10 |    TABLE ACCESS BY INDEX ROWID  | T_SCHEMA                 |      7 |      1 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       5 |
|* 11 |     INDEX UNIQUE SCAN           | PK_T_SCHEMA              |      7 |      1 |     0   (0)|          |      1 |00:00:00.01 |       4 |
----------------------------------------------------------------------------------------------------------------------------------------------
利用Hint调整后:
SELECT /*+ use_nl(t) use_nl(ta) use_nl(s) use_nl(a) leading(ta t s a) index(a IDX_T_DEP_FDC_SEGMENT_01) */
       a.ID,
       a.SEG_FLIGHT_ID,
       a.SEG_DEP_STATION_CODE,
       a.SEG_ARR_STATION_CODE,
       a.SEG_CLASS_SEQUENCE,
       a.SEG_TRAFFIC_RESTRICTION_CODE,
       a.SEG_DEP_TERMINAL,
       a.SEG_ARR_TERMINAL,
       a.SEG_OC_TYPE,
       a.SEG_OC_MAP_NUMBER,
       a.SEG_OC_CLASS_MAPPING,
       a.SEG_DATA_SOURCE
FROM T_SEGMENT a,
  (SELECT id
   FROM
     (SELECT /*+ use_nl(ta) use_nl(t) use_nl(s) leading(ta t s) index(ta PK_INIT_TASK_ID) index(t IDX_T_DEP_FDC_FLIGHT_01) */ t.id
      FROM T_FLIGHT t,
           T_TASK ta
      WHERE 1=1
        AND ta.id IN ('11008',
                      '11003',
                      '11014',
                      '11012',
                      '14001',
                      '11001')
        AND t.FLT_AIRLINE_CODE = ta.FLT_AIRLINE_CODE
        AND t.FLT_FLIGHT_NO = ta.FLT_FLIGHT_NO
        AND t.FLT_FLIGHT_DATE = ta.FLT_FLIGHT_DATE
        AND t.FLT_OC_FLIGHT_ID IS NULL
        AND t.IS_VALID = 1
        AND nullif('AAAA',t.FLT_OFFICE) IS NOT NULL )
   LEFT JOIN t_dep_schema s ON id = s.flt_id
   WHERE s.flt_id IS NULL
     OR (s.flt_id IS NOT NULL
         AND (s.FLT_IN_TCARD_PROCESS_FLAG ='N'
              OR s.FLT_IN_TCARD_PROCESS_FLAG IS NULL))
     OR (s.flt_id IS NOT NULL
         AND (s.FLT_IN_INIT_PROCESS_FLAG IS NULL
              OR s.FLT_IN_INIT_PROCESS_FLAG ='N')) ) f
WHERE 1=1
  AND a.SEG_FLIGHT_ID = f.ID
  AND a.IS_VALID = 1
------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                     | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                          |      1 |        |    31 (100)|          |      7 |00:00:00.01 |      50 |
|   1 |  NESTED LOOPS                     |                          |      1 |      5 |    31   (0)| 00:00:01 |      7 |00:00:00.01 |      50 |
|   2 |   NESTED LOOPS                    |                          |      1 |     18 |    31   (0)| 00:00:01 |      7 |00:00:00.01 |      48 |
|*  3 |    FILTER                         |                          |      1 |        |            |          |     16 |00:00:00.01 |      39 |
|   4 |     NESTED LOOPS OUTER            |                          |      1 |      6 |    21   (0)| 00:00:01 |     16 |00:00:00.01 |      39 |
|   5 |      NESTED LOOPS                 |                          |      1 |      6 |    20   (0)| 00:00:01 |     16 |00:00:00.01 |      34 |
|   6 |       INLIST ITERATOR             |                          |      1 |        |            |          |      6 |00:00:00.01 |      13 |
|   7 |        TABLE ACCESS BY INDEX ROWID| T_TASK                   |      6 |      6 |     8   (0)| 00:00:01 |      6 |00:00:00.01 |      13 |
|*  8 |         INDEX UNIQUE SCAN         | PK_INIT_TASK_ID          |      6 |      6 |     2   (0)| 00:00:01 |      6 |00:00:00.01 |       8 |
|*  9 |       TABLE ACCESS BY INDEX ROWID | T_FLIGHT                 |      6 |      1 |     2   (0)| 00:00:01 |     16 |00:00:00.01 |      21 |
|* 10 |        INDEX RANGE SCAN           | IDX_T_FLIGHT_07          |      6 |      1 |     1   (0)| 00:00:01 |     16 |00:00:00.01 |       9 |
|  11 |      TABLE ACCESS BY INDEX ROWID  | T_DEP_SCHEMA             |     16 |      1 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       5 |
|* 12 |       INDEX UNIQUE SCAN           | PK_T_DEP_SCHEMA          |     16 |      1 |     0   (0)|          |      1 |00:00:00.01 |       4 |
|* 13 |    INDEX RANGE SCAN               | IDX_T_SEGMENT_01         |     16 |      3 |     1   (0)| 00:00:01 |      7 |00:00:00.01 |       9 |
|* 14 |   TABLE ACCESS BY INDEX ROWID     | T_SEGMENT                |      7 |      1 |     2   (0)| 00:00:01 |      7 |00:00:00.01 |       2 |
------------------------------------------------------------------------------------------------------------------------------------------------

资源消耗(buffer get)由原有1199降至50个,因测试数据量较小,生产上理论效果更加明显。