分区表insert超慢
巡检发现本周比上周数据库IO吞吐量增加了近10倍，查询下来发现如下两个SQL有嫌疑，上面的为JOB，下面的为SQL，其中下面的SQL是上面的JOB里面的一部分，明确下来就是一个SQL引起
问题分析
1. 该SQL是insert语句，insert的表并不大，但是SQL语句执行一次要20多分钟，因该SQL交换数据块太多，执行计划在shard_pool中已经被清除出去了，无法直接查看该insert的执行计划
2. 把突破点放到了insert后面的select语句，调出具体select语句，执行一次大概20多分钟
3. 分析该select语句的执行计划，发现有PARTITION RANGE ALL和TABLE ACCESS FULL
4. TABLE ACCESS FULL用到字段是分区字段且有索引，但是并未走索引
原因定位
不走索引是因为使用了函数
解决方法
分区字段创建函数索引
再查看语句发现只去查前一天的数据，这样（29/30）*100%的数据都是在落在同一个分区内（跨月时数据会落在两个分区内），适用local模式索引
分区字段创建函数索引并采用local模式
create index IN_TRA_FUNC on ESB_MSG_LOG(REPLACE(SUBSTR(TRANSTIME, 1, 10), '-', '')) local
create index IN_ERR_TRA_FUNC on ESB_ERROR_LOG(REPLACE(SUBSTR(TRANSTIME, 1, 10), '-', '')) local
处理结果
SQL语句执行一次不到1秒，执行计划显示走了新建的索引了
d4dxa0xvsjvj4的原执行计划
SQL_ID d4dxa0xvsjvj4, child number 0
-------------------------------------
SELECT TO_CHAR(SYSDATE - 1, 'yyyymmdd') AS MSG_DAY,
T5.SERVID,
NVL(T1.SERVICETIMES, 0),
NVL(T3.RECVMSGSIZE, 0),
NVL(T2.ERRORTIMES, 0),
NVL(T4.MINUSTIME * 1000, 0),
T5.TRANHOUR
FROM (SELECT SERVICENAME, TRANHOUR, COUNT(*) SERVICETIMES
FROM (SELECT T.SERVICENAME,
T.REQUESTID,
SUBSTR(MAX(T.TRANSTIME), 12, 2) AS TRANHOUR
FROM ESB_MSG_LOG T
WHERE REPLACE(SUBSTR(T.TRANSTIME, 1, 10), '-', '') =
TO_CHAR(SYSDATE - 1, 'yyyymmdd')
GROUP BY T.SERVICENAME, T.REQUESTID) T
GROUP BY T.SERVICENAME, T.TRANHOUR
ORDER BY SERVICETIMES DESC) T1,
(SELECT SERVICENAME, TRANHOUR, COUNT(*) ERRORTIMES
FROM (SELECT T.SERVICENAME,
T.REQUESTID,SUBSTR(MAX(T.TRANSTIME), 12, 2) AS TRANHOUR
FROM ESB_ERROR_LOG T
WHERE REPLACE(SUBSTR(T.TRANSTIME, 1, 10), '-', '') =
TO_CHAR(SYSDATE - 1, 'yyyymmdd')
GROUP BY T.SERVICENAME, T.REQUESTID) T
GROUP BY T.SERVICENAME, TRANHOUR ORDER BY ERRORTIMES DESC) T2,
(SELECT SERVICENAME, TRANHOUR, SUM(MSGSIZE) RECVMSGSIZE
FROM (SELECT T.SERVICENAME,
T.REQUESTID,
SUM(T.MSGSIZE) AS MSGSIZE,
SUBSTR(MAX(T.TRANSTIME), 12, 2) AS TRANHOUR
FROM ESB_MSG_LOG T
WHERE REPLACE(SUBSTR(T.TRANSTIME, 1, 10), '-', '') =
TO_CHAR(SYSDATE - 1, 'yyyymmdd')
GROUP BY T.SERVICENAME, T.REQUESTID) T
GROUP BY T.SERVICENAME, TRANHOUR
ORDER BY RECVMSGSIZE DESC) T3,
(SELECT T.SERVICENAME,
TRANHOUR,
AVG(TO_NUMBER(SUBSTR(TO_CHAR(TO_TIMESTAMP((SELECT L.ENDTIME
FROM ESB_MSG_LOG L
WHERE L.REQUESTID =
T.REQUESTID
AND L.FLOWNAME =
'com.huaqin.esb.http.main_http'
AND L.MONITORPOINT = '20'
AND ROWNUM = 1),
'YYYY-MM-DD HH24:MI:SS.ff') -
TO_TIMESTAMP((SELECT L.ENDTIME
FROM ESB_MSG_LOG L
WHERE L.REQUESTID =
T.REQUESTID
AND L.FLOWNAME =
'com.huaqin.esb.http.main_http'
AND L.MONITORPOINT = '10'
AND ROWNUM = 1),
'YYYY-MM-DD HH24:MI:SS.ff'),
'YYYY-MM-DD HH24:MI:SS.ff'),
19,
11))) AS MINUSTIME
FROM (SELECT DISTINCT T.SERVICENAME,
T.REQUESTID,
T.TRANSTIME,
SUBSTR(T.TRANSTIME, 12, 2) AS TRANHOUR,
T.ENDTIME,
T.MONITORPOINT,
T.FLOWNAME
FROM ESB_MSG_LOG T
WHERE REPLACE(SUBSTR(T.TRANSTIME, 1, 10), '-', '') =
TO_CHAR(SYSDATE - 1, 'yyyymmdd')
AND T.ERRORCODE = '000000'
AND T.FLOWNAME = 'com.huaqin.esb.http.main_http'
GROUP BY T.SERVICENAME,
T.REQUESTID,
T.TRANSTIME,
T.ENDTIME,
T.MONITORPOINT,
T.FLOWNAME) T
GROUP BY T.SERVICENAME, TRANHOUR) T4,
(SELECT T1.SERVID, T1.SERVICENAME, T2.TRANHOUR
FROM SERVDEFINE T1,
(SELECT T1.SERVICENAME,
SUBSTR(T1.TRANSTIME, 12, 2) AS TRANHOUR
FROM ESB_MSG_LOG T1
WHERE REPLACE(SUBSTR(T1.TRANSTIME, 1, 10), '-', '') =
TO_CHAR(SYSDATE - 1, 'yyyymmdd')
GROUP BY T1.SERVICENAME, SUBSTR(T1.TRANSTIME, 12, 2)
UNION
SELECT T2.SERVICENAME,
SUBSTR(T2.TRANSTIME, 12, 2) AS TRANHOUR
FROM ESB_ERROR_LOG T2
WHERE REPLACE(SUBSTR(T2.TRANSTIME, 1, 10), '-', '') =
TO_CHAR(SYSDATE - 1, 'yyyymmdd')
GROUP BY T2.SERVICENAME, SUBSTR(T2.TRANSTIME, 12, 2)) T2
WHERE T1.SERVICENAME = T2.SERVICENAME
AND T2.SERVICENAME IS NOT NULL) T5
WHERE T5.SERVICENAME = T1.SERVICENAME(+)
AND T5.TRANHOUR = T1.TRANHOUR(+)
AND T5.SERVICENAME = T2.SERVICENAME(+)
AND T5.TRANHOUR = T2.TRANHOUR(+)
AND T5.SERVICENAME = T3.SERVICENAME(+)
AND T5.TRANHOUR = T3.TRANHOUR(+)
AND T5.SERVICENAME = T4.SERVICENAME(+)
AND T5.TRANHOUR = T4.TRANHOUR(+)
Plan hash value: 1642813352
-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 629K(100)| | | |
|* 1 | COUNT STOPKEY | | | | | | | | |
|* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID | ESB_MSG_LOG | 1 | 92 | | 6 (0)| 00:00:01 | ROWID | ROWID |
|* 3 | INDEX RANGE SCAN | INDEX_RE_SER_MON_TRA | 2 | | | 4 (0)| 00:00:01 | | |
|* 4 | COUNT STOPKEY | | | | | | | | |
|* 5 | TABLE ACCESS BY GLOBAL INDEX ROWID| ESB_MSG_LOG | 1 | 92 | | 6 (0)| 00:00:01 | ROWID | ROWID |
|* 6 | INDEX RANGE SCAN | INDEX_RE_SER_MON_TRA | 2 | | | 4 (0)| 00:00:01 | | |
|* 7 | HASH JOIN RIGHT OUTER | | 48468 | 9939K| | 629K (1)| 02:05:56 | | |
| 8 | VIEW | | 68 | 2788 | | 191K (1)| 00:38:17 | | |
| 9 | HASH GROUP BY | | 68 | 35496 | | 191K (1)| 00:38:17 | | |
| 10 | VIEW | | 25134 | 12M| | 191K (1)| 00:38:17 | | |
| 11 | HASH GROUP BY | | 25134 | 3559K| 4112K| 191K (1)| 00:38:17 | | |
| 12 | PARTITION RANGE ALL | | 25134 | 3559K| | 190K (1)| 00:38:07 | 1 | 42 |
|* 13 | TABLE ACCESS FULL | ESB_MSG_LOG | 25134 | 3559K| | 190K (1)| 00:38:07 | 1 | 42 |
|* 14 | HASH JOIN RIGHT OUTER | | 48468 | 7999K| | 438K (1)| 01:27:40 | | |
| 15 | VIEW | | 68 | 2788 | | 191K (1)| 00:38:19 | | |
| 16 | SORT ORDER BY | | 68 | 2788 | | 191K (1)| 00:38:19 | | |
| 17 | HASH GROUP BY | | 68 | 2788 | | 191K (1)| 00:38:19 | | |
| 18 | VIEW | | 47832 | 1915K| | 191K (1)| 00:38:19 | | |
| 19 | HASH GROUP BY | | 47832 | 3923K| 4512K| 191K (1)| 00:38:19 | | |
| 20 | PARTITION RANGE ALL | | 47832 | 3923K| | 190K (1)| 00:38:08 | 1 | 42 |
|* 21 | TABLE ACCESS FULL | ESB_MSG_LOG | 47832 | 3923K| | 190K (1)| 00:38:08 | 1 | 42 |
|* 22 | HASH JOIN RIGHT OUTER | | 48468 | 6058K| | 246K (1)| 00:49:21 | | |
| 23 | VIEW | | 68 | 2788 | | 191K (1)| 00:38:18 | | |
| 24 | SORT ORDER BY | | 68 | 544 | | 191K (1)| 00:38:18 | | |
| 25 | HASH GROUP BY | | 68 | 544 | | 191K (1)| 00:38:18 | | |
| 26 | VIEW | | 47832 | 373K| | 191K (1)| 00:38:18 | | |
| 27 | HASH GROUP BY | | 47832 | 3690K| 4360K| 191K (1)| 00:38:18 | | |
| 28 | PARTITION RANGE ALL | | 47832 | 3690K| | 190K (1)| 00:38:08 | 1 | 42 |
|* 29 | TABLE ACCESS FULL | ESB_MSG_LOG | 47832 | 3690K| | 190K (1)| 00:38:08 | 1 | 42 |
|* 30 | HASH JOIN RIGHT OUTER | | 48468 | 4117K| | 55272 (1)| 00:11:04 | | |
| 31 | VIEW | | 65 | 2535 | | 14770 (1)| 00:02:58 | | |
| 32 | SORT ORDER BY | | 65 | 520 | | 14770 (1)| 00:02:58 | | |
| 33 | HASH GROUP BY | | 65 | 520 | | 14770 (1)| 00:02:58 | | |
| 34 | VIEW | | 636 | 5088 | | 14768 (1)| 00:02:58 | | |
| 35 | HASH GROUP BY | | 636 | 45792 | | 14768 (1)| 00:02:58 | | |
| 36 | PARTITION RANGE ALL | | 636 | 45792 | | 14767 (1)| 00:02:58 | 1 | 42 |
|* 37 | TABLE ACCESS FULL | ESB_ERROR_LOG | 636 | 45792 | | 14767 (1)| 00:02:58 | 1 | 42 |
| 38 | VIEW | | 48468 | 2271K| | 40502 (1)| 00:08:07 | | |
|* 39 | HASH JOIN | | 48468 | 25M| | 40502 (1)| 00:08:07 | | |
| 40 | VIEW | index$_join$_016 | 198 | 6534 | | 2 (0)| 00:00:01 | | |
|* 41 | HASH JOIN | | | | | | | | |
| 42 | INDEX FAST FULL SCAN | INDEX_SERVICENAME | 198 | 6534 | | 1 (0)| 00:00:01 | | |
| 43 | INDEX FAST FULL SCAN | SYS_C0060193 | 198 | 6534 | | 1 (0)| 00:00:01 | | |
| 44 | VIEW | | 48468 | 24M| | 40500 (1)| 00:08:06 | | |
| 45 | SORT UNIQUE | | 48468 | 4352K| 2648K| 40500 (1)| 00:08:06 | | |
| 46 | UNION-ALL | | | | | | | | |
| 47 | HASH GROUP BY | | 47832 | 2148K| 2648K| 25730 (1)| 00:05:09 | | |
|* 48 | INDEX FAST FULL SCAN | INDEX_RE_SER_MON_TRA | 47832 | 2148K| | 24617 (1)| 00:04:56 | | |
| 49 | HASH GROUP BY | | 636 | 27984 | | 14769 (1)| 00:02:58 | | |
| 50 | PARTITION RANGE ALL | | 636 | 27984 | | 14767 (1)| 00:02:58 | 1 | 42 |
|* 51 | TABLE ACCESS FULL | ESB_ERROR_LOG | 636 | 27984 | | 14767 (1)| 00:02:58 | 1 | 42 |
-------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
2 - filter("L"."FLOWNAME"='com.huaqin.esb.http.main_http')
3 - access("L"."REQUESTID"=:B1 AND "L"."MONITORPOINT"='20')
filter("L"."MONITORPOINT"='20')
4 - filter(ROWNUM=1)
5 - filter("L"."FLOWNAME"='com.huaqin.esb.http.main_http')
6 - access("L"."REQUESTID"=:B1 AND "L"."MONITORPOINT"='10')
filter("L"."MONITORPOINT"='10')
7 - access("T5"."SERVICENAME"="T4"."SERVICENAME" AND "T5"."TRANHOUR"="T4"."TRANHOUR")
13 - filter(("T"."FLOWNAME"='com.huaqin.esb.http.main_http' AND
REPLACE(SUBSTR("T"."TRANSTIME",1,10),'-','')=TO_CHAR(SYSDATE@!-1,'yyyymmdd') AND "T"."ERRORCODE"='000000'))
14 - access("T5"."SERVICENAME"="T3"."SERVICENAME" AND "T5"."TRANHOUR"="T3"."TRANHOUR")
21 - filter(REPLACE(SUBSTR("T"."TRANSTIME",1,10),'-','')=TO_CHAR(SYSDATE@!-1,'yyyymmdd'))
22 - access("T5"."SERVICENAME"="T1"."SERVICENAME" AND "T5"."TRANHOUR"="T1"."TRANHOUR")
29 - filter(REPLACE(SUBSTR("T"."TRANSTIME",1,10),'-','')=TO_CHAR(SYSDATE@!-1,'yyyymmdd'))
30 - access("T5"."SERVICENAME"="T2"."SERVICENAME" AND "T5"."TRANHOUR"="T2"."TRANHOUR")
37 - filter(REPLACE(SUBSTR("T"."TRANSTIME",1,10),'-','')=TO_CHAR(SYSDATE@!-1,'yyyymmdd'))
39 - access("T1"."SERVICENAME"="T2"."SERVICENAME")
41 - access(ROWID=ROWID)
48 - filter((REPLACE(SUBSTR("T1"."TRANSTIME",1,10),'-','')=TO_CHAR(SYSDATE@!-1,'yyyymmdd') AND "T1"."SERVICENAME" IS NOT
NULL))
51 - filter((REPLACE(SUBSTR("T2"."TRANSTIME",1,10),'-','')=TO_CHAR(SYSDATE@!-1,'yyyymmdd') AND "T2"."SERVICENAME" IS NOT
NULL))
d4dxa0xvsjvj4的增加索引后的执行计划
SQL_ID av5kt4vb6ugvp, child number 0
-------------------------------------
SELECT TO_CHAR(SYSDATE - 1, 'yyyymmdd') AS MSG_DAY,
T5.SERVID,
NVL(T1.SERVICETIMES, 0),
NVL(T3.RECVMSGSIZE, 0),
NVL(T2.ERRORTIMES, 0),
NVL(T4.MINUSTIME * 1000, 0),
T5.TRANHOUR
FROM (SELECT SERVICENAME, TRANHOUR, COUNT(*) SERVICETIMES
FROM (SELECT T.SERVICENAME,
T.REQUESTID,
SUBSTR(MAX(T.TRANSTIME), 12, 2) AS TRANHOUR
FROM ESB_MSG_LOG T
WHERE REPLACE(SUBSTR(T.TRANSTIME, 1, 10), '-', '') =
TO_CHAR(SYSDATE - 1, 'yyyymmdd')
GROUP BY T.SERVICENAME, T.REQUESTID) T
GROUP BY T.SERVICENAME, T.TRANHOUR
ORDER BY SERVICETIMES DESC) T1,
(SELECT SERVICENAME, TRANHOUR, COUNT(*) ERRORTIMES
FROM (SELECT T.SERVICENAME,
T.REQUESTID,SUBSTR(MAX(T.TRANSTIME), 12, 2) AS TRANHOUR
FROM ESB_ERROR_LOG T
WHERE REPLACE(SUBSTR(T.TRANSTIME, 1, 10), '-', '') =
TO_CHAR(SYSDATE - 1, 'yyyymmdd')
GROUP BY T.SERVICENAME, T.REQUESTID) T
GROUP BY T.SERVICENAME, TRANHOUR ORDER BY ERRORTIMES DESC) T2,
(SELECT SERVICENAME, TRANHOUR, SUM(MSGSIZE) RECVMSGSIZE
FROM (SELECT T.SERVICENAME,
T.REQUESTID,
SUM(T.MSGSIZE) AS MSGSIZE,
SUBSTR(MAX(T.TRANSTIME), 12, 2) AS TRANHOUR
FROM ESB_MSG_LOG T
WHERE REPLACE(SUBSTR(T.TRANSTIME, 1, 10), '-', '') =
TO_CHAR(SYSDATE - 1, 'yyyymmdd')
GROUP BY T.SERVICENAME, T.REQUESTID) T
GROUP BY T.SERVICENAME, TRANHOUR
ORDER BY RECVMSGSIZE DESC) T3,
(SELECT T.SERVICENAME,
TRANHOUR,
AVG(TO_NUMBER(SUBSTR(TO_CHAR(TO_TIMESTAMP((SELECT L.ENDTIME
FROM ESB_MSG_LOG L
WHERE L.REQUESTID =
T.REQUESTID
AND L.FLOWNAME =
'com.huaqin.esb.http.main_http'
AND L.MONITORPOINT = '20'
AND ROWNUM = 1),
'YYYY-MM-DD HH24:MI:SS.ff') -
TO_TIMESTAMP((SELECT L.ENDTIME
FROM ESB_MSG_LOG L
WHERE L.REQUESTID =
T.REQUESTID
AND L.FLOWNAME =
'com.huaqin.esb.http.main_http'
AND L.MONITORPOINT = '10'
AND ROWNUM = 1),
'YYYY-MM-DD HH24:MI:SS.ff'),
'YYYY-MM-DD HH24:MI:SS.ff'),
19,
11))) AS MINUSTIME
FROM (SELECT DISTINCT T.SERVICENAME,
T.REQUESTID,
T.TRANSTIME,
SUBSTR(T.TRANSTIME, 12, 2) AS TRANHOUR,
T.ENDTIME,
T.MONITORPOINT,
T.FLOWNAME
FROM ESB_MSG_LOG T
WHERE REPLACE(SUBSTR(T.TRANSTIME, 1, 10), '-', '') =
TO_CHAR(SYSDATE - 1, 'yyyymmdd')
AND T.ERRORCODE = '000000'
AND T.FLOWNAME = 'com.huaqin.esb.http.main_http'
GROUP BY T.SERVICENAME,
T.REQUESTID,
T.TRANSTIME,
T.ENDTIME,
T.MONITORPOINT,
T.FLOWNAME) T
GROUP BY T.SERVICENAME, TRANHOUR) T4,
(SELECT T1.SERVID, T1.SERVICENAME, T2.TRANHOUR
FROM SERVDEFINE T1,
(SELECT T1.SERVICENAME,
SUBSTR(T1.TRANSTIME, 12, 2) AS TRANHOUR
FROM ESB_MSG_LOG T1
WHERE REPLACE(SUBSTR(T1.TRANSTIME, 1, 10), '-', '') =
TO_CHAR(SYSDATE - 1, 'yyyymmdd')
GROUP BY T1.SERVICENAME, SUBSTR(T1.TRANSTIME, 12, 2)
UNION
SELECT T2.SERVICENAME,
SUBSTR(T2.TRANSTIME, 12, 2) AS TRANHOUR
FROM ESB_ERROR_LOG T2
WHERE REPLACE(SUBSTR(T2.TRANSTIME, 1, 10), '-', '') =
TO_CHAR(SYSDATE - 1, 'yyyymmdd')
GROUP BY T2.SERVICENAME, SUBSTR(T2.TRANSTIME, 12, 2)) T2
WHERE T1.SERVICENAME = T2.SERVICENAME
AND T2.SERVICENAME IS NOT NULL) T5
WHERE T5.SERVICENAME = T1.SERVICENAME(+)
AND T5.TRANHOUR = T1.TRANHOUR(+)
AND T5.SERVICENAME = T2.SERVICENAME(+)
AND T5.TRANHOUR = T2.TRANHOUR(+)
AND T5.SERVICENAME = T3.SERVICENAME(+)
AND T5.TRANHOUR = T3.TRANHOUR(+)
AND T5.SERVICENAME = T4.SERVICENAME(+)
AND T5.TRANHOUR = T4.TRANHOUR(+)
Plan hash value: 4096774298
---------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 17114 (100)| | | |
|* 1 | COUNT STOPKEY | | | | | | | | |
|* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID | ESB_MSG_LOG | 1 | 92 | | 6 (0)| 00:00:01 | ROWID | ROWID |
|* 3 | INDEX RANGE SCAN | INDEX_RE_SER_MON_TRA | 2 | | | 4 (0)| 00:00:01 | | |
|* 4 | COUNT STOPKEY | | | | | | | | |
|* 5 | TABLE ACCESS BY GLOBAL INDEX ROWID | ESB_MSG_LOG | 1 | 92 | | 6 (0)| 00:00:01 | ROWID | ROWID |
|* 6 | INDEX RANGE SCAN | INDEX_RE_SER_MON_TRA | 2 | | | 4 (0)| 00:00:01 | | |
|* 7 | HASH JOIN RIGHT OUTER | | 53414 | 10M| | 17114 (1)| 00:03:26 | | |
| 8 | VIEW | | 71 | 2911 | | 3929 (1)| 00:00:48 | | |
| 9 | HASH GROUP BY | | 71 | 37062 | | 3929 (1)| 00:00:48 | | |
| 10 | VIEW | | 27961 | 13M| | 3927 (1)| 00:00:48 | | |
| 11 | HASH GROUP BY | | 27961 | 4560K| 5216K| 3927 (1)| 00:00:48 | | |
| 12 | PARTITION RANGE ALL | | 27961 | 4560K| | 2898 (1)| 00:00:35 | 1 | 42 |
|* 13 | TABLE ACCESS BY LOCAL INDEX ROWID | ESB_MSG_LOG | 27961 | 4560K| | 2898 (1)| 00:00:35 | 1 | 42 |
|* 14 | INDEX RANGE SCAN | IN_TRA_FUNC | 21328 | | | 148 (0)| 00:00:02 | 1 | 42 |
|* 15 | HASH JOIN RIGHT OUTER | | 53414 | 8815K| | 13185 (1)| 00:02:39 | | |
| 16 | VIEW | | 71 | 2911 | | 4171 (1)| 00:00:51 | | |
| 17 | SORT ORDER BY | | 71 | 2911 | | 4171 (1)| 00:00:51 | | |
| 18 | HASH GROUP BY | | 71 | 2911 | | 4171 (1)| 00:00:51 | | |
| 19 | VIEW | | 52714 | 2110K| | 4167 (1)| 00:00:51 | | |
| 20 | HASH GROUP BY | | 52714 | 5456K| 6216K| 4167 (1)| 00:00:51 | | |
| 21 | PARTITION RANGE ALL | | 52714 | 5456K| | 2898 (1)| 00:00:35 | 1 | 42 |
| 22 | TABLE ACCESS BY LOCAL INDEX ROWID | ESB_MSG_LOG | 52714 | 5456K| | 2898 (1)| 00:00:35 | 1 | 42 |
|* 23 | INDEX RANGE SCAN | IN_TRA_FUNC | 21328 | | | 148 (0)| 00:00:02 | 1 | 42 |
|* 24 | HASH JOIN RIGHT OUTER | | 53414 | 6676K| | 9014 (1)| 00:01:49 | | |
| 25 | VIEW | | 71 | 2911 | | 4121 (1)| 00:00:50 | | |
| 26 | SORT ORDER BY | | 71 | 568 | | 4121 (1)| 00:00:50 | | |
| 27 | HASH GROUP BY | | 71 | 568 | | 4121 (1)| 00:00:50 | | |
| 28 | VIEW | | 52714 | 411K| | 4117 (1)| 00:00:50 | | |
| 29 | HASH GROUP BY | | 52714 | 5199K| 6040K| 4117 (1)| 00:00:50 | | |
| 30 | PARTITION RANGE ALL | | 52714 | 5199K| | 2898 (1)| 00:00:35 | 1 | 42 |
| 31 | TABLE ACCESS BY LOCAL INDEX ROWID | ESB_MSG_LOG | 52714 | 5199K| | 2898 (1)| 00:00:35 | 1 | 42 |
|* 32 | INDEX RANGE SCAN | IN_TRA_FUNC | 21328 | | | 148 (0)| 00:00:02 | 1 | 42 |
|* 33 | HASH JOIN RIGHT OUTER | | 53414 | 4538K| | 4893 (1)| 00:00:59 | | |
| 34 | VIEW | | 68 | 2652 | | 147 (3)| 00:00:02 | | |
| 35 | SORT ORDER BY | | 68 | 544 | | 147 (3)| 00:00:02 | | |
| 36 | HASH GROUP BY | | 68 | 544 | | 147 (3)| 00:00:02 | | |
| 37 | VIEW | | 700 | 5600 | | 145 (1)| 00:00:02 | | |
| 38 | HASH GROUP BY | | 700 | 65800 | | 145 (1)| 00:00:02 | | |
| 39 | PARTITION RANGE ALL | | 700 | 65800 | | 144 (0)| 00:00:02 | 1 | 42 |
| 40 | TABLE ACCESS BY LOCAL INDEX ROWID | ESB_ERROR_LOG | 700 | 65800 | | 144 (0)| 00:00:02 | 1 | 42 |
|* 41 | INDEX RANGE SCAN | IN_ERR_TRA_FUNC | 281 | | | 43 (0)| 00:00:01 | 1 | 42 |
| 42 | VIEW | | 53414 | 2503K| | 4745 (1)| 00:00:57 | | |
|* 43 | HASH JOIN | | 53414 | 28M| | 4745 (1)| 00:00:57 | | |
| 44 | VIEW | index$_join$_016 | 198 | 6534 | | 2 (0)| 00:00:01 | | |
|* 45 | HASH JOIN | | | | | | | | |
| 46 | INDEX FAST FULL SCAN | INDEX_SERVICENAME | 198 | 6534 | | 1 (0)| 00:00:01 | | |
| 47 | INDEX FAST FULL SCAN | SYS_C0060193 | 198 | 6534 | | 1 (0)| 00:00:01 | | |
| 48 | VIEW | | 53414 | 26M| | 4743 (1)| 00:00:57 | | |
| 49 | SORT UNIQUE | | 53414 | 7091K| 4144K| 4743 (1)| 00:00:57 | | |
| 50 | UNION-ALL | | | | | | | | |
| 51 | HASH GROUP BY | | 52714 | 3500K| 4144K| 4597 (1)| 00:00:56 | | |
| 52 | PARTITION RANGE ALL | | 52714 | 3500K| | 2898 (1)| 00:00:35 | 1 | 42 |
|* 53 | TABLE ACCESS BY LOCAL INDEX ROWID| ESB_MSG_LOG | 52714 | 3500K| | 2898 (1)| 00:00:35 | 1 | 42 |
|* 54 | INDEX RANGE SCAN | IN_TRA_FUNC | 21328 | | | 148 (0)| 00:00:02 | 1 | 42 |
| 55 | HASH GROUP BY | | 700 | 46200 | | 146 (2)| 00:00:02 | | |
| 56 | PARTITION RANGE ALL | | 700 | 46200 | | 144 (0)| 00:00:02 | 1 | 42 |
|* 57 | TABLE ACCESS BY LOCAL INDEX ROWID| ESB_ERROR_LOG | 700 | 46200 | | 144 (0)| 00:00:02 | 1 | 42 |
|* 58 | INDEX RANGE SCAN | IN_ERR_TRA_FUNC | 281 | | | 43 (0)| 00:00:01 | 1 | 42 |
---------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
2 - filter("L"."FLOWNAME"='com.huaqin.esb.http.main_http')
3 - access("L"."REQUESTID"=:B1 AND "L"."MONITORPOINT"='20')
filter("L"."MONITORPOINT"='20')
4 - filter(ROWNUM=1)
5 - filter("L"."FLOWNAME"='com.huaqin.esb.http.main_http')
6 - access("L"."REQUESTID"=:B1 AND "L"."MONITORPOINT"='10')
filter("L"."MONITORPOINT"='10')
7 - access("T5"."SERVICENAME"="T4"."SERVICENAME" AND "T5"."TRANHOUR"="T4"."TRANHOUR")
13 - filter(("T"."FLOWNAME"='com.huaqin.esb.http.main_http' AND "T"."ERRORCODE"='000000'))
14 - access("T"."SYS_NC00020$"=TO_CHAR(SYSDATE@!-1,'yyyymmdd'))
15 - access("T5"."SERVICENAME"="T3"."SERVICENAME" AND "T5"."TRANHOUR"="T3"."TRANHOUR")
23 - access("T"."SYS_NC00020$"=TO_CHAR(SYSDATE@!-1,'yyyymmdd'))
24 - access("T5"."SERVICENAME"="T1"."SERVICENAME" AND "T5"."TRANHOUR"="T1"."TRANHOUR")
32 - access("T"."SYS_NC00020$"=TO_CHAR(SYSDATE@!-1,'yyyymmdd'))
33 - access("T5"."SERVICENAME"="T2"."SERVICENAME" AND "T5"."TRANHOUR"="T2"."TRANHOUR")
41 - access("T"."SYS_NC00019$"=TO_CHAR(SYSDATE@!-1,'yyyymmdd'))
43 - access("T1"."SERVICENAME"="T2"."SERVICENAME")
45 - access(ROWID=ROWID)
53 - filter("T1"."SERVICENAME" IS NOT NULL)
54 - access("T1"."SYS_NC00020$"=TO_CHAR(SYSDATE@!-1,'yyyymmdd'))
57 - filter("T2"."SERVICENAME" IS NOT NULL)
58 - access("T2"."SYS_NC00019$"=TO_CHAR(SYSDATE@!-1,'yyyymmdd'))
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30126024/viewspace-2138708/ ，如需转载，请注明出处，否则将追究法律责任。
