找出最耗资源的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

参考：http://www.knowsky.com/5445.html：
　　　http://www.pconline.com.cn/pcjob/ittest/oracle/jy/10308/205001_2.html

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/64429/viewspace-916196/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/64429/viewspace-916196/