当前位置: 代码迷 >> SQL >> oracle 实时查询耗费资源SQL
  详细解决方案

oracle 实时查询耗费资源SQL

热度:94   发布时间:2016-05-05 12:32:57.0
oracle 实时查询消耗资源SQL

1. 先通过top命令查看产用资源较多的pid号, 注意:top命令的user的oacle的,关注pid

2.查询当前耗时的会话ID,用户名,sqlID等:其中top中的pid就是v$process的spid字段值。不是v$process视图中的pid值
select sid,serial#,machine,username,program,sql_hash_value,sql_id,
to_char(logon_time,'yyyy/mm/dd hh24:mi:ss') as login_time from v$session
where paddr in (select addr from v$process where spid in ('5648612','256500')); 其中 5648612','256500' 就是查询出的进程对应的PID值


3. 如果上一步sql_id或者 hash_value不为空,则可用v$sqlarea查出当前正在使用的sql
select sql_text
from v$sqltext_with_newlines
where hash_value = &hash_value
order by piece;

select * from v$sql where sql_id=''

?

--网友提供的----

?

SELECT sql_text
FROM v$sqltext a
WHERE (a.hash_value, a.address) IN
(SELECT DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value),
DECODE(sql_hash_value, 0, prev_sql_addr, sql_address)
FROM v$session b
WHERE b.paddr = (SELECT addr FROM v$process c WHERE c.spid = '&pid'))
ORDER BY piece ASC

  相关解决方案