同事发来一个核心业务库的TOP SQL，占据并浪费了大量的服务器CPU资源，此SQL执效率信息如下：
![](http://albertdba.com/"wp-content/uploads"/2020/11/20201105-1-1024x230.png)
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执行计划如下：
![](http://albertdba.com/"wp-content/uploads"/2020/11/20201105-2-1024x244.png)
从执行计划中看到，估算行数与实际行数差别巨大，实际行数只是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个，因测试数据量较小，生产上理论效果更加明显。