快速找出top执行的sql语句

快速找出top执行的sql语句[@more@]

SELECT saddr,paddr,sid,serial#,PREV_SQL_ADDR,PREV_HASH_VALUE,SQL_HASH_VALUE
FROM V$SESSION

select SQL_TEXT,SORTS,FETCHES,EXECUTIONS,LOADS,DISK_READS,BUFFER_GETS,
ROWS_PROCESSED,OPTIMIZER_MODE,CPU_TIME,ADDRESS from V$SQL

select sql_text,cpu_time,address,hash_value
from v$sql
where (address,hash_value) in
(select sql_address,sql_hash_value from v$session s,v$process p
where s.paddr=p.addr
and p.spid='&pid'
)
--对应到系统的进程ID号,win下的线程，linux下的真实PID号

select addr,spid,username,pga_used_mem/1024 k from v$process

col username for a16
col lockwait for a8
col osuser for a10
col process for a8
select process Pid,sid,serial#,username,lockwait,osuser,program
from v$session

select s.sid,s.serial#,s.username,s.machine,s.terminal from v$session s,v$process p
where s.paddr=p.addr
and p.spid='&pid'

select sid,serial#,username,machine,terminal,program from v$session s
where process='&pid' --找到进程占用CPU的用户会话，连接主机,终端，运行程序等

alter system kill session '&sid,&serial#';--kill用户连接会话

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

转载于:http://blog.itpub.net/9194732/viewspace-912225/