低效SQL语句经典案例解析（下）

数据与人 2020-12-15

855

笔者整理了一下之前做的低效SQL优化案例，都是非常经典的案例，分享给大家，也希望能够抛砖引玉。

【案例大纲汇总】

【写hint后没有验证】

优化建议：

sql语句中hint语句未生效，

应改写为：
/*+ INDEX(A,IDX_B_L_DEBITREC_SX)*/ ；

SQL_ID        PLAN_HASH_VALUE Date time              No.execs         LIO/exec CPUTIM/exec  ETIME/exec       PIO/exec     ROWs/exec
------------- --------------- -------------------- ---------- ---------------- ----------- ----------- -------------- -------------
96fasbg4v1ff9       281022494 06/24/19_1100_1200          326         11910.99         .08         .17         520.67         12.10
96fasbg4v1ff9       281022494 06/24/19_1300_1400          299         13949.49         .10         .20         615.02         14.92
96fasbg4v1ff9       281022494 06/24/19_1400_1500          255         22183.32         .15         .30         880.25         18.50
96fasbg4v1ff9       281022494 06/24/19_1500_1600          286         21241.05         .14         .28         582.91         17.49
96fasbg4v1ff9       281022494 06/24/19_1600_1700          459         17661.18         .12         .22         385.00         11.90
96fasbg4v1ff9       281022494 06/24/19_1700_1800          559         16421.39         .10         .17          92.42         10.77
96fasbg4v1ff9       281022494 06/24/19_1800_1900          165         15973.18         .11         .20         477.39         37.49
96fasbg4v1ff9       281022494 06/24/19_2300_0000           92         27817.39         .19         .38        1381.47         70.40
96fasbg4v1ff9       281022494 06/25/19_0300_0400           15         18278.07         .18         .48        3918.53        434.60
96fasbg4v1ff9       281022494 06/25/19_0400_0500           78         21851.41         .14         .22          37.64         84.58
96fasbg4v1ff9       281022494 06/25/19_0500_0600           25         39803.00         .24         .38            .00        264.88
96fasbg4v1ff9       281022494 06/25/19_0800_0900           21         16114.81         .11         .29        1922.29        316.38
96fasbg4v1ff9       281022494 06/25/19_0900_1000           62         13864.23         .11         .25        1368.90        108.16

SQL_ID 96fasbg4v1ff9
--------------------
SELECT /*+INDEX(IDX_B_L_DEBITREC_SX)*/ 
  FROM tablename A 
 WHERE  A.POLICY_NO = :B3 
   AND  A.CLASS_CODE = :B2 
   AND A.BRANCH_CODE IN (:B1 ,'Z000000')


Plan hash value: 281022494


-------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |              |       |       |    14 (100)|          |       |       |
|   1 |  SORT AGGREGATE        |              |     1 |    35 |            |          |       |       |
|   2 |   PARTITION LIST INLIST|              |     1 |    35 |    14   (0)| 00:00:01 |KEY(I) |KEY(I) |
|   3 |    PARTITION HASH ALL  |              |     1 |    35 |    14   (0)| 00:00:01 |     1 |     8 |
|   4 |     TABLE ACCESS FULL  | B_L_DEBITREC |     1 |    35 |    14   (0)| 00:00:01 |KEY(I) |KEY(I) |
-------------------------------------------------------------------------------------------------------

【空心表】

空心表是指表占了大量存储空间，但数据却很少。

SQL_ID        PLAN_HASH_VALUE Date time              No.execs         LIO/exec CPUTIM/exec  ETIME/exec       PIO/exec     ROWs/exec------------- --------------- -------------------- ---------- ---------------- ----------- ----------- -------------- -------------75459krvwrnwb      3682506797 06/21/19_2100_2200          721             1.00         .00         .00            .00           .0075459krvwrnwb      3682506797 06/21/19_2200_2300          711             1.00         .00         .00            .00           .0075459krvwrnwb      3682506797 06/21/19_2300_0000          721             1.00         .00         .00            .00           .00

