重庆思庄Oracle、Redhat认证学习论坛
标题: 由 single-task message 等待引发的血案 [打印本页]
作者: 郑全 时间: 2019-1-23 11:36
标题: 由 single-task message 等待引发的血案
案件回放：
一天，接用户电话，系统非常慢，请求解决。
响应：
用户的请求，就是命令，马上登陆到对方机器上查看，发现系统大量cursor: pin S wait on X等待事件。
SQL> select event,count(*) from v$session group by event ;
EVENT COUNT(*)
---------------------------------------------------------------- ----------
cursor: pin S wait on X 230
SQL*Net message from client 580
db file parallel write 1
ASM background timer 1
wait for unread message on broadcast channel 2
ges remote message 1
gcs remote message 6
LNS ASYNC end of log 1
pmon timer 1
rdbms ipc message 35
gc cr request 6
EVENT COUNT(*)
---------------------------------------------------------------- ----------
smon timer 1
class slave wait 4
Streams AQ: qmn slave idle wait 1
Space Manager: slave idle wait 3
SQL*Net message to client 2
GCR sleep 1
log file parallel write 1
single-task message 3
VKTM Logical Idle Wait 1
Streams AQ: qmn coordinator idle wait 1
Streams AQ: waiting for time management or cleanup tasks 1
EVENT COUNT(*)
---------------------------------------------------------------- ----------
DIAG idle wait 2
PING 1
24 rows selected.
看看cursor: pin S wait on X，
“cursor: pin S wait on X”，这个等待事件主要是由硬解析引起的,解释如下：
“cursor: pin S wait on X” wait event is mostly related to mutex and hard parse.
- When a process hard parses the SQL statement, it should acquire exclusive
library cache pin for the corresponding LCO.
- This means that the process acquires the mutex in exclusive mode.
- Another process which also executes the same query needs to acquire the mutex
but it’s being blocked by preceding process. The wait event is “cursor: pin S wait on X”.
cursor: pin S，cursor: pin X，cursor: pin S wait on X这三个等待事件，实际上就是替代了cursor的library cache pin，pin S代表执行（share pin），pin X代表解析（exclusive pin），pin S wait on X代表执行正在等待解析操作。这里需要强调一下，它们只是替换了访问cursor的library cache pin，而对于访问procedure这种实体对象，依然是传统的library cache pin。
看一下，这个等待事件有哪些session在执行呢？
1* select sid,blocking_session,username,program,event,to_char(logon_time,'yyyy-mm-dd hh24:mi:ss') from v$session where event='cursor: pin S wait on X' order by 6
SID BLOCKING_SESSION USERNAME PROGRAM EVENT TO_CHAR(LOGON_TIME,
---------- ---------------- -------------------------- ------------------------------------------------ ---------------------------------------------------------------- -------------------
2619 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-21 08:47:23
1946 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-21 18:04:42
1969 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-22 08:54:50
753 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-23 08:22:49
343 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-23 18:05:59
1065 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 00:28:37
2211 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 07:34:22
2696 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 07:54:57
2070 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 07:56:27
2264 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 07:56:31
2570 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 07:57:50
xxx
SID BLOCKING_SESSION NAME xxxGRAM EVENT TO_CHAR(LOGON_TIME,
---------- ---------------- -------------------------- xxx--------------------------------------------- ---------------------------------------------------------------- -------------------
680 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:03:11
1543 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:11:11
2526 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:11:57
2308 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:16:09
536 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:19:04
2286 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:19:29
1904 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:21:31
2121 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:21:46
1927 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:23:20
1417 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:23:29
702 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:25:09
xxx
SID BLOCKING_SESSION NAME xxxGRAM EVENT TO_CHAR(LOGON_TIME,
---------- ---------------- -------------------------- xxx--------------------------------------------- ---------------------------------------------------------------- -------------------
438 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:25:28
701 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:27:18
367 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:29:40
895 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:30:08
487 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:30:23
2407 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:30:32
6 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:30:54
80 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:31:05
1899 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:34:27
2162 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:35:19
2648 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:36:11
xxx
SID BLOCKING_SESSION NAME xxxGRAM EVENT TO_CHAR(LOGON_TIME,
---------- ---------------- -------------------------- xxx--------------------------------------------- ---------------------------------------------------------------- -------------------
221 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:36:50
1229 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:40:13
2071 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:40:28
1783 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:41:43
2333 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:42:51
151 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:42:52
390 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:43:53
2188 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:45:47
2977 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:47:16
3002 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:49:04
1711 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:50:18
xxx
SID BLOCKING_SESSION NAME xxxGRAM EVENT TO_CHAR(LOGON_TIME,
---------- ---------------- -------------------------- xxx--------------------------------------------- ---------------------------------------------------------------- -------------------
2646 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:50:30
675 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:51:01
1923 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:51:02
2377 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:51:30
484 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:51:48
795 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:52:21
1592 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:53:00
1423 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:53:07
2067 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:54:18
2455 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:55:08
2479 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:55:14
xxx
SID BLOCKING_SESSION NAME xxxGRAM EVENT TO_CHAR(LOGON_TIME,
---------- ---------------- -------------------------- xxx--------------------------------------------- ---------------------------------------------------------------- -------------------
2505 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:55:24
579 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 08:57:01
2718 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 09:00:22
2863 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 09:01:49
320 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 09:01:55
2285 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 09:02:17
1470 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 09:02:52
2693 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 09:03:14
222 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 09:03:19
436 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 09:03:19
1853 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 09:04:32
xxx
SID BLOCKING_SESSION NAME xxxGRAM EVENT TO_CHAR(LOGON_TIME,
---------- ---------------- -------------------------- xxx--------------------------------------------- ---------------------------------------------------------------- -------------------
2762 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 09:06:10
2787 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 09:06:33
460 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 09:06:42
389 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 09:06:51
2576 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 09:08:30
1110 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 09:10:33
3007 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 09:12:41
244 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 09:13:48
2263 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 09:14:01
2359 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 09:14:02
1131 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 09:15:21
xxx
SID BLOCKING_SESSION NAME xxxGRAM EVENT TO_CHAR(LOGON_TIME,
---------- ---------------- -------------------------- xxx--------------------------------------------- ---------------------------------------------------------------- -------------------
2310 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 09:15:53
2117 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 09:15:54
1345 1657 CHIS xxxH.exe cursor: pin S wait on X 2018-12-24 09:16:44
1304 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-08 08:10:22
224 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-08 08:25:34
1808 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-08 08:29:08
2545 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-08 08:37:52
1399 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-08 08:38:43
1156 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-08 08:58:46
3053 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-08 09:24:52
51 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-08 09:29:14
xxx
SID BLOCKING_SESSION NAME xxxGRAM EVENT TO_CHAR(LOGON_TIME,
---------- ---------------- -------------------------- xxx--------------------------------------------- ---------------------------------------------------------------- -------------------
464 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-08 10:11:05
2767 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-08 14:53:42
2789 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-09 07:58:59
2934 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-09 07:59:42
559 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-09 08:02:36
2380 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-09 08:15:59
2792 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-09 08:28:45
243 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-09 08:36:17
1299 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-09 08:38:08
510 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-09 08:39:26
1804 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-09 08:49:32
xxx
SID BLOCKING_SESSION NAME xxxGRAM EVENT TO_CHAR(LOGON_TIME,
---------- ---------------- -------------------------- xxx--------------------------------------------- ---------------------------------------------------------------- -------------------
1489 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-09 09:57:09
1160 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-09 10:10:08
890 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-09 15:38:49
105 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-09 18:19:53
2096 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-10 08:12:59
2713 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-10 08:20:53
53 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-10 08:27:06
2288 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-10 09:28:38
387 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-10 11:07:07
1301 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-10 11:08:40
246 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-10 12:05:16
xxx
SID BLOCKING_SESSION NAME xxxGRAM EVENT TO_CHAR(LOGON_TIME,
---------- ---------------- -------------------------- xxx--------------------------------------------- ---------------------------------------------------------------- -------------------
152 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-10 14:27:15
2118 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-10 14:50:28
1496 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-10 14:54:18
2527 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-10 15:55:01
79 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-11 07:23:34
2456 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-11 07:58:58
849 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-11 08:11:02
1685 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-11 08:17:26
991 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-11 08:22:23
1778 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-11 08:30:55
1664 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-11 08:32:27
xxx
SID BLOCKING_SESSION NAME xxxGRAM EVENT TO_CHAR(LOGON_TIME,
---------- ---------------- -------------------------- xxx--------------------------------------------- ---------------------------------------------------------------- -------------------
724 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-11 08:34:31
2163 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-11 08:37:55
1562 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-11 08:43:32
1161 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-11 14:01:11
1 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-11 14:51:46
2236 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-11 15:16:55
728 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-11 17:27:46
123 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-11 18:03:02
2305 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-12 08:10:22
798 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-12 08:14:00
1253 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-12 08:26:14
xxx
SID BLOCKING_SESSION NAME xxxGRAM EVENT TO_CHAR(LOGON_TIME,
---------- ---------------- -------------------------- xxx--------------------------------------------- ---------------------------------------------------------------- -------------------
26 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-12 08:41:56
2332 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-12 11:04:28
125 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-12 11:30:49
1998 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-13 08:14:03
2143 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-13 08:32:51
2434 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-13 13:14:14
2978 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-13 15:22:39
967 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-13 15:26:04
772 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-13 15:28:17
1996 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-13 17:45:11
2500 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-13 20:01:55
xxx
SID BLOCKING_SESSION NAME xxxGRAM EVENT TO_CHAR(LOGON_TIME,
---------- ---------------- -------------------------- xxx--------------------------------------------- ---------------------------------------------------------------- -------------------
2017 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-14 08:02:00
2214 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-14 08:02:37
2145 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-14 08:04:26
1205 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-14 08:37:52
1682 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-14 08:38:15
1183 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-14 09:40:48
3003 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-14 10:51:55
1352 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-14 11:31:18
677 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-14 11:40:06
1249 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-14 15:37:17
2929 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-15 08:18:07
xxx
SID BLOCKING_SESSION NAME xxxGRAM EVENT TO_CHAR(LOGON_TIME,
---------- ---------------- -------------------------- xxx--------------------------------------------- ---------------------------------------------------------------- -------------------
124 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-15 08:22:07
654 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-15 08:29:20
1371 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-15 08:35:07
1037 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-15 09:36:22
1637 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-15 09:41:13
1803 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-15 10:03:18
101 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-15 10:39:09
150 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-15 10:56:10
1401 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-15 11:00:47
537 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-15 11:14:24
1321 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-15 11:18:15
xxx
SID BLOCKING_SESSION NAME xxxGRAM EVENT TO_CHAR(LOGON_TIME,
---------- ---------------- -------------------------- xxx--------------------------------------------- ---------------------------------------------------------------- -------------------
726 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-15 11:21:12
483 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-15 14:37:16
3006 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-15 15:43:33
1038 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-15 17:46:41
269 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-16 07:55:47
340 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-16 08:10:50
1495 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-16 08:11:11
1129 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-16 14:33:25
2836 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-16 15:32:12
272 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-16 17:14:09
317 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-16 17:57:16
xxx
SID BLOCKING_SESSION NAME xxxGRAM EVENT TO_CHAR(LOGON_TIME,
---------- ---------------- -------------------------- xxx--------------------------------------------- ---------------------------------------------------------------- -------------------
820 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-17 08:06:09
2360 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-17 08:14:08
1303 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-17 08:19:03
1278 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-17 08:19:05
28 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-17 08:32:03
1565 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-17 08:43:52
1897 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-17 08:59:38
750 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-17 09:01:18
1639 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-17 09:45:24
1083 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-17 10:12:18
2239 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-17 11:30:30
xxx
SID BLOCKING_SESSION NAME xxxGRAM EVENT TO_CHAR(LOGON_TIME,
---------- ---------------- -------------------------- xxx--------------------------------------------- ---------------------------------------------------------------- -------------------
2233 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-17 11:44:16
2744 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-17 14:36:20
1686 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-18 08:36:15
366 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-18 08:38:18
1206 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-18 14:30:43
3029 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-18 14:55:54
2092 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-18 15:01:40
104 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-19 08:01:42
2353 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-20 07:59:57
609 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-20 09:22:19
987 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-20 09:29:52
xxx
SID BLOCKING_SESSION NAME xxxGRAM EVENT TO_CHAR(LOGON_TIME,
---------- ---------------- -------------------------- xxx--------------------------------------------- ---------------------------------------------------------------- -------------------
2647 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-20 09:41:50
55 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-20 10:14:11
2716 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-20 10:17:53
2192 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-21 07:26:09
2261 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-21 08:11:13
1394 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-21 08:34:48
2262 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-21 08:38:03
1251 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-21 08:38:52
2839 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-21 08:47:48
1929 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-21 08:49:49
2282 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-21 10:10:26
xxx
SID BLOCKING_SESSION NAME xxxGRAM EVENT TO_CHAR(LOGON_TIME,
---------- ---------------- -------------------------- xxx--------------------------------------------- ---------------------------------------------------------------- -------------------
560 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-22 07:49:02
556 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-22 07:55:06
2577 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-22 08:16:06
2453 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-22 08:38:19
1497 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-22 08:44:47
388 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-22 08:47:30
2670 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-22 08:54:05
1105 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-22 08:56:47
866 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-22 09:20:18
77 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-22 09:37:56
102 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-22 09:56:20
xxx
SID BLOCKING_SESSION NAME xxxGRAM EVENT TO_CHAR(LOGON_TIME,
---------- ---------------- -------------------------- xxx--------------------------------------------- ---------------------------------------------------------------- -------------------
81 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-22 10:04:35
629 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-22 10:28:17
1633 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-22 14:37:25
148 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-22 19:57:45
630 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-23 08:17:19
914 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-23 08:22:24
292 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-23 08:22:31
608 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-23 08:25:00
2475 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-23 08:26:30
1011 1657 CHIS xxxH.exe cursor: pin S wait on X 2019-01-23 08:28:42
230 rows selected.
通过以上看到，全部都在等待 1657这个会话，这个 1657在干什么呢？
SQL> select sid,sql_id,username,to_char(logon_time,'yyyy-mm-dd hh24:mi:ss') ,event from v$session where sid=1657;
SID SQL_ID USERNAME TO_CHAR(LOGON_TIME, EVENT
---------- ------------- ------------------------------ ------------------- ----------------------------------------------------------------
1657 brk444q27u5p5 CHIS 2018-12-24 08:22:02 single-task message
原来在等待 single-task message ，这个等待事件一般是dblink引起。
看看具体在执行什么：
SQL> set long 20000
SQL> Select sql_text From v$sqltext a
2 Where a.hash_value=
3 (Select sql_hash_value
4 From v$session b
5 Where b.sid=1657)
6 Order By piece Asc;
SQL_TEXT
----------------------------------------------------------------
Select 小行星
From x_bld@bloodhis a , xxx_info b Where a.住院
号=b.admit_num And b.ADMIT_ID=:P_0
Group By a.血型,a.RH,a.住院号
8 rows selected.
SQL>
这里确实有一个 dblink bloodhis ,赶紧找到这个机器，
对方是一个windows机器，通过 systeminfo，立马看到，2018-12-24 8：32重启了。
