问题现象
顾问在系统监视器中发现了大量的锁,手动解锁无效果。
查询数据库发现大量的锁表
SELECT l.session_id sid,
s.serial#,
l.locked_mode 锁模式,
l.oracle_username,
l.os_user_name,
s.machine,
s.terminal,
o.object_name,
s.logon_time
FROM v$locked_object l, all_objects o, v$session s
WHERE l.object_id = o.object_id
AND l.session_id = s.sid
ORDER BY sid, s.serial#;
问题分析
查看数据库中有很多TX-row lock contention 事件。
select last_call_et,v.event,
s.sql_id,
--- s.SQL_FULLTEXT,
s.SQL_TEXT,
v.inst_id,
V.SID,
V.CLIENT_IDENTIFIER,
v.blocking_session,
v.blocking_session_status,
'alter system kill session ''' || v.sid || ',' || v.serial# || ''' immediate;',
v.USERNAME,
s.CPU_TIME,
s.ELAPSED_TIME,
v.PROGRAM,
'kill -9 ' || p.spid,
v.CLIENT_INFO,
v.SQL_HASH_VALUE,
v.SQL_ADDRESS,
v.MACHINE,
v.TERMINAL, s.DISK_READS,s.BUFFER_GETS,s.SORTS,s.SHARABLE_MEM,s.PERSISTENT_MEM,s.RUNTIME_MEM,s.ROWS_PROCESSED
from gv$session v, gv$process p, gv$sql s
where v.last_call_et > 0
and v.status = 'ACTIVE'
and v.username != 'SYS'
and p.addr = v.paddr
and s.ADDRESS = v.SQL_ADDRESS
and s.HASH_VALUE = v.SQL_HASH_VALUE
order by last_call_et desc;
源头为批量审批,查看nmc对应的堆栈信息发现在发送信息。
非标准产品堆栈,让客开查了下代码,是再向致远oa发东西,一直没有返回结果。询问了客户得知为护网行动,封闭了相关的IP和端口所致。
解决办法
与客户沟通后可不向外部系统发消息,出补丁禁用此发送消息处。