同事发来一个核心业务库的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个,因测试数据量较小,生产上理论效果更加明显。