SQL_ID 75459krvwrnwb
--------------------
SELECT A.AREACODE,
       A.COUNTRYCODE,
       A.TEL,
       A.EXT,
       A.TELNUMBER,
       E.CODESTRING       USAGE,
       A.PID,
       A.ID15,
       B.CODESTRING       IDTYPE,
       A.SOURCE_PERSON_ID,
       A.TYPE,
       A.BUSI_USAGE,
       A.AUTHEN_TYPE,
       A.RD
  FROM (SELECT T.*, ROWID RD
          FROM TABLENAME T
         WHERE T.DEAL_FLAG = 0
           AND rownum <= :1) A
  LEFT JOIN TABLENAME B
    ON A.IDTYPE = B.VALUE
   AND B.TABLENAME = 'person'
   AND B.ATTRNAME = 'idtype'
  LEFT JOIN TABLENAME E
    ON A.USAGE = E.VALUE
   AND E.TABLENAME = 'phone'
   AND E.ATTRNAME = 'phone_type'


Plan hash value: 3682506797


-------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                       |       |       |  6374 (100)|          |
|   1 |  NESTED LOOPS OUTER           |                       |     1 |   307 |  6374   (1)| 00:01:17 |
|   2 |   NESTED LOOPS OUTER          |                       |     1 |   273 |  6372   (1)| 00:01:17 |
|   3 |    VIEW                       |                       |     1 |   239 |  6370   (1)| 00:01:17 |
|   4 |     COUNT STOPKEY             |                       |       |       |            |          |
|   5 |      TABLE ACCESS FULL        | TABLENAME             |     1 |   241 |  6370   (1)| 00:01:17 |
|   6 |    TABLE ACCESS BY INDEX ROWID| TABLENAME1            |     1 |    34 |     2   (0)| 00:00:01 |
|   7 |     INDEX RANGE SCAN          | MDM_ODS_CODESTRING_01 |     1 |       |     1   (0)| 00:00:01 |
|   8 |   TABLE ACCESS BY INDEX ROWID | TABLENAME             |     1 |    34 |     2   (0)| 00:00:01 |
|   9 |    INDEX RANGE SCAN           | MDM_ODS_CODESTRING_01 |    14 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
OWNER           TABLE_NAME                total_size(M) used_size(M) wasted_size(M) WASTED_PERCENT  LAST_ANALYZED         NUM_ROWS
--------------- ------------------------- ------------- ------------ -------------- --------------- ------------------- ----------
OWNER             TABLENAME                  183.45            0         183.45 100%            2017-09-23 22:08:15          0

此表为空心表，可以做一次降高水位处理

SQL> desc tablename;
 Name                    Null?    Type
 ----------------------- -------- ----------------
 SNO                              NUMBER(20)
 TYPEID                           NUMBER(7)
 AREACODE                         VARCHAR2(20)
 COUNTRYCODE                      VARCHAR2(20)
 TEL                              VARCHAR2(20)
 EXT                              VARCHAR2(20)
 TYPE                             VARCHAR2(1)
 TELNUMBER                        VARCHAR2(100)
 USAGE                            VARCHAR2(1)
 PERSON_ID                        NUMBER(20)
 PID                              VARCHAR2(40)
 IDTYPE                           VARCHAR2(2)
 PURPOSE                          VARCHAR2(1)
 SEQ                              VARCHAR2(10)
 SOURCE_PERSON_ID                 VARCHAR2(20)
 ID15                             VARCHAR2(40)
 WORKDAY_CNTCT_TIME               VARCHAR2(17)
 WEEKEND_CNTCT_TIME               VARCHAR2(17)
 BUSI_USAGE                       VARCHAR2(1)
 CONTACTED_IND                    VARCHAR2(1)
 BEGTIME                          NUMBER(11)
 ENDTIME                          NUMBER(11)
 BRANCH                           CHAR(14)
 ETL_TIME                         DATE
 SRC_SYS                          VARCHAR2(20)
