当前位置: 首页 > Oracle, oracle 10g, oracle 11g > 正文

ORACLE数据库SQL优化

今天一早,开发人员就过来找我看一条SQL,业务人员说这条SQL很慢,要30秒才能跑完,SQL如下:

SELECT SUM(B.RL_SLS_AMT) as "sumPrice", A.ORD_ID as "orderId" 
FROM CHGSHS.ORD_ORD_ADT_D A, CHGSHS.ORD_ORD_DTL_D B, CHGSHS.ORD_ORD_DTL_D_EXT D 
WHERE A.ORD_ADT_PSS_YN = 'N' 
AND A.ORD_ID = B.ORD_ID 
AND A.ORD_SEQ = B.ORD_SEQ 
AND A.ORD_ID = D.ORD_ID 
AND A.ORD_SEQ = D.ORD_SEQ 
AND B.RJT_RTN_YN = 'N' 
AND B.ORD_SRN_PSS_YN = 'Y' 
AND B.ORD_PTR_CD <> '210' 
AND (B.ORD_STS_CD = '40' OR B.ORD_STS_CD = '92') 
AND B.ORD_ACP_DTM BETWEEN TO_DATE('2016-04-01 00:00:00', 'YYYY - MM - DD HH24 :MI :SS') AND TO_DATE('2016-04-28 09:05:54', 'YYYY - MM - DD HH24 :MI :SS') 
AND A.INST_DTM BETWEEN TO_DATE('2016-04-01 00:00:00', 'YYYY - MM - DD HH24 :MI :SS') AND TO_DATE('2016-04-28 09:05:54', 'YYYY - MM - DD HH24 :MI :SS') 
AND D.INST_DTM BETWEEN TO_DATE('2016-04-01 00:00:00', 'YYYY - MM - DD HH24 :MI :SS') AND TO_DATE('2016-04-28 09:05:54', 'YYYY - MM - DD HH24 :MI :SS') 
AND A.ORD_ADT_ASS_YN = 'N' 
GROUP BY A.ORD_ID 
order by A.ORD_ID ASC 
;

看下这个SQL的执行计划和统计信息。

SQL> set timing on 
SQL> set autotrace on 
SQL> SELECT SUM(B.RL_SLS_AMT) as "sumPrice", A.ORD_ID as "orderId" 
  2  FROM CHGSHS.ORD_ORD_ADT_D A, CHGSHS.ORD_ORD_DTL_D B, CHGSHS.ORD_ORD_DTL_D_EXT D 
  3  WHERE A.ORD_ADT_PSS_YN = 'N' 
  4  AND A.ORD_ID = B.ORD_ID 
  5  AND A.ORD_SEQ = B.ORD_SEQ 
  6  AND A.ORD_ID = D.ORD_ID 
  7  AND A.ORD_SEQ = D.ORD_SEQ 
  8  AND B.RJT_RTN_YN = 'N' 
  9  AND B.ORD_SRN_PSS_YN = 'Y' 
 10  AND B.ORD_PTR_CD <> '210' 
 11  AND (B.ORD_STS_CD = '40' OR B.ORD_STS_CD = '92') 
 12  AND B.ORD_ACP_DTM BETWEEN TO_DATE('2016-04-01 00:00:00', 'YYYY - MM - DD HH24 :MI :SS') AND TO_DATE('2016-04-28 09:05:54', 'YYYY - MM - DD HH24 :MI :SS') 
 13  AND A.INST_DTM BETWEEN TO_DATE('2016-04-01 00:00:00', 'YYYY - MM - DD HH24 :MI :SS') AND TO_DATE('2016-04-28 09:05:54', 'YYYY - MM - DD HH24 :MI :SS') 
 14  AND D.INST_DTM BETWEEN TO_DATE('2016-04-01 00:00:00', 'YYYY - MM - DD HH24 :MI :SS') AND TO_DATE('2016-04-28 09:05:54', 'YYYY - MM - DD HH24 :MI :SS') 
 15  AND A.ORD_ADT_ASS_YN = 'N' 
 16  GROUP BY A.ORD_ID 
 17  order by A.ORD_ID ASC 
 18  ;

  sumPrice orderId
