Oracle调优-索引的锅

前言

数据库近日做了一次迁移,迁移前一切正常,迁移后业务发现有个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';

Oracle调优-索引的锅

发现是索引,查下是哪个表的

select * from dba_indexes where index_name ='SELECT_INDEX_NO1_PART';

Oracle调优-索引的锅

复制表名FIN_ADM.T5001_TASK,在plsql中右键->查询
Oracle调优-索引的锅

根据执行计划的嫌疑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'))

Oracle调优-索引的锅

耗时4秒,解决问题

上一篇:ios registerNib: and registerClass:


下一篇:IOS 类别