-- DEAL_FLAG                        VARCHAR2(1)
 DEAL_COUNT                       NUMBER(10)
 AUTHEN_TYPE                      VARCHAR2(120)

【SQL语句设计不合理】

一、exists改写

SQL语句3zqgcv1pysyyp

该语句为dblink远程关联查询语句，语句消耗非常大，

其执行时间为00:02:38.31（S）,逻辑读为2624K，

其语句是EXISTS连接，建议将EXISTS连接改写为join 方式连接；

改写后，语句执行结果一致，其执行时间为00:00:00.23（S）,逻辑读为2652，

详细分析如下；

--SQL_ID 3zqgcv1pysyyp


    SnapId PLAN_HASH_VALUE Date time            No. of exec        LIO/exec CPUTIM/exec  ETIME/exec    PIO/exec   ROWs/exec
---------- --------------- -------------------- ----------- --------------- ----------- ----------- ----------- -----------
    33047      2329456640 07/25/18_1500_1600           297      2035184.01       10.91       12.34        5.08       40.94
     33048      2329456640 07/25/18_1600_1700           792             .00       10.87       12.92        4.90       16.35
     33064      2329456640 07/26/18_0800_0900           204      2137394.93       12.04       12.87       51.53        1.00
     33065      2329456640 07/26/18_0900_1000           283      1863929.26       10.57       11.81        3.49        1.72
     33066      2329456640 07/26/18_1000_1100           267      1997147.04       10.82       12.32        5.37        2.82
     33067      2329456640 07/26/18_1100_1200           284      2006242.49       10.73       12.09        5.69        3.65
     33068      2329456640 07/26/18_1200_1300           281      2080810.79       11.11       11.99        3.48        4.69
     33069      2329456640 07/26/18_1300_1400           275      2123700.40       11.10       12.33        3.32        5.79
     33070      2329456640 07/26/18_1400_1500           281      2153854.10       11.17       12.31        3.93        6.67
     33071      2329456640 07/26/18_1500_1600           292             .00       10.77       11.63        3.62        7.42
     33072      2329456640 07/26/18_1600_1700           287      2101076.17       11.17       11.85        4.86        8.55
     33073      2329456640 07/26/18_1700_1800           279      1939129.91       10.52       11.76        3.01        9.79
     33074      2329456640 07/26/18_1800_1900           301      1853067.86       10.22       11.09        5.48       10.08
     33075      2329456640 07/26/18_1900_2000           282      1915417.34       10.39       11.34        4.51       11.76
     33076      2329456640 07/26/18_2000_2100           287      2089227.70       11.10       11.87        4.48       12.55
     33077      2329456640 07/26/18_2100_2200           291      2078090.54       10.74       11.44        4.86       13.38
     33078      2329456640 07/26/18_2200_2300           277      2254932.59       11.65       12.28        4.13       15.05
     33079      2329456640 07/26/18_2300_0000           288             .00       11.09       11.77        3.68       15.48
     33080      2329456640 07/27/18_0000_0100           265      2213931.24       11.44       12.14        2.67       17.82
     33081      2329456640 07/27/18_0100_0200           284      2229272.67       11.10       11.78        3.65       17.63
     33082      2329456640 07/27/18_0200_0300           279      1834529.52        9.81       10.57        9.23       18.95
     33083      2329456640 07/27/18_0300_0400           215      1451296.24        8.71        9.51       11.93       25.59
     33088      2329456640 07/27/18_0800_0900           256      2087902.20       11.91       13.47      111.43       22.49
     33089      2329456640 07/27/18_0900_1000           281      1934691.31       10.69       11.86        3.12       21.49
     33090      2329456640 07/27/18_1000_1100           259      1943204.27       10.76       12.35        5.96       24.32
     33091      2329456640 07/27/18_1100_1200           269             .00       11.00       12.62        5.33       24.41
     33092      2329456640 07/27/18_1200_1300           282      2081425.84       11.14       12.13        2.61       24.29