---------- ----------------------------------------
       288 160428754218
       240 160428754220
       899 160428754226
      3980 160428754246
       593 160428754275
       298 160428754301
      1350 160428754314
       389 160428754339
       899 160428754343
       269 160428754351
       288 160428754352
      1999 160428754367
       298 160428754373
       388 160428754381

14 rows selected.

Elapsed: 00:00:04.69

Execution Plan
----------------------------------------------------------
Plan hash value: 59381993

----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                        |    48 |  4416 |  1377   (1)| 00:00:17 |       |       |
|   1 |  SORT GROUP BY                          |                        |    48 |  4416 |  1377   (1)| 00:00:17 |       |       |
|*  2 |   FILTER                                |                        |       |       |            |          |       |       |
|   3 |    NESTED LOOPS                         |                        |    48 |  4416 |  1376   (1)| 00:00:17 |       |       |
|   4 |     NESTED LOOPS                        |                        |    48 |  4416 |  1376   (1)| 00:00:17 |       |       |
|   5 |      NESTED LOOPS                       |                        |    48 |  3264 |  1232   (1)| 00:00:15 |       |       |
|*  6 |       TABLE ACCESS BY INDEX ROWID       | ORD_ORD_ADT_D          |    62 |  1798 |  1046   (1)| 00:00:13 |       |       |
|   7 |        BITMAP CONVERSION TO ROWIDS      |                        |       |       |            |          |       |       |
|   8 |         BITMAP AND                      |                        |       |       |            |          |       |       |
|   9 |          BITMAP CONVERSION FROM ROWIDS  |                        |       |       |            |          |       |       |
|* 10 |           INDEX RANGE SCAN              | IX_ORD_ORD_ADT_D_02    | 52259 |       |   217   (1)| 00:00:03 |       |       |
|  11 |          BITMAP CONVERSION FROM ROWIDS  |                        |       |       |            |          |       |       |
|* 12 |           INDEX RANGE SCAN              | IX_ORD_ORD_ADT_D_03    | 52259 |       |   640   (1)| 00:00:08 |       |       |
|* 13 |       TABLE ACCESS BY GLOBAL INDEX ROWID| ORD_ORD_DTL_D          |     1 |    39 |     3   (0)| 00:00:01 | ROWID | ROWID |
|* 14 |        INDEX UNIQUE SCAN                | PK_ORD_ORD_DTL_D_2     |     1 |       |     2   (0)| 00:00:01 |       |       |
|* 15 |      INDEX UNIQUE SCAN                  | PK_ORD_ORD_DTL_D_EXT_1 |     1 |       |     2   (0)| 00:00:01 |       |       |
|* 16 |     TABLE ACCESS BY GLOBAL INDEX ROWID  | ORD_ORD_DTL_D_EXT      |     1 |    24 |     3   (0)| 00:00:01 | ROWID | ROWID |
----------------------------------------------------------------------------------------------------------------------------------

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

   2 - filter(TO_DATE('2016-04-28 09:05:54','YYYY - MM - DD HH24 :MI :SS')>=TO_DATE('2016-04-01 00:00:00','YYYY - MM - DD
              HH24 :MI :SS'))
   6 - filter("A"."INST_DTM">=TO_DATE('2016-04-01 00:00:00','YYYY - MM - DD HH24 :MI :SS') AND
              "A"."INST_DTM"<=TO_DATE('2016-04-28 09:05:54','YYYY - MM - DD HH24 :MI :SS'))
  10 - access("A"."ORD_ADT_PSS_YN"='N')
  12 - access("A"."ORD_ADT_ASS_YN"='N')
  13 - filter(("B"."ORD_STS_CD"='40' OR "B"."ORD_STS_CD"='92') AND "B"."RJT_RTN_YN"='N' AND
              "B"."ORD_ACP_DTM">=TO_DATE('2016-04-01 00:00:00','YYYY - MM - DD HH24 :MI :SS') AND "B"."ORD_PTR_CD"<>'210' AND
              "B"."ORD_SRN_PSS_YN"='Y' AND "B"."ORD_ACP_DTM"<=TO_DATE('2016-04-28 09:05:54','YYYY - MM - DD HH24 :MI :SS'))
  14 - access("A"."ORD_ID"="B"."ORD_ID" AND "A"."ORD_SEQ"="B"."ORD_SEQ")
  15 - access("A"."ORD_ID"="D"."ORD_ID" AND "A"."ORD_SEQ"="D"."ORD_SEQ")
  16 - filter("D"."INST_DTM">=TO_DATE('2016-04-01 00:00:00','YYYY - MM - DD HH24 :MI :SS') AND
              "D"."INST_DTM"<=TO_DATE('2016-04-28 09:05:54','YYYY - MM - DD HH24 :MI :SS'))

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       6378  consistent gets
        750  physical reads
        680  redo size
        941  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         14  rows processed

从统计信息看,逻辑读和物理读都不是太高,对性能影响最大的部分就是ORD_ORD_ADT_D表上面,这里可以看到走了两个索引IX_ORD_ORD_ADT_D_02和IX_ORD_ORD_ADT_D_03,下面看下这两个索引是在哪个字段上面。

SQL> select table_name,index_name,column_name from dba_ind_columns where index_name in ('IX_ORD_ORD_ADT_D_02','IX_ORD_ORD_ADT_D_03');

TABLE_NAME                     INDEX_NAME                     COLUMN_NAME
------------------------------ ------------------------------ --------------
ORD_ORD_ADT_D                  IX_ORD_ORD_ADT_D_02            ORD_ADT_PSS_YN
ORD_ORD_ADT_D                  IX_ORD_ORD_ADT_D_03            ORD_ADT_ASS_YN

从这两个索引创建的字段和上面SQL可以猜出,这两个字段ORD_ADT_PSS_YN和ORD_ADT_ASS_YN存的数据可能就Y和N两个值,下面看下是否是这样,以下是整体的数据分布。

SQL> select ORD_ADT_PSS_YN,count(*) from chgshs.ORD_ORD_ADT_D group by ORD_ADT_PSS_YN;

O   COUNT(*)
- ----------
          42
Y    9362112
C     584259
Z      51752
N      51236

SQL> select ORD_ADT_ASS_YN,count(*) from chgshs.ORD_ORD_ADT_D group by ORD_ADT_ASS_YN;

O   COUNT(*)
- ----------
Y    9896024
N     153390

可见,大部分数据都是Y或者N,再看下查询的时间段内的数据分布。

SQL> SELECT A.ORD_ADT_PSS_YN,count(*)
  2  FROM CHGSHS.ORD_ORD_ADT_D A
  3  WHERE A.ORD_ADT_PSS_YN = 'N' 
  4  AND A.INST_DTM BETWEEN TO_DATE('2016-04-01 00:00:00', 'YYYY - MM - DD HH24 :MI :SS') AND TO_DATE('2016-04-28 09:05:54', 'YYYY - MM - DD HH24 :MI :SS') 
  5  AND A.ORD_ADT_ASS_YN = 'N' 
  6  GROUP BY A.ORD_ADT_PSS_YN 
  7  ;

O   COUNT(*)
- ----------
N        123

SQL> SELECT A.ORD_ADT_ASS_YN,count(*)
  2  FROM CHGSHS.ORD_ORD_ADT_D A
  3  WHERE A.ORD_ADT_PSS_YN = 'N' 
  4  AND A.INST_DTM BETWEEN TO_DATE('2016-04-01 00:00:00', 'YYYY - MM - DD HH24 :MI :SS') AND TO_DATE('2016-04-28 09:05:54', 'YYYY - MM - DD HH24 :MI :SS') 
  5  AND A.ORD_ADT_ASS_YN = 'N' 
  6  GROUP BY A.ORD_ADT_ASS_YN 
  7  ;

O   COUNT(*)
- ----------
N        123

所有的数据都是N,可见这条SQL走这两个索引是有问题的,看下A表的索引情况。

SQL> select table_name,index_name,column_name from dba_ind_columns where table_name='ORD_ORD_ADT_D';

TABLE_NAME      INDEX_NAME                     COLUMN_NAME
--------------- ------------------------------ --------------------
ORD_ORD_ADT_D   IX_ORD_ORD_ADT_D_07            MDF_DTM
ORD_ORD_ADT_D   IX_ORD_ORD_ADT_D_08            NO_AUTO_ADT_CD
ORD_ORD_ADT_D   PK_ORD_ORD_ADT_D               ORD_ADT_ID
ORD_ORD_ADT_D   IX_ORD_ORD_ADT_D_01            ORD_ID
ORD_ORD_ADT_D   IX_ORD_ORD_ADT_D_01            ORD_SEQ
ORD_ORD_ADT_D   IX_ORD_ORD_ADT_D_02            ORD_ADT_PSS_YN
ORD_ORD_ADT_D   IX_ORD_ORD_ADT_D_03            ORD_ADT_ASS_YN
ORD_ORD_ADT_D   IX_ORD_ORD_ADT_D_04            ORD_ADT_HNG_ID
ORD_ORD_ADT_D   IX_ORD_ORD_ADT_D_04            ORD_ADT_PSS_YN
ORD_ORD_ADT_D   IX_ORD_ORD_ADT_D_05            INST_DTM
ORD_ORD_ADT_D   IX_ORD_ORD_ADT_D_06            ORD_ADT_HNG_ID
ORD_ORD_ADT_D   IX_ORD_ORD_ADT_D_06            ASS_DTM

12 rows selected.

下面尝试一下使用IX_ORD_ORD_ADT_D_01索引,来看看SQL的效率是否有所提升,为了防止刚才查询的数据已经cache到内存中,修改下查询的时间范围。

SQL> SELECT /*+INDEX(A,IX_ORD_ORD_ADT_D_01)*/SUM(B.RL_SLS_AMT) as "sumPrice", A.ORD_ID as "orderId" 
  2  FROM CHGSHS.ORD_ORD_ADT_D A, CHGSHS.ORD_ORD_DTL_D B, CHGSHS.ORD_ORD_DTL_D_EXT D 
  3  WHERE A.ORD_ADT_PSS_YN = 'N' 
  4  AND A.ORD_ID = B.ORD_ID 
  5  AND A.ORD_SEQ = B.ORD_SEQ 
  6  AND A.ORD_ID = D.ORD_ID 
  7  AND A.ORD_SEQ = D.ORD_SEQ 
  8  AND B.RJT_RTN_YN = 'N' 
  9  AND B.ORD_SRN_PSS_YN = 'Y' 
 10  AND B.ORD_PTR_CD <> '210' 
 11  AND (B.ORD_STS_CD = '40' OR B.ORD_STS_CD = '92') 
 12  AND B.ORD_ACP_DTM BETWEEN TO_DATE('2016-03-01 00:00:00', 'YYYY - MM - DD HH24 :MI :SS') AND TO_DATE('2016-03-28 09:05:54', 'YYYY - MM - DD HH24 :MI :SS') 
 13  AND A.INST_DTM BETWEEN TO_DATE('2016-03-01 00:00:00', 'YYYY - MM - DD HH24 :MI :SS') AND TO_DATE('2016-03-28 09:05:54', 'YYYY - MM - DD HH24 :MI :SS') 
 14  AND D.INST_DTM BETWEEN TO_DATE('2016-03-01 00:00:00', 'YYYY - MM - DD HH24 :MI :SS') AND TO_DATE('2016-03-28 09:05:54', 'YYYY - MM - DD HH24 :MI :SS') 
 15  AND A.ORD_ADT_ASS_YN = 'N' 
 16  GROUP BY A.ORD_ID 
 17  order by A.ORD_ID ASC ;

no rows selected

Elapsed: 00:00:00.68

Execution Plan
----------------------------------------------------------
Plan hash value: 4089042316

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                        |    53 |  4876 |  3401   (1)| 00:00:41 |       |       |
|   1 |  SORT GROUP BY                           |                        |    53 |  4876 |  3401   (1)| 00:00:41 |       |       |
|*  2 |   FILTER                                 |                        |       |       |            |          |       |       |
|   3 |    NESTED LOOPS                          |                        |    53 |  4876 |  3400   (1)| 00:00:41 |       |       |
|   4 |     NESTED LOOPS                         |                        |    53 |  4876 |  3400   (1)| 00:00:41 |       |       |
|   5 |      NESTED LOOPS                        |                        |    53 |  3604 |  3241   (1)| 00:00:39 |       |       |
|   6 |       PARTITION RANGE ITERATOR           |                        |    53 |  2067 |  3072   (1)| 00:00:37 |   KEY |   KEY |
|   7 |        INLIST ITERATOR                   |                        |       |       |            |          |       |       |
|*  8 |         TABLE ACCESS BY LOCAL INDEX ROWID| ORD_ORD_DTL_D          |    53 |  2067 |  3072   (1)| 00:00:37 |   KEY |   KEY |
|*  9 |          INDEX RANGE SCAN                | IX_ORD_ORD_DTL_D_04    |  5941 |       |  1983   (1)| 00:00:24 |   KEY |   KEY |
|* 10 |       TABLE ACCESS BY INDEX ROWID        | ORD_ORD_ADT_D          |     1 |    29 |     4   (0)| 00:00:01 |       |       |
|* 11 |        INDEX RANGE SCAN                  | IX_ORD_ORD_ADT_D_01    |     1 |       |     3   (0)| 00:00:01 |       |       |
|* 12 |      INDEX UNIQUE SCAN                   | PK_ORD_ORD_DTL_D_EXT_1 |     1 |       |     2   (0)| 00:00:01 |       |       |
|* 13 |     TABLE ACCESS BY GLOBAL INDEX ROWID   | ORD_ORD_DTL_D_EXT      |     1 |    24 |     3   (0)| 00:00:01 | ROWID | ROWID |
-----------------------------------------------------------------------------------------------------------------------------------

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

   2 - filter(TO_DATE('2016-03-28 09:05:54','YYYY - MM - DD HH24 :MI :SS')>=TO_DATE('2016-03-01 00:00:00','YYYY - MM - DD
              HH24 :MI :SS'))
   8 - filter("B"."RJT_RTN_YN"='N' AND "B"."ORD_ACP_DTM">=TO_DATE('2016-03-01 00:00:00','YYYY - MM - DD HH24 :MI :SS') AND
              "B"."ORD_PTR_CD"<>'210' AND "B"."ORD_ACP_DTM"<=TO_DATE('2016-03-28 09:05:54','YYYY - MM - DD HH24 :MI :SS') AND
              "B"."ORD_SRN_PSS_YN"='Y')
   9 - access("B"."ORD_STS_CD"='40' OR "B"."ORD_STS_CD"='92')
  10 - filter("A"."ORD_ADT_PSS_YN"='N' AND "A"."ORD_ADT_ASS_YN"='N' AND "A"."INST_DTM">=TO_DATE('2016-03-01
              00:00:00','YYYY - MM - DD HH24 :MI :SS') AND "A"."INST_DTM"<=TO_DATE('2016-03-28 09:05:54','YYYY - MM - DD HH24 :MI :SS'))
  11 - access("A"."ORD_ID"="B"."ORD_ID" AND "A"."ORD_SEQ"="B"."ORD_SEQ")
  12 - access("A"."ORD_ID"="D"."ORD_ID" AND "A"."ORD_SEQ"="D"."ORD_SEQ")
  13 - filter("D"."INST_DTM">=TO_DATE('2016-03-01 00:00:00','YYYY - MM - DD HH24 :MI :SS') AND
              "D"."INST_DTM"<=TO_DATE('2016-03-28 09:05:54','YYYY - MM - DD HH24 :MI :SS'))

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        332  consistent gets
        110  physical reads
          0  redo size
        405  bytes sent via SQL*Net to client
        509  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed

可见,强制使用IX_ORD_ORD_ADT_D_01索引,SQL运行的时间明显减少,消耗的资源也明显减少,下面测试下使用ORD_ORD_DTL_D表为驱动表,SQL是否被优化。

SQL> SELECT /*+ LEADING(B) */ SUM(B.RL_SLS_AMT) as "sumPrice", A.ORD_ID as "orderId" 
  2  FROM CHGSHS.ORD_ORD_ADT_D A, CHGSHS.ORD_ORD_DTL_D B, CHGSHS.ORD_ORD_DTL_D_EXT D 
  3  WHERE A.ORD_ADT_PSS_YN = 'N' 
  4  AND A.ORD_ID = B.ORD_ID 
  5  AND A.ORD_SEQ = B.ORD_SEQ 
  6  AND A.ORD_ID = D.ORD_ID 
  7  AND A.ORD_SEQ = D.ORD_SEQ 
  8  AND B.RJT_RTN_YN = 'N' 
  9  AND B.ORD_SRN_PSS_YN = 'Y' 
 10  AND B.ORD_PTR_CD <> '210' 
 11  AND (B.ORD_STS_CD = '40' OR B.ORD_STS_CD = '92') 
 12  AND B.ORD_ACP_DTM BETWEEN TO_DATE('2016-02-01 00:00:00', 'YYYY - MM - DD HH24 :MI :SS') AND TO_DATE('2016-02-28 09:05:54', 'YYYY - MM - DD HH24 :MI :SS') 
 13  AND A.INST_DTM BETWEEN TO_DATE('2016-02-01 00:00:00', 'YYYY - MM - DD HH24 :MI :SS') AND TO_DATE('2016-02-28 09:05:54', 'YYYY - MM - DD HH24 :MI :SS') 
 14  AND D.INST_DTM BETWEEN TO_DATE('2016-02-01 00:00:00', 'YYYY - MM - DD HH24 :MI :SS') AND TO_DATE('2016-02-28 09:05:54', 'YYYY - MM - DD HH24 :MI :SS') 
 15  AND A.ORD_ADT_ASS_YN = 'N' 
 16  GROUP BY A.ORD_ID 
 17  order by A.ORD_ID ASC ; 

no rows selected

Elapsed: 00:00:00.82

Execution Plan
----------------------------------------------------------
Plan hash value: 4089042316

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                        |     1 |    92 |  3401   (1)| 00:00:41 |       |       |
|   1 |  SORT GROUP BY                           |                        |     1 |    92 |  3401   (1)| 00:00:41 |       |       |
|*  2 |   FILTER                                 |                        |       |       |            |          |       |       |
|   3 |    NESTED LOOPS                          |                        |    53 |  4876 |  3400   (1)| 00:00:41 |       |       |
|   4 |     NESTED LOOPS                         |                        |    53 |  4876 |  3400   (1)| 00:00:41 |       |       |
|   5 |      NESTED LOOPS                        |                        |    53 |  3604 |  3241   (1)| 00:00:39 |       |       |
|   6 |       PARTITION RANGE ITERATOR           |                        |    53 |  2067 |  3072   (1)| 00:00:37 |   KEY |   KEY |
|   7 |        INLIST ITERATOR                   |                        |       |       |            |          |       |       |
|*  8 |         TABLE ACCESS BY LOCAL INDEX ROWID| ORD_ORD_DTL_D          |    53 |  2067 |  3072   (1)| 00:00:37 |   KEY |   KEY |
|*  9 |          INDEX RANGE SCAN                | IX_ORD_ORD_DTL_D_04    |  5941 |       |  1983   (1)| 00:00:24 |   KEY |   KEY |
|* 10 |       TABLE ACCESS BY INDEX ROWID        | ORD_ORD_ADT_D          |     1 |    29 |     4   (0)| 00:00:01 |       |       |
|* 11 |        INDEX RANGE SCAN                  | IX_ORD_ORD_ADT_D_01    |     1 |       |     3   (0)| 00:00:01 |       |       |
|* 12 |      INDEX UNIQUE SCAN                   | PK_ORD_ORD_DTL_D_EXT_1 |     1 |       |     2   (0)| 00:00:01 |       |       |
|* 13 |     TABLE ACCESS BY GLOBAL INDEX ROWID   | ORD_ORD_DTL_D_EXT      |     1 |    24 |     3   (0)| 00:00:01 | ROWID | ROWID |
-----------------------------------------------------------------------------------------------------------------------------------

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

   2 - filter(TO_DATE('2016-02-28 09:05:54','YYYY - MM - DD HH24 :MI :SS')>=TO_DATE('2016-02-01 00:00:00','YYYY - MM - DD
              HH24 :MI :SS'))
   8 - filter("B"."RJT_RTN_YN"='N' AND "B"."ORD_ACP_DTM">=TO_DATE('2016-02-01 00:00:00','YYYY - MM - DD HH24 :MI :SS') AND
              "B"."ORD_PTR_CD"<>'210' AND "B"."ORD_ACP_DTM"<=TO_DATE('2016-02-28 09:05:54','YYYY - MM - DD HH24 :MI :SS') AND
              "B"."ORD_SRN_PSS_YN"='Y')
   9 - access("B"."ORD_STS_CD"='40' OR "B"."ORD_STS_CD"='92')
  10 - filter("A"."ORD_ADT_PSS_YN"='N' AND "A"."ORD_ADT_ASS_YN"='N' AND "A"."INST_DTM">=TO_DATE('2016-02-01
              00:00:00','YYYY - MM - DD HH24 :MI :SS') AND "A"."INST_DTM"<=TO_DATE('2016-02-28 09:05:54','YYYY - MM - DD HH24 :MI :SS'))
  11 - access("A"."ORD_ID"="B"."ORD_ID" AND "A"."ORD_SEQ"="B"."ORD_SEQ")
  12 - access("A"."ORD_ID"="D"."ORD_ID" AND "A"."ORD_SEQ"="D"."ORD_SEQ")
  13 - filter("D"."INST_DTM">=TO_DATE('2016-02-01 00:00:00','YYYY - MM - DD HH24 :MI :SS') AND
              "D"."INST_DTM"<=TO_DATE('2016-02-28 09:05:54','YYYY - MM - DD HH24 :MI :SS'))

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        276  consistent gets
         94  physical reads
          0  redo size
        405  bytes sent via SQL*Net to client
        509  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed

可见,SQL的运行时间和消耗的资源都明显减少,下面看下这3张表的数据量。

SQL> select sum(bytes)/1024/1024/1024,segment_name from dba_segments where segment_name in ('ORD_ORD_DTL_D','ORD_ORD_DTL_D_EXT','ORD_ORD_ADT_D') GROUP BY SEGMENT_NAME;

SUM(BYTES)/1024/1024/1024 SEGMENT_NAME
------------------------- --------------------
               28.4238281 ORD_ORD_DTL_D
               1.06445313 ORD_ORD_ADT_D
                 15.40625 ORD_ORD_DTL_D_EXT

这三张表除A表外,B表和C表都是分区表,而且查询条件都使用的分区列,而A表并不是分区表,如果将A表设计为按照INST_DTM字段按月分区的分区表,这个问题也就迎刃而解,目前已经和开发人员建议将A表修改为分区表,但做不做什么时候做还是未知呀。

本文固定链接: http://www.dbdream.com.cn/2016/04/oracle%e6%95%b0%e6%8d%ae%e5%ba%93sql%e4%bc%98%e5%8c%96/ | 信春哥,系统稳,闭眼上线不回滚!

该日志由 dbdream 于2016年04月28日发表在 Oracle, oracle 10g, oracle 11g 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: ORACLE数据库SQL优化 | 信春哥,系统稳,闭眼上线不回滚!
关键字: , ,

ORACLE数据库SQL优化:目前有1 条留言

  1. 沙发
    shunzi:

    最后一句是重点啊

    2016-06-03 09:34 [回复]

发表评论

快捷键:Ctrl+Enter