没人遇到这样的问题吗?
补充一个现象,v$session中"SQL*Net message from client"很多,下面是随便选择一个时间点查到的数据:
SQL> select t.EVENT, count(1) from v$session t group by t.EVENT;
EVENT COUNT(1)
---------------------------------------------------------------- ----------
SQL*Net message from client 48
control file sequential read 1
jobq slave wait 1
db file sequential read 1
rdbms ipc message 10
smon timer 1
pmon timer 1
Streams AQ: qmn slave idle wait 1
SQL*Net message to client 2
Streams AQ: waiting for time management or cleanup tasks 1
Streams AQ: qmn coordinator idle wait 1
11 rows selected
最初要解决的问题是查询下面SQL时,长时间没有返回结果,查看v$session_wait,唯一的EVENT就是"SQL*Net message from client"
select /*+rule*/
a.sid,
b.serial#,
a.type,
c.object_name,
b.username,
a.lmode,
B.MACHINE,
D.SPID,
a.ctime / 3600 ctime
from (select * from v$lock) a, v$session b, all_objects c, V$PROCESS D
where a.sid = b.sid
and a.type in ('TM', 'TX')
and c.object_id = a.id1
AND B.PADDR = D.ADDR
order by a.ctime / 3600 desc;
进而查看服务端的资源使用情况,发现前面描述的情况。
希望高手不吝赐教。谢谢!