SQL_ID 3zqgcv1pysyyp
--------------------
SELECT SUM(NVL(B.BILL_AMT, 0))
  FROM A@LINK A, B@LINK B
 WHERE A.CLAIM_NO = B.CLAIM_NO
   AND A.RECEIPT_NO = B.RECEIPT_NO
   AND A.RECEIPT_TYPE = :B1
   AND EXISTS (SELECT 1
          FROM C C, D D
         WHERE C.POLICY_NO = D.POLICYNO
           AND C.GPOLICYNO = :B5
           AND D.GROUPS = :B4
           AND D.GROUPNAME = :B3
           AND C.END_DATE <= :B2
           AND A.CLAIM_NO = C.CLAIM_NO
           AND C.STATUS = '01')


SUM(NVL(B.BILL_AMT,0))
----------------------
                150.69


Elapsed: 00:02:38.44
SQL> 


Plan hash value: 2329456640


---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                         | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                              |      1 |        |  5829K(100)|      1 |00:02:38.31 |    2624K|  44657 |       |       |      |
|   1 |  SORT AGGREGATE                 |                              |      1 |      1 |            |      1 |00:02:38.31 |    2624K|  44657 |       |       |      |
|*  2 |   FILTER                        |                              |      1 |        |            |      5 |00:02:38.31 |    2624K|  44657 |       |       |      |
|*  3 |    HASH JOIN                    |                              |      1 |    832K| 42310   (1)|   3924K|00:00:12.82 |       0 |      0 |   166M|    11M|  169M (0)|
|   4 |     REMOTE                      | A                            |      1 |    832K| 11248   (1)|   2341K|00:00:01.94 |       0 |      0 |       |       |      |
|   5 |     REMOTE                      | B                            |      1 |   5190K| 16885   (1)|   5194K|00:00:04.77 |       0 |      0 |       |       |      |
|   6 |    NESTED LOOPS                 |                              |    655K|      1 |     7   (0)|      1 |00:02:23.77 |    2624K|  44657 |       |       |      |
|   7 |     NESTED LOOPS                |                              |    655K|      1 |     7   (0)|    984 |00:02:20.33 |    2623K|  44276 |       |       |      |
|*  8 |      TABLE ACCESS BY INDEX ROWID| C                            |    655K|      1 |     4   (0)|    984 |00:02:19.91 |    2620K|  44256 |       |       |      |
|*  9 |       INDEX RANGE SCAN          | IDX_T_LP_CLAIM_DATA_03       |    655K|      1 |     3   (0)|    669K|00:00:08.67 |    1962K|   2675 |       |       |      |
|* 10 |      INDEX RANGE SCAN           | IDX_T_LP_GRPLIST_RELATION_01 |    984 |      1 |     2   (0)|    984 |00:00:00.13 |    2948 |     20 |       |       |      |
|* 11 |     TABLE ACCESS BY INDEX ROWID | D                            |    984 |      1 |     3   (0)|      1 |00:00:03.09 |     972 |    381 |       |       |      |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   2 - filter( IS NOT NULL)
   3 - access("A"."CLAIM_NO"="B"."CLAIM_NO" AND "A"."RECEIPT_NO"="B"."RECEIPT_NO")
   8 - filter(("C"."GPOLICYNO"=:B5 AND "C"."END_DATE"<=:B2 AND "C"."STATUS"='01'))
   9 - access("C"."CLAIM_NO"=:B1)
  10 - access("C"."POLICY_NO"="D"."POLICYNO")
  11 - filter(("D"."GROUPNAME"=:B3 AND "D"."GROUPS"=:B4))


40 rows selected.


Elapsed: 00:00:01.03
SQL>

--sql改写后

SQL> 


Plan hash value: 2588496186


------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                         | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                              |      1 |        |    30 (100)|      1 |00:00:00.23 |    2652 |     10 |
|   1 |  SORT AGGREGATE                 |                              |      1 |      1 |            |      1 |00:00:00.23 |    2652 |     10 |
|   2 |   NESTED LOOPS                  |                              |      1 |      1 |    30   (0)|      5 |00:00:00.23 |    2652 |     10 |
|   3 |    NESTED LOOPS                 |                              |      1 |      1 |    27   (0)|      2 |00:00:00.17 |    2652 |     10 |
|   4 |     NESTED LOOPS                |                              |      1 |      1 |    24   (0)|      1 |00:00:00.08 |    2652 |     10 |
|*  5 |      TABLE ACCESS BY INDEX ROWID| C                            |      1 |      6 |     6   (0)|    868 |00:00:00.02 |     155 |      6 |
|*  6 |       INDEX RANGE SCAN          | IDX_T_LP_CLAIM_DATA_04       |      1 |      7 |     3   (0)|    868 |00:00:00.01 |       6 |      6 |
|*  7 |      TABLE ACCESS BY INDEX ROWID| D                            |    868 |      1 |     3   (0)|      1 |00:00:00.06 |    2497 |      4 |
|*  8 |       INDEX RANGE SCAN          | IDX_T_LP_GRPLIST_RELATION_01 |    868 |      1 |     2   (0)|    868 |00:00:00.02 |    1727 |      1 |
|   9 |     REMOTE                      | A                            |      1 |      2 |     3   (0)|      2 |00:00:00.09 |       0 |      0 |
|  10 |    REMOTE                       | B                            |      2 |      1 |     3   (0)|      5 |00:00:00.06 |       0 |      0 |
------------------------------------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   5 - filter(("C"."END_DATE"<=:B2 AND "C"."STATUS"='01'))
   6 - access("C"."GPOLICYNO"=:B5)
   7 - filter(("D"."GROUPNAME"=:B3 AND "D"."GROUPS"=:B4))
   8 - access("C"."POLICY_NO"="D"."POLICYNO")


35 rows selected.


Elapsed: 00:00:00.13

二、with as 改写

CPU使用率为98.01%，排查原因主要是同一时间大量执行同类SQL语句。

进行关联查询的同类SQL语句，其中SQL语句冗余同类SQL语句建议对SQL语句改写，并采用绑定变量形式。

--优化前，其执行时间为 00:02:40.76 s，逻辑读为 21M ；
--优化后，其执行时间为 00:00:00.01 s，逻辑读为 3 ；

--用with as改写，与原语句结果一致；

详情如下：

SQL_ID        PLAN_HASH_VALUE Date time              No.execs         LIO/exec CPUTIM/exec  ETIME/exec       PIO/exec     ROWs/exec
------------- --------------- -------------------- ---------- ---------------- ----------- ----------- -------------- -------------
02k8cp1hgw1qk      2928302935 03/26/19_0900_1000            1      20370426.00       72.59       72.59            .00          1.00

--优化前，原执行计划，其执行时间为 00:02:40.76，逻辑读为 21M ；

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last +cost')); 


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  ch9a907wxtvd1, child number 0


Plan hash value: 2928302935


