找出最耗資源的sql

select * from v$process where spid='2796';

select sql_hash_value,machine,username,program from v$session where PAddr='63B7A584';[@more@]select * from v$sqltext where hash_value='833203018';

select * from v$sql where hash_value='833203018';

select * from v$sqlarea where hash_value='833203018';

SELECT a.username,a.machine,a.program,a.sid,a.serial#,a.status,c.piece,c.sql_text FROM v$session a,v$process b,v$sqltext c WHERE b.spid='2796' AND b.addr=a.paddr AND a.sql_address=c.address(+) ORDER BY c.piece

要找出最耗資源的sql，我們可以首先使用top等工具，找到最好資源的程式（記住程式號），例如，作業系統程式號為2796，然後根據這個程式號(v$process.spid)在v$process中找到程式地址(v$process.addr)，然後根據這個地址在v$session中找到相應的sid(v$session.sid)，然後根據這個sid找到相應的hash alue（v$session. sql_hash_value），然後根據這個hash alue在v$sqltext，$sql，v$sqlarea等檢視中找到對應的sql語句（sql_text）。

select * from v$process where spid='2796';

select sql_hash_value,machine,username,program from v$session where PAddr='63B7A584';

select * from v$sqltext where hash_value='833203018';

select * from v$sql where hash_value='833203018';

select * from v$sqlarea where hash_value='833203018';

SELECT a.username,a.machine,a.program,a.sid,a.serial#,a.status,c.piece,c.sql_text FROM v$session a,v$process b,v$sqltext c WHERE b.spid='2796' AND b.addr=a.paddr AND a.sql_address=c.address(+) ORDER BY c.piece

參考：：