1. 先透過top命令檢視產用資源較多的spid號

2.查詢當前耗時的會話ID，使用者名稱，sqlID等：

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','256523'));

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 a.*,b.SQL_TEXT from (

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 ('23226'))

) a,v$sql b

where a.sql_id = b.SQL_ID(+)

4.kill佔用大資源的session

Alter system kill session 'SID,SERIAL#'

Oracle 查耗CPU資源sql語句及程式代號-轉

相關文章