------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name             | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY                  |                  |      1 |        |            |      0 |00:02:40.76 |      21M|      0 |
|   2 |   NESTED LOOPS SEMI             |                  |      1 |      1 |     9   (0)|      0 |00:02:40.76 |      21M|      0 |
|   3 |    NESTED LOOPS                 |                  |      1 |      1 |     6   (0)|   4060K|00:01:49.64 |      12M|      0 |
|*  4 |     INDEX FAST FULL SCAN        | IND_DSFTOBANK_01 |      1 |   3959K|     2   (0)|   4060K|00:00:04.06 |   75294 |      0 |
|   5 |     TABLE ACCESS BY INDEX ROWID |                  |   4060K|      1 |     4   (0)|   4060K|00:01:38.87 |      12M|      0 |
|*  6 |      INDEX RANGE SCAN           | IND_NREGCLM_1    |   4060K|      1 |     2   (0)|   4060K|00:00:55.13 |    8122K|      0 |
|*  7 |    INDEX RANGE SCAN             | PK_NCLMCCL       |   3157K|     24M|     3   (0)|      0 |00:00:44.89 |    9472K|      0 |
|   8 |  VIEW                           |                  |      1 |      1 |    12   (0)|      1 |00:00:00.03 |      11 |      4 |
|   9 |   SORT AGGREGATE                |                  |      1 |      1 |            |      1 |00:00:00.03 |      11 |      4 |
|  10 |    NESTED LOOPS                 |                  |      1 |      4 |    12   (0)|      1 |00:00:00.03 |      11 |      4 |
|  11 |     NESTED LOOPS                |                  |      1 |      2 |     8   (0)|      1 |00:00:00.03 |       8 |      4 |
|  12 |      TABLE ACCESS BY INDEX ROWID|                  |      1 |      1 |     5   (0)|      1 |00:00:00.02 |       4 |      3 |
|* 13 |       INDEX RANGE SCAN          | IND_NREGCLM_1    |      1 |      1 |     3   (0)|      1 |00:00:00.02 |       3 |      2 |
|* 14 |      INDEX RANGE SCAN           | PK_NCLMCCL       |      1 |      2 |     3   (0)|      1 |00:00:00.01 |       4 |      1 |
|* 15 |     INDEX RANGE SCAN            | IND_DSFTOBANK_01 |      1 |      2 |     2   (0)|      1 |00:00:00.01 |       3 |      0 |
------------------------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter(ROWNUM=1)
   4 - filter("DSF"."PLACFILE" IS NOT NULL)
   6 - access("B"."CAPPNO"='GYA195000003991')
   7 - access("CCL"."CLAIMNO"="B"."CLAIMNO" AND "DSF"."POLICYNO"="CCL"."POLICYNO")
  13 - access("B"."CAPPNO"='GYA195000003991')
  14 - access("CCL"."CLAIMNO"="B"."CLAIMNO")
  15 - access("DSF"."POLICYNO"="CCL"."POLICYNO")

--优化后，其执行计划（with as 改写），其执行时间为 00:00:00.01 s，逻辑读为 3 ；

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last +cost')); 


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  64u34u5xb8pdc, child number 0


Plan hash value: 859862363


