ORACLE数据库SQL优化
Apr282016
今天一早，开发人员就过来找我看一条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表修改为分区表，但做不做什么时候做还是未知呀。
【上一篇】ORACLE数据库RMAN-06023 no backup or copy of datafile 4 found to restore错误
【下一篇】ORACLE数据库内存设置过大，导致服务器异常缓慢
【下一篇】ORACLE数据库内存设置过大，导致服务器异常缓慢
ORACLE数据库SQL优化：目前有1 条留言
发表评论
最后一句是重点啊
2016-06-03 09:34