前言
数据库近日做了一次迁移，迁移前一切正常，迁移后业务发现有个sql执行非常慢，十万火急！
二话不说，上sql
SELECT N.DEP_CODE,
N.P_CODE,
N.AC_TYPE_MACRO,
nvl(T.UPDOWN, 0) + nvl(U.UPDOWN, 0) UP,
nvl(S.UPDOWN, 0) + nvl(U.UPDOWN, 0) DOWN,
N.TECH_NO
FROM
(SELECT DISTINCT A.DEP_CODE,
A.P_CODE,
C.AC_TYPE_CREW AS AC_TYPE_MACRO,
b.tech_no TECH_NO
FROM T3017 A, fleet_info C
,(select a.p_code,a.tech_no,a.aircraft_type from
(select s.tech_no tech_no,
a.aircraft_type,
a.p_code,
row_number() over(PARTITION BY a.p_code,a.tech_ac_type ORDER BY s."LEVEL" ASC) rn
from t3009 a,t3021 s
where nvl(a.tech_no1, 'Z380') = s.tech_no
and s.tech_type = 'F'
AND a.valide_flag = 'Y'
AND s.in_ex = 'Z'
AND s.isvalid = 1
AND s.rank_no IN('F201', 'F230') ) a
where rn = 1 ) b
WHERE A.P_CODE = B.P_CODE
AND B.aircraft_type = C.ac_type and c.carrier = 'MF'
AND instr('/FL0302/', A.DEP_CODE) > 0
AND(A.DEP_CODE <> 'FL19' and A.DEP_CODE <> 'FL05' and
A.DEP_CODE <> 'FL07' and A.DEP_CODE <> 'FL06' and
A.DEP_CODE <> 'FL08')
and A.DUTY_CODE = 'F'
AND A.AVAIL_FLAG = 'Y'
AND(A.DEP_CODE <> 'FL06' and A.DEP_CODE <> 'FL07' and
A.DEP_CODE <> 'FL08')
and B.tech_no is not null
group by A.DEP_CODE, A.P_CODE, C.AC_TYPE_CREW, b.tech_no) N, (SELECT T3017.P_CODE, fleet_info.AC_TYPE_CREW AS AC_TYPE_MACRO, COUNT(1) updown
FROM T5001_TASK, T3017, fleet_info
WHERE fleet_info.AC_TYPE = T5001_TASK.AC_TYPE and fleet_info.carrier='MF'
AND instr('/FL0302/', T3017.DEP_CODE) > 0
AND (T3017.DEP_CODE <> 'FL19' and T3017.DEP_CODE <> 'FL05' and
T3017.DEP_CODE <> 'FL07' and T3017.DEP_CODE <> 'FL06' and
T3017.DEP_CODE <> 'FL08')
AND T3017.DUTY_CODE = 'F'
AND T3017.AVAIL_FLAG = 'Y'
AND T5001_TASK.FLIGHT_DATE + 90 > TRUNC(date'2019-6-20')
AND T5001_TASK.FLIGHT_DATE <= TRUNC(date'2019-6-20')
AND T5001_TASK.FLIGHT_TYPE <> 'K'
AND ((T3017.P_CODE = T5001_TASK.LEFT_P_CODE AND
T5001_TASK.CONTROL_PERSON = 'L') OR
(T3017.P_CODE = T5001_TASK.RIGHT_P_CODE AND
T5001_TASK.CONTROL_PERSON = 'R'))
GROUP BY T3017.P_CODE, fleet_info.AC_TYPE_CREW) T,
(SELECT T3017.P_CODE, fleet_info.AC_TYPE_CREW AS AC_TYPE_MACRO, COUNT(1) updown
FROM T5001_TASK, T3017, fleet_info
WHERE fleet_info.AC_TYPE = T5001_TASK.AC_TYPE and fleet_info.carrier='MF'
AND instr('/FL0302/', T3017.DEP_CODE) > 0
AND (T3017.DEP_CODE <> 'FL19' and T3017.DEP_CODE <> 'FL05' and
T3017.DEP_CODE <> 'FL07' and T3017.DEP_CODE <> 'FL06' and
T3017.DEP_CODE <> 'FL08')
AND T3017.DUTY_CODE = 'F'
AND T3017.AVAIL_FLAG = 'Y'
AND T5001_TASK.FLIGHT_DATE + 90 > TRUNC(date'2019-6-20')
AND T5001_TASK.FLIGHT_DATE <= TRUNC(date'2019-6-20')
AND T5001_TASK.FLIGHT_TYPE <> 'K'
AND ((T3017.P_CODE = T5001_TASK.Land_Left_p_Code AND
T5001_TASK.Land_Control_Person = 'L') OR
(T3017.P_CODE = T5001_TASK.Land_Right_p_Code AND
T5001_TASK.Land_Control_Person = 'R'))
GROUP BY T3017.P_CODE, fleet_info.AC_TYPE_CREW) S,
(SELECT T3017.P_CODE,
FLEET_INFO.Ac_Type_Crew AS AC_TYPE_MACRO,
SUM(T3005.UPDOWN) UPDOWN
FROM T3001, T3005, T3017, FLEET_INFO
WHERE FLEET_INFO.AC_TYPE = T3001.AC_TYPE
AND FLEET_INFO.CARRIER = 'MF'
AND instr('/FL0302/', T3017.DEP_CODE) > 0
AND (T3017.DEP_CODE <> 'FL19' and T3017.DEP_CODE <> 'FL05' and
T3017.DEP_CODE <> 'FL07' and T3017.DEP_CODE <> 'FL06' and
T3017.DEP_CODE <> 'FL08')
AND T3017.DUTY_CODE = 'F'
AND T3017.AVAIL_FLAG = 'Y'
AND T3005.P_CODE = T3017.P_CODE
AND T3005.FLIGHT_DATE + 90 > TRUNC(date'2019-6-20')
AND T3005.FLIGHT_DATE <= TRUNC(date'2019-6-20')
AND T3001.Flight_Date = T3005.FLIGHT_DATE
AND T3001.CREW_LINK_LINE = T3005.CREW_LINK_LINE
AND T3001.FLIGHT_TYPE = 'X'
GROUP BY T3017.P_CODE, FLEET_INFO.AC_TYPE_CREW) U
WHERE N.p_code = T.p_code(+)
AND N.ac_type_macro = T.ac_type_macro(+)
AND N.p_code = S.p_code(+)
AND N.ac_type_macro = S.ac_type_macro(+)
AND N.p_code = U.p_code(+)
AND N.ac_type_macro = U.ac_type_macro(+)
ORDER BY N.DEP_CODE,N.P_CODE,N.AC_TYPE_MACRO
排查经过如下
1.查询SQL监视（DBA）
select dbms_sqltune.report_sql_monitor(type=>'TEXT', sql_id=>'fw40xhr1skg98',report_level=>'ALL') monitor_report from dual;结果：
SQL Monitoring Report
SQL Text
------------------------------
SELECT N.DEP_CODE, N.P_CODE, N.AC_TYPE_MACRO, nvl(T.UPDOWN, 0) + nvl(U.UPDOWN, 0) UP, nvl(S.UPDOWN, 0) + nvl(U.UPDOWN, 0) DOWN, N.TECH_NO FROM (SELECT DISTINCT A.DEP_CODE, A.P_CODE, C.AC_TYPE_CREW AS AC_TYPE_MACRO, b.tech_no TECH_NO FROM T3017 A, fleet_info C ,(select a.p_code,a.tech_no,a.aircraft_type from (select s.tech_no tech_no, a.aircraft_type, a.p_code, row_number() over(PARTITION BY a.p_code,a.tech_ac_type ORDER BY s."LEVEL" ASC) rn from t3009 a,t3021 s where nvl(a.tech_no1, 'Z380') =
s.tech_no and s.tech_type = 'F' AND a.valide_flag = 'Y' AND s.in_ex = 'Z' AND s.isvalid = 1 AND s.rank_no IN('F201', 'F230') ) a where rn = 1 ) bWHERE A.P_CODE = B.P_CODEAND B.aircraft_type = C.ac_type and c.carrier = 'MF'AND instr(:DeptCode, A.DEP_CODE) > 0AND(A.DEP_CODE <> 'FL19' and A.DEP_CODE <> 'FL05' andA.DEP_CODE <> 'FL07' and A.DEP_CODE <> 'FL06' andA.DEP_CODE <> 'FL08')and A.DUTY_CODE = 'F'AND A.AVAIL_FLAG = 'Y'AND(A.DEP_CODE <> 'FL06' and A.DEP_CODE <> 'FL07' andA.DEP_CODE <>
'FL08')and B.tech_no is not nullgroup by A.DEP_CODE, A.P_CODE, C.AC_TYPE_CREW, b.tech_no) N, (SELECT T3017.P_CODE, fleet_info.AC_TYPE_CREW AS AC_TYPE_MACRO, COUNT(1) updown FROM T5001_TASK, T3017, fleet_info WHERE fleet_info.AC_TYPE = T5001_TASK.AC_TYPE and fleet_info.carrier='MF' AND instr(:DeptCode
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : CMS_APP (942:36667)
SQL ID : fw40xhr1skg98
SQL Execution ID : 16777228
Execution Started : 06/20/2019 14:11:41
First Refresh Time : 06/20/2019 14:11:47
Last Refresh Time : 06/20/2019 14:30:56
Duration : 1155s
Module/Action : w3wp.exe/-
Service : focdbsvc1
Program : w3wp.exe
Fetch Calls : 1
Binds
========================================================================================================================
| Name | Position | Type | Value |
========================================================================================================================
| :DEPTCODE | 1 | VARCHAR2(32) | /FL0302/ |
| :DAY | 3 | NUMBER | 90 |
| :FLIGHTDATE | 4 | DATE | 06/20/2019 00:00:00 |
========================================================================================================================
Global Stats
=================================================
| Elapsed | Cpu | Other | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls | Gets |
=================================================
| 1155 | 1154 | 1.00 | 1 | 35M |
=================================================
SQL Plan Monitoring Details (Plan Hash Value=1046888249)
============================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (Max) | (%) | (# samples) |
============================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1144 | +12 | 1 | 110 | | | |
| 1 | SORT ORDER BY | | 1 | 34877 | 1144 | +12 | 1 | 110 | 12288 | | |
| 2 | NESTED LOOPS OUTER | | 1 | 34877 | 1150 | +6 | 1 | 110 | | | |
| 3 | NESTED LOOPS OUTER | | 1 | 18809 | 1146 | +6 | 1 | 110 | | | |
| 4 | NESTED LOOPS OUTER | | 1 | 2742 | 1142 | +6 | 1 | 110 | | | |
| 5 | VIEW | | 1 | 2471 | 1142 | +6 | 1 | 110 | | | |
| 6 | HASH GROUP BY | | 1 | 2471 | 1142 | +6 | 1 | 110 | 1M | | |
| 7 | NESTED LOOPS | | 1 | 2470 | 1 | +6 | 1 | 110 | | | |
| 8 | NESTED LOOPS | | 6 | 2470 | 1 | +6 | 1 | 1100 | | | |
| 9 | NESTED LOOPS | | 1 | 2468 | 1 | +6 | 1 | 110 | | | |
| 10 | VIEW | index$_join$_002 | 107 | 113 | 1 | +6 | 1 | 218 | | | |
| 11 | HASH JOIN | | | | 1 | +6 | 1 | 218 | 2M | | |
| 12 | INDEX RANGE SCAN | IDX
$$
_10A230003 | 107 | 18 | 1 | +6 | 1 | 2746 | | | |
| 13 | INDEX FAST FULL SCAN | IDX_T3017 | 107 | 119 | 1 | +6 | 1 | 260 | | | |
| 14 | VIEW PUSHED PREDICATE | | 1 | 22 | 1 | +6 | 218 | 110 | | | |
| 15 | WINDOW SORT PUSHED RANK | | 1 | 22 | 1 | +6 | 218 | 327 | 2048 | | |
| 16 | NESTED LOOPS | | 1 | 21 | 1 | +6 | 218 | 329 | | | |
| 17 | NESTED LOOPS | | 3 | 21 | 1 | +6 | 218 | 386 | | | |
| 18 | TABLE ACCESS BY INDEX ROWID | T3009 | 3 | 18 | 1 | +6 | 218 | 386 | | | |
| 19 | INDEX SKIP SCAN | SYS_C0098286 | 5 | 13 | 1 | +6 | 218 | 568 | | | |
| 20 | INDEX UNIQUE SCAN | PK_T3021 | 1 | | 1150 | +6 | 386 | 386 | | | |
| 21 | TABLE ACCESS BY INDEX ROWID | T3021 | 1 | 1 | 1 | +6 | 386 | 329 | | | |
| 22 | INDEX RANGE SCAN | UK_PUB_FLEET | 6 | 1 | 1 | +6 | 110 | 1100 | | | |
| 23 | TABLE ACCESS BY INDEX ROWID | FLEET_INFO | 1 | 2 | 1 | +6 | 1100 | 110 | | | |
| 24 | VIEW PUSHED PREDICATE | | 1 | 272 | 1009 | +54 | 110 | 9 | | | |
| 25 | SORT GROUP BY | | 1 | 272 | 1009 | +54 | 110 | 9 | 2048 | | |
| 26 | NESTED LOOPS | | 1 | 271 | 1009 | +54 | 110 | 14 | | | |
| 27 | NESTED LOOPS | | 8 | 271 | 1142 | +6 | 110 | 14 | | | |
| 28 | NESTED LOOPS | | 8 | 255 | 1142 | +6 | 110 | 330K | | | |
| 29 | NESTED LOOPS | | 1 | 4 | 1142 | +6 | 110 | 1100 | | | |
| 30 | TABLE ACCESS BY INDEX ROWID | T3017 | 1 | 2 | 1142 | +6 | 110 | 110 | | | |
| 31 | INDEX RANGE SCAN | IDX
$$
_10A230003 | 1 | 1 | 1142 | +6 | 110 | 110 | | | |
| 32 | TABLE ACCESS BY INDEX ROWID | FLEET_INFO | 6 | 2 | 1142 | +6 | 110 | 1100 | | | |
| 33 | INDEX RANGE SCAN | UK_PUB_FLEET | 6 | 1 | 1142 | +6 | 110 | 1100 | | | |
| 34 | TABLE ACCESS BY INDEX ROWID | T3001 | 188 | 251 | 1142 | +6 | 1100 | 330K | | 0.09 | Cpu (1) |
| 35 | INDEX RANGE SCAN | FK_T3001_T9008_PK_T9008 | 3480 | 7 | 1142 | +6 | 1100 | 3M | | | |
| 36 | INDEX UNIQUE SCAN | PK_T3005 | 1 | 1 | 1009 | +54 | 330K | 14 | | 0.09 | Cpu (1) |
| 37 | TABLE ACCESS BY INDEX ROWID | T3005 | 1 | 2 | 1009 | +54 | 14 | 14 | | | |
| 38 | VIEW PUSHED PREDICATE | | 1 | 16067 | 1146 | +6 | 110 | 103 | | | |
| 39 | SORT GROUP BY | | 1 | 16067 | 1146 | +6 | 110 | 108 | 2048 | | |
| 40 | NESTED LOOPS | | 1 | 16066 | 1146 | +6 | 110 | 3314 | | 0.09 | Cpu (1) |
| 41 | NESTED LOOPS | | 10457 | 16066 | 1146 | +6 | 110 | 9M | | | |
| 42 | NESTED LOOPS | | 1 | 4 | 1146 | +6 | 110 | 1100 | | | |
| 43 | TABLE ACCESS BY INDEX ROWID | T3017 | 1 | 2 | 1142 | +6 | 110 | 110 | | | |
| 44 | INDEX RANGE SCAN | IDX
$$
_10A230003 | 1 | 1 | 1144 | +6 | 110 | 110 | | | |
| 45 | TABLE ACCESS BY INDEX ROWID | FLEET_INFO | 6 | 2 | 1146 | +6 | 110 | 1100 | | | |
| 46 | INDEX RANGE SCAN | UK_PUB_FLEET | 6 | 1 | 1146 | +6 | 110 | 1100 | | | |
| 47 | INDEX RANGE SCAN | SELECT_INDEX_NO1_PART | 10457 | 11999 | 1151 | +1 | 1100 | 9M | | 48.69 | Cpu (559) |
| 48 | TABLE ACCESS BY GLOBAL INDEX ROWID | T5001_TASK | 3 | 16062 | 1146 | +6 | 9M | 3314 | | 1.83 | Cpu (21) |
| 49 | VIEW PUSHED PREDICATE | | 1 | 16067 | 1144 | +12 | 110 | 103 | | | |
| 50 | SORT GROUP BY | | 1 | 16067 | 1146 | +10 | 110 | 108 | 2048 | | |
| 51 | NESTED LOOPS | | 1 | 16066 | 1148 | +8 | 110 | 3311 | | | |
| 52 | NESTED LOOPS | | 10457 | 16066 | 1150 | +6 | 110 | 9M | | | |
| 53 | NESTED LOOPS | | 1 | 4 | 1150 | +6 | 110 | 1100 | | | |
| 54 | TABLE ACCESS BY INDEX ROWID | T3017 | 1 | 2 | 1146 | +6 | 110 | 110 | | | |
| 55 | INDEX RANGE SCAN | IDX
$$
_10A230003 | 1 | 1 | 1150 | +6 | 110 | 110 | | | |
| 56 | TABLE ACCESS BY INDEX ROWID | FLEET_INFO | 6 | 2 | 1150 | +6 | 110 | 1100 | | | |
| 57 | INDEX RANGE SCAN | UK_PUB_FLEET | 6 | 1 | 1150 | +6 | 110 | 1100 | | | |
| 58 | INDEX RANGE SCAN | SELECT_INDEX_NO1_PART | 10457 | 11999 | 1150 | +6 | 1100 | 9M | | 47.30 | Cpu (543) |
| 59 | TABLE ACCESS BY GLOBAL INDEX ROWID | T5001_TASK | 3 | 16062 | 1146 | +10 | 9M | 3311 | | 1.92 | Cpu (22) |
============================================================================================================================================================================2.定位问题（DBA）
从以上执行计划定位到对象SELECT_INDEX_NO1_PART的cost最大，走过
查清楚该对象是什么
select * from dba_objects where object_name='SELECT_INDEX_NO1_PART';发现是索引，查下是哪个表的
select * from dba_indexes where index_name ='SELECT_INDEX_NO1_PART';复制表名FIN_ADM.T5001_TASK，在plsql中右键->查询
根据执行计划的嫌疑T5001_TASK，发现问题出现在该表的索引，尝试着收集一下统计信息，详见Oracle执行计划，测试一下执行速度，依旧很慢。
之前一位大师说过，调优就像打太极，急不得！略焦虑的我强忍着迫切的心情出去吃了个苹果，静下心来，仔细对比一下sql文本对应位置与所查索引的字段，仔细阅读过滤条件，发现AND T5001_TASK.FLIGHT_DATE + 90 > TRUNC(date'2019-6-20')处有大嫌疑，+90可能会导致查询结果集变大，从而走索引效率反而可能没有全表扫描高。实践如下。
3.解决方案
在对应位置加入/*+ full(T5001_TASK) */，sql如下
SELECT N.DEP_CODE,
N.P_CODE,
N.AC_TYPE_MACRO,
nvl(T.UPDOWN, 0) + nvl(U.UPDOWN, 0) UP,
nvl(S.UPDOWN, 0) + nvl(U.UPDOWN, 0) DOWN,
N.TECH_NO
FROM (SELECT DISTINCT A.DEP_CODE,
A.P_CODE,
C.AC_TYPE_CREW AS AC_TYPE_MACRO,
b.tech_no TECH_NO
FROM T3017 A,
fleet_info C,
(select a.p_code, a.tech_no, a.aircraft_type
from (select s.tech_no tech_no,
a.aircraft_type,
a.p_code,
row_number() over(PARTITION BY a.p_code, a.tech_ac_type ORDER BY s."LEVEL" ASC) rn
from t3009 a, t3021 s
where nvl(a.tech_no1, 'Z380') = s.tech_no
and s.tech_type = 'F'
AND a.valide_flag = 'Y'
AND s.in_ex = 'Z'
AND s.isvalid = 1
AND s.rank_no IN ('F201', 'F230')) a
where rn = 1) b
WHERE A.P_CODE = B.P_CODE
AND B.aircraft_type = C.ac_type
and c.carrier = 'MF'
AND instr('/FL0302/', A.DEP_CODE) > 0
AND (A.DEP_CODE <> 'FL19' and A.DEP_CODE <> 'FL05' and
A.DEP_CODE <> 'FL07' and A.DEP_CODE <> 'FL06' and
A.DEP_CODE <> 'FL08')
and A.DUTY_CODE = 'F'
AND A.AVAIL_FLAG = 'Y'
AND (A.DEP_CODE <> 'FL06' and A.DEP_CODE <> 'FL07' and
A.DEP_CODE <> 'FL08')
and B.tech_no is not null
group by A.DEP_CODE, A.P_CODE, C.AC_TYPE_CREW, b.tech_no) N,
(SELECT /*+ full(T5001_TASK) */T3017.P_CODE,
fleet_info.AC_TYPE_CREW AS AC_TYPE_MACRO,
COUNT(1) updown
FROM T5001_TASK, T3017, fleet_info ------ FLIGHT_DATE, AC_TYPE, CONTROL_PERSON, CARRIER
WHERE fleet_info.AC_TYPE = T5001_TASK.AC_TYPE
and fleet_info.carrier = 'MF'
AND instr('/FL0302/', T3017.DEP_CODE) > 0
AND (T3017.DEP_CODE <> 'FL19' and T3017.DEP_CODE <> 'FL05' and
T3017.DEP_CODE <> 'FL07' and T3017.DEP_CODE <> 'FL06' and
T3017.DEP_CODE <> 'FL08')
AND T3017.DUTY_CODE = 'F'
AND T3017.AVAIL_FLAG = 'Y'
AND T5001_TASK.FLIGHT_DATE + 90 > TRUNC(date '2019-6-20')
AND T5001_TASK.FLIGHT_DATE <= TRUNC(date '2019-6-20')
AND T5001_TASK.FLIGHT_TYPE <> 'K'
AND ((T3017.P_CODE = T5001_TASK.LEFT_P_CODE AND
T5001_TASK.CONTROL_PERSON = 'L') OR
(T3017.P_CODE = T5001_TASK.RIGHT_P_CODE AND
T5001_TASK.CONTROL_PERSON = 'R'))
GROUP BY T3017.P_CODE, fleet_info.AC_TYPE_CREW) T,
(SELECT /*+ full(T5001_TASK) */T3017.P_CODE,
fleet_info.AC_TYPE_CREW AS AC_TYPE_MACRO,
COUNT(1) updown
FROM T5001_TASK, T3017, fleet_info -------
WHERE fleet_info.AC_TYPE = T5001_TASK.AC_TYPE
and fleet_info.carrier = 'MF'
AND instr('/FL0302/', T3017.DEP_CODE) > 0
AND (T3017.DEP_CODE <> 'FL19' and T3017.DEP_CODE <> 'FL05' and
T3017.DEP_CODE <> 'FL07' and T3017.DEP_CODE <> 'FL06' and
T3017.DEP_CODE <> 'FL08')
AND T3017.DUTY_CODE = 'F'
AND T3017.AVAIL_FLAG = 'Y'
AND T5001_TASK.FLIGHT_DATE > TRUNC(date '2019-6-20') -90
AND T5001_TASK.FLIGHT_DATE <= TRUNC(date '2019-6-20')
AND T5001_TASK.FLIGHT_TYPE <> 'K'
AND ((T3017.P_CODE = T5001_TASK.Land_Left_p_Code AND
T5001_TASK.Land_Control_Person = 'L') OR
(T3017.P_CODE = T5001_TASK.Land_Right_p_Code AND
T5001_TASK.Land_Control_Person = 'R'))
GROUP BY T3017.P_CODE, fleet_info.AC_TYPE_CREW) S,
(SELECT T3017.P_CODE,
FLEET_INFO.Ac_Type_Crew AS AC_TYPE_MACRO,
SUM(T3005.UPDOWN) UPDOWN
FROM T3001, T3005, T3017, FLEET_INFO
WHERE FLEET_INFO.AC_TYPE = T3001.AC_TYPE
AND FLEET_INFO.CARRIER = 'MF'
AND instr('/FL0302/', T3017.DEP_CODE) > 0
AND (T3017.DEP_CODE <> 'FL19' and T3017.DEP_CODE <> 'FL05' and
T3017.DEP_CODE <> 'FL07' and T3017.DEP_CODE <> 'FL06' and
T3017.DEP_CODE <> 'FL08')
AND T3017.DUTY_CODE = 'F'
AND T3017.AVAIL_FLAG = 'Y'
AND T3005.P_CODE = T3017.P_CODE
AND T3005.FLIGHT_DATE > TRUNC(date '2019-6-20') -90
AND T3005.FLIGHT_DATE <= TRUNC(date '2019-6-20')
AND T3001.Flight_Date = T3005.FLIGHT_DATE
AND T3001.CREW_LINK_LINE = T3005.CREW_LINK_LINE
AND T3001.FLIGHT_TYPE = 'X'
GROUP BY T3017.P_CODE, FLEET_INFO.AC_TYPE_CREW) U
WHERE N.p_code = T.p_code(+)
AND N.ac_type_macro = T.ac_type_macro(+)
AND N.p_code = S.p_code(+)
AND N.ac_type_macro = S.ac_type_macro(+)
AND N.p_code = U.p_code(+)
AND N.ac_type_macro = U.ac_type_macro(+)
ORDER BY N.DEP_CODE, N.P_CODE, N.AC_TYPE_MACRO查看执行计划
Plan Hash Value : 690382872
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 44 | 4708 | 49486 | 00:09:54 |
| 1 | SORT ORDER BY | | 44 | 4708 | 49486 | 00:09:54 |
| * 2 | HASH JOIN OUTER | | 44 | 4708 | 49485 | 00:09:54 |
| * 3 | HASH JOIN OUTER | | 44 | 3520 | 46170 | 00:09:15 |
| * 4 | HASH JOIN OUTER | | 44 | 2332 | 306 | 00:00:04 |
| 5 | VIEW | | 44 | 1144 | 147 | 00:00:02 |
| 6 | HASH GROUP BY | | 44 | 2904 | 147 | 00:00:02 |
| 7 | NESTED LOOPS | | 51 | 3366 | 146 | 00:00:02 |
| 8 | NESTED LOOPS | | 71 | 3366 | 146 | 00:00:02 |
| * 9 | HASH JOIN | | 71 | 3195 | 75 | 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | FLEET_INFO | 6 | 78 | 2 | 00:00:01 |
| * 11 | INDEX RANGE SCAN | UK_PUB_FLEET | 6 | | 1 | 00:00:01 |
| * 12 | VIEW | | 150 | 4800 | 73 | 00:00:01 |
| * 13 | WINDOW SORT PUSHED RANK | | 150 | 6750 | 73 | 00:00:01 |
| * 14 | HASH JOIN | | 150 | 6750 | 72 | 00:00:01 |
| * 15 | TABLE ACCESS FULL | T3021 | 1 | 19 | 4 | 00:00:01 |
| * 16 | TABLE ACCESS FULL | T3009 | 8923 | 231998 | 68 | 00:00:01 |
| * 17 | INDEX UNIQUE SCAN | PK_T3017 | 1 | | 0 | 00:00:01 |
| * 18 | TABLE ACCESS BY INDEX ROWID | T3017 | 1 | 21 | 1 | 00:00:01 |
| 19 | VIEW | | 1 | 27 | 158 | 00:00:02 |
| 20 | HASH GROUP BY | | 1 | 77 | 158 | 00:00:02 |
| 21 | NESTED LOOPS | | 1 | 77 | 157 | 00:00:02 |
| 22 | NESTED LOOPS | | 17 | 77 | 157 | 00:00:02 |
| 23 | NESTED LOOPS | | 17 | 952 | 140 | 00:00:02 |
| * 24 | HASH JOIN | | 17 | 544 | 91 | 00:00:02 |
| 25 | TABLE ACCESS BY INDEX ROWID | FLEET_INFO | 6 | 78 | 2 | 00:00:01 |
| * 26 | INDEX RANGE SCAN | UK_PUB_FLEET | 6 | | 1 | 00:00:01 |
| 27 | TABLE ACCESS BY INDEX ROWID | T3001 | 55 | 1045 | 89 | 00:00:02 |
| 28 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 29 | BITMAP AND | | | | | |
| 30 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| * 31 | INDEX RANGE SCAN | FK_T3001_T9008_PK_T9008 | 3480 | | 8 | 00:00:01 |
| 32 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| 33 | SORT ORDER BY | | | | | |
| * 34 | INDEX RANGE SCAN | PK_T3001 | 3480 | | 65 | 00:00:01 |
| 35 | TABLE ACCESS BY INDEX ROWID | T3005 | 1 | 24 | 3 | 00:00:01 |
| * 36 | INDEX RANGE SCAN | PK_T3005 | 1 | | 2 | 00:00:01 |
| * 37 | INDEX UNIQUE SCAN | PK_T3017 | 1 | | 0 | 00:00:01 |
| * 38 | TABLE ACCESS BY INDEX ROWID | T3017 | 1 | 21 | 1 | 00:00:01 |
| 39 | VIEW | | 227 | 6129 | 45865 | 00:09:11 |
| 40 | HASH GROUP BY | | 227 | 15436 | 45865 | 00:09:11 |
| * 41 | HASH JOIN | | 2099 | 142732 | 45863 | 00:09:11 |
| 42 | MERGE JOIN CARTESIAN | | 609 | 20706 | 231 | 00:00:03 |
| * 43 | VIEW | index$_join$_010 | 107 | 2247 | 113 | 00:00:02 |
| * 44 | HASH JOIN | | | | | |
| * 45 | INDEX RANGE SCAN | IDX
$$
_10A230003 | 107 | 2247 | 18 | 00:00:01 |
| * 46 | INDEX FAST FULL SCAN | IDX_T3017 | 107 | 2247 | 119 | 00:00:02 |
| 47 | BUFFER SORT | | 6 | 78 | 118 | 00:00:02 |
| * 48 | TABLE ACCESS FULL | FLEET_INFO | 6 | 78 | 1 | 00:00:01 |
| 49 | PARTITION RANGE ITERATOR | | 115835 | 3938390 | 45615 | 00:09:08 |
| * 50 | TABLE ACCESS FULL | T5001_TASK | 115835 | 3938390 | 45615 | 00:09:08 |
| 51 | VIEW | | 227 | 6129 | 3315 | 00:00:40 |
| 52 | HASH GROUP BY | | 227 | 15436 | 3315 | 00:00:40 |
| * 53 | HASH JOIN | | 3100 | 210800 | 3314 | 00:00:40 |
| 54 | MERGE JOIN CARTESIAN | | 609 | 20706 | 231 | 00:00:03 |
| * 55 | VIEW | index$_join$_014 | 107 | 2247 | 113 | 00:00:02 |
| * 56 | HASH JOIN | | | | | |
| * 57 | INDEX RANGE SCAN | IDX
$$
_10A230003 | 107 | 2247 | 18 | 00:00:01 |
| * 58 | INDEX FAST FULL SCAN | IDX_T3017 | 107 | 2247 | 119 | 00:00:02 |
| 59 | BUFFER SORT | | 6 | 78 | 118 | 00:00:02 |
| * 60 | TABLE ACCESS FULL | FLEET_INFO | 6 | 78 | 1 | 00:00:01 |
| 61 | PARTITION RANGE SINGLE | | 74875 | 2545750 | 3063 | 00:00:37 |
| * 62 | TABLE ACCESS FULL | T5001_TASK | 74875 | 2545750 | 3063 | 00:00:37 |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("N"."P_CODE"="S"."P_CODE"(+) AND "N"."AC_TYPE_MACRO"="S"."AC_TYPE_MACRO"(+))
* 3 - access("N"."P_CODE"="T"."P_CODE"(+) AND "N"."AC_TYPE_MACRO"="T"."AC_TYPE_MACRO"(+))
* 4 - access("N"."P_CODE"="U"."P_CODE"(+) AND "N"."AC_TYPE_MACRO"="U"."AC_TYPE_MACRO"(+))
* 9 - access("A"."AIRCRAFT_TYPE"="C"."AC_TYPE")
* 11 - access("C"."CARRIER"='MF')
* 12 - filter("RN"=1)
* 13 - filter(ROW_NUMBER() OVER ( PARTITION BY "A"."P_CODE","A"."TECH_AC_TYPE" ORDER BY "S"."LEVEL")<=1)
* 14 - access("S"."TECH_NO"=NVL("A"."TECH_NO1",'Z380'))
* 15 - filter("S"."IN_EX"='Z' AND ("S"."RANK_NO"='F201' OR "S"."RANK_NO"='F230') AND TO_NUMBER("S"."ISVALID")=1 AND "S"."TECH_TYPE"='F')
* 16 - filter("A"."VALIDE_FLAG"='Y')
* 17 - access("A"."P_CODE"="A"."P_CODE")
* 18 - filter(INSTR('/FL0302/',"A"."DEP_CODE")>0 AND "A"."DUTY_CODE"='F' AND "A"."AVAIL_FLAG"='Y' AND "A"."DEP_CODE"<>'FL07' AND "A"."DEP_CODE"<>'FL19' AND "A"."DEP_CODE"<>'FL05' AND
"A"."DEP_CODE"<>'FL06' AND "A"."DEP_CODE"<>'FL08')
* 24 - access("FLEET_INFO"."AC_TYPE"="T3001"."AC_TYPE")
* 26 - access("FLEET_INFO"."CARRIER"='MF')
* 31 - access("T3001"."FLIGHT_TYPE"='X')
* 34 - access("T3001"."FLIGHT_DATE">TO_DATE(' 2019-03-22 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T3001"."FLIGHT_DATE"<=TO_DATE(' 2019-06-20 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
* 34 - filter("T3001"."FLIGHT_DATE"<=TO_DATE(' 2019-06-20 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T3001"."FLIGHT_DATE">TO_DATE(' 2019-03-22 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
* 36 - access("T3001"."FLIGHT_DATE"="T3005"."FLIGHT_DATE" AND "T3001"."CREW_LINK_LINE"="T3005"."CREW_LINK_LINE")
* 36 - filter("T3005"."FLIGHT_DATE">TO_DATE(' 2019-03-22 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T3005"."FLIGHT_DATE"<=TO_DATE(' 2019-06-20 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
* 37 - access("T3005"."P_CODE"="T3017"."P_CODE")
* 38 - filter(INSTR('/FL0302/',"T3017"."DEP_CODE")>0 AND "T3017"."DUTY_CODE"='F' AND "T3017"."AVAIL_FLAG"='Y' AND "T3017"."DEP_CODE"<>'FL07' AND "T3017"."DEP_CODE"<>'FL19' AND
"T3017"."DEP_CODE"<>'FL05' AND "T3017"."DEP_CODE"<>'FL06' AND "T3017"."DEP_CODE"<>'FL08')
* 41 - access("FLEET_INFO"."AC_TYPE"="T5001_TASK"."AC_TYPE")
* 41 - filter("T3017"."P_CODE"="T5001_TASK"."LEFT_P_CODE" AND "T5001_TASK"."CONTROL_PERSON"='L' OR "T3017"."P_CODE"="T5001_TASK"."RIGHT_P_CODE" AND "T5001_TASK"."CONTROL_PERSON"='R')
* 43 - filter("T3017"."DUTY_CODE"='F')
* 44 - access(ROWID=ROWID)
* 45 - access("T3017"."DUTY_CODE"='F')
* 46 - filter("T3017"."DEP_CODE"<>'FL08' AND "T3017"."DEP_CODE"<>'FL06' AND "T3017"."DEP_CODE"<>'FL07' AND "T3017"."DEP_CODE"<>'FL05' AND "T3017"."DEP_CODE"<>'FL19' AND
INSTR('/FL0302/',"T3017"."DEP_CODE")>0 AND "T3017"."AVAIL_FLAG"='Y')
* 48 - filter("FLEET_INFO"."CARRIER"='MF')
* 50 - filter("T5001_TASK"."FLIGHT_TYPE"<>'K' AND INTERNAL_FUNCTION("T5001_TASK"."FLIGHT_DATE")+90>TO_DATE(' 2019-06-20 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T5001_TASK"."FLIGHT_DATE"<=TO_DATE('
2019-06-20 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
* 53 - access("FLEET_INFO"."AC_TYPE"="T5001_TASK"."AC_TYPE")
* 53 - filter("T3017"."P_CODE"="T5001_TASK"."LAND_LEFT_P_CODE" AND "T5001_TASK"."LAND_CONTROL_PERSON"='L' OR "T3017"."P_CODE"="T5001_TASK"."LAND_RIGHT_P_CODE" AND
"T5001_TASK"."LAND_CONTROL_PERSON"='R')
* 55 - filter("T3017"."DUTY_CODE"='F')
* 56 - access(ROWID=ROWID)
* 57 - access("T3017"."DUTY_CODE"='F')
* 58 - filter("T3017"."DEP_CODE"<>'FL08' AND "T3017"."DEP_CODE"<>'FL06' AND "T3017"."DEP_CODE"<>'FL07' AND "T3017"."DEP_CODE"<>'FL05' AND "T3017"."DEP_CODE"<>'FL19' AND
INSTR('/FL0302/',"T3017"."DEP_CODE")>0 AND "T3017"."AVAIL_FLAG"='Y')
* 60 - filter("FLEET_INFO"."CARRIER"='MF')
* 62 - filter("T5001_TASK"."FLIGHT_DATE">TO_DATE(' 2019-03-22 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T5001_TASK"."FLIGHT_TYPE"<>'K' AND "T5001_TASK"."FLIGHT_DATE"<=TO_DATE(' 2019-06-20 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))耗时4秒，解决问题