----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                       | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY                  |                            |      1 |        |            |      0 |00:00:00.01 |       3 |      0 |      0 |       |       |          |
|*  2 |   VIEW                          |                            |      1 |   7036K| 23384   (1)|      0 |00:00:00.01 |       3 |      0 |      0 |       |       |          |
|   3 |    TABLE ACCESS FULL            | SYS_TEMP_0FD9D6633_7481530 |      1 |   7036K| 23384   (1)|      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          |
|   4 |  TEMP TABLE TRANSFORMATION      |                            |      1 |        |            |      1 |00:00:00.01 |      25 |      1 |      1 |       |       |          |
|   5 |   LOAD AS SELECT                |                            |      1 |        |            |      1 |00:00:00.01 |      16 |      0 |      1 |   265K|   265K|  265K (0)|
|   6 |    NESTED LOOPS                 |                            |      1 |      4 |    12   (0)|      1 |00:00:00.01 |      11 |      0 |      0 |       |       |          |
|   7 |     NESTED LOOPS                |                            |      1 |      2 |     8   (0)|      1 |00:00:00.01 |       8 |      0 |      0 |       |       |          |
|   8 |      TABLE ACCESS BY INDEX ROWID|                            |      1 |      1 |     5   (0)|      1 |00:00:00.01 |       4 |      0 |      0 |       |       |          |
|*  9 |       INDEX RANGE SCAN          | IND_NREGCLM_1              |      1 |      1 |     3   (0)|      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          |
|  10 |      SORT UNIQUE                |                            |      1 |      2 |     3   (0)|      1 |00:00:00.01 |       4 |      0 |      0 |  2048 |  2048 | 2048  (0)|
|* 11 |       INDEX RANGE SCAN          | PK_NCLMCCL                 |      1 |      2 |     3   (0)|      1 |00:00:00.01 |       4 |      0 |      0 |       |       |          |
|* 12 |     INDEX RANGE SCAN            | IND_DSFTOBANK_01           |      1 |      2 |     2   (0)|      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          |
|  13 |   VIEW                          |                            |      1 |   7036K| 23384   (1)|      1 |00:00:00.01 |       6 |      1 |      0 |       |       |          |
|  14 |    TABLE ACCESS FULL            | SYS_TEMP_0FD9D6633_7481530 |      1 |   7036K| 23384   (1)|      1 |00:00:00.01 |       6 |      1 |      0 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter(ROWNUM=1)
   2 - filter("A"."PLACFILE" IS NOT NULL)
   9 - access("B"."CAPPNO"='GYA195000003991')
  11 - access("CCL"."CLAIMNO"="B"."CLAIMNO")
  12 - access("DSF"."POLICYNO"="CCL"."POLICYNO")


36 rows selected.

3、笛卡尔积

SQL语句1h0fhdwy2xg0y优化分析：

SQL语句中共有8张表进行关联查询，但只有6个关联条件，其执行计划走笛卡尔积（MERGE JOIN CARTESIAN），

单次执行时间达76795.24 s,单次逻辑读高达2926M (2926160262)

SQL语句长时间跑不出来，消耗非常大。

详情如下：

SQL_ID        PLAN_HASH_VALUE Date time              No.execs         LIO/exec CPUTIM/exec  ETIME/exec       PIO/exec     ROWs/exec
------------- --------------- -------------------- ---------- ---------------- ----------- ----------- -------------- -------------
1h0fhdwy2xg0y      1152747689 01/10/19_1500_1600           14     103750614.00      865.25     1050.35       36271.50           .00
1h0fhdwy2xg0y      1152747689 01/10/19_1600_1700            0    2157755512.00    40522.67    50255.00     3208536.00           .00
1h0fhdwy2xg0y      1152747689 01/10/19_1700_1800            0    2574875138.00    41755.44    50469.65     1705923.00           .00
1h0fhdwy2xg0y      1152747689 01/10/19_1800_1900            0     651729994.00    10387.50    11885.25      480478.00      12362.00
1h0fhdwy2xg0y      1152747689 01/11/19_1400_1500            4      21049052.50       38.72       38.82         857.00           .00
1h0fhdwy2xg0y      1152747689 01/11/19_1500_1600            1     638678734.00    17724.36    17729.88      779068.00           .00
1h0fhdwy2xg0y      1152747689 01/11/19_1600_1700            0     808540316.00    17927.53    18029.36      368200.00           .00
1h0fhdwy2xg0y      1152747689 01/11/19_1700_1800            0              .00    18020.64    18018.90      287601.00           .00
1h0fhdwy2xg0y      1152747689 01/11/19_1800_1900            0     868320441.00    17999.89    17994.18      307894.00           .00
1h0fhdwy2xg0y      1152747689 01/11/19_1900_2000            0              .00    18025.91    18029.40      307165.00           .00
1h0fhdwy2xg0y      1152747689 01/11/19_2000_2100            0    1203432234.00     2259.94     2260.24       55749.00        745.00


1h0fhdwy2xg0y      1152747689 01/17/19_1700_1800            0     376932848.00    19580.10    76795.24      283736.00           .00

Plan hash value: 1152747689


-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |              |       |       |       |  1683M(100)|          |
|   1 |  TABLE ACCESS FULL                             | T_ITEM       |     1 |    40 |       |     8   (0)| 00:00:01 |
|   2 |  TABLE ACCESS FULL                             | T_ITEM       |     1 |    40 |       |     8   (0)| 00:00:01 |
|   3 |  TABLE ACCESS FULL                             | T_ITEM       |     1 |    40 |       |     8   (0)| 00:00:01 |
|   4 |  SORT ORDER BY                                 |              |     1 |  1077 |  1645G|  1683M  (1)|999:59:59 |
|   5 |   FILTER                                       |              |       |       |       |            |          |
|   6 |    FILTER                                      |              |       |       |       |            |          |
|   7 |     HASH JOIN                                  |              |   754M|   757G|  6328K|   163K  (9)| 00:32:42 |
|   8 |      MERGE JOIN CARTESIAN                      |              |  7460 |  6236K|       |  5091   (2)| 00:01:02 |
|   9 |       HASH JOIN SEMI                           |              |   307 |   244K|       |  3069   (2)| 00:00:37 |
|  10 |        HASH JOIN                               |              |   309 |   242K|       |  2789   (2)| 00:00:34 |
|  11 |         HASH JOIN                              |              |   310 |   234K|       |  2509   (1)| 00:00:31 |
|  12 |          TABLE ACCESS FULL                     | T_ITEM       |    24 |   960 |       |     8   (0)| 00:00:01 |
|  13 |          HASH JOIN                             |              |   319 |   228K|       |  2501   (1)| 00:00:31 |
|  14 |           TABLE ACCESS FULL                    | T_ITEM       |    24 |   960 |       |     8   (0)| 00:00:01 |
|  15 |           TABLE ACCESS FULL                    | S_ISCUST     |   329 |   222K|       |  2492   (1)| 00:00:30 |
|  16 |         TABLE ACCESS FULL                      | P_ORG        |  8786 |   265K|       |   279   (2)| 00:00:04 |
|  17 |        VIEW                                    |              | 43931 |   514K|       |   279   (2)| 00:00:04 |
|  18 |         CONNECT BY NO FILTERING WITH START-WITH|              |       |       |       |            |          |
|  19 |          TABLE ACCESS FULL                     | P_ORG        | 43931 |  1072K|       |   279   (2)| 00:00:04 |
|  20 |       BUFFER SORT                              |              |    24 |   960 |       |  5091   (2)| 00:01:02 |
|  21 |        TABLE ACCESS FULL                       | T_ITEM       |    24 |   960 |       |     7   (0)| 00:00:01 |
|  22 |      TABLE ACCESS FULL                         | S_ISMX       |  2631K|   554M|       |   116K  (2)| 00:23:19 |
|  23 |    FILTER                                      |              |       |       |       |            |          |
|  24 |     CONNECT BY WITH FILTERING                  |              |       |       |       |            |          |
|  25 |      TABLE ACCESS BY INDEX ROWID               | S_ISCUST     |     1 |    83 |       |     2   (0)| 00:00:01 |
|  26 |       INDEX RANGE SCAN                         | S_ISCUST_001 |     1 |       |       |     1   (0)| 00:00:01 |
|  27 |      NESTED LOOPS                              |              |       |       |       |            |          |
|  28 |       CONNECT BY PUMP                          |              |       |       |       |            |          |
|  29 |       TABLE ACCESS BY INDEX ROWID              | S_ISCUST     |     1 |    29 |       |     2   (0)| 00:00:01 |
|  30 |        INDEX RANGE SCAN                        | S_ISCUST_001 |     1 |       |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------

推荐文章 · 点击阅读

低效SQL语句经典案例解析（上）

数据库

文章转载自数据与人，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

低效SQL语句经典案例解析（下）

评论