--根据前程ID查询对应的Session
select sa.MODULE,sl.OPNAME, to_char(sl.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,sa.SQL_TEXT, pr.*,'||',se.*
from v$session se ,v$process pr ,v$session_longops sl,v$sqlarea sa
where se.PADDR=pr.ADDR(+)
and se.SID=sl.SID(+)
and sl.sql_hash_value = sa.hash_value(+)
and sl.START_TIME is not null
and pr.SPID in (18188)
查询排名前10低的SQL
select sa.* from (
select parsing_user_id,executions,sorts,command_type,disk_reads,sql_text,action,module,address
from v$sqlarea order by disk_reads desc
) sa where rownum<10
找到长时间执行的oracle会话，查看其操作系统进程状态
1、查询系统中长时间执行的SQL操作，顺序排列：
select sid, message from v$session_longops order by start_time;
通过MESSAGE列，可以看到会话当前执行的状态。
2、查询当前正在执行的SQL完成比率：
select sid, opname, target_desc, sofar, totalwork, trunc(sofar/totalwork*100,2) || '%' as perwork
from v$session_longops where sofar != totalwork;
3、执行下面的查询，得到当前长时间操作执行的SQL任务分别是什么：
select opname, target, to_char(start_time, 'yyyy-mm-dd hh24:mi:ss') start_time, elapsed_seconds elapsed,
executions execs, buffer_gets/decode(executions, 0, 1, executions) bufgets, module, sql_text
from v$session_longops sl, v$sqlarea sa
where sl.sql_hash_value = sa.hash_value
and upper(substr(module, 1, 4)) <> 'RMAN'
and substr(opname, 1, 4 ) <> 'RMAN'
and sl.start_time > trunc(sysdate)
order by start_time;
4、执行下面的查询，取得这些oracle进程对应的操作系统进程信息：
select p.spid, s.sid, s.serial#, s.program from v$process, v$session s
where p.addr = s.paddr and s.sid in (138);
5、以root身份登录操作系统，执行topas命令，可以看到相关操作系统任务（也可能看不到）。
--超过特定时间的查询
select opname, target, to_char(start_time, 'yyyy-mm-dd hh24:mi:ss') start_time, elapsed_seconds elapsed,
executions execs, buffer_gets/decode(executions, 0, 1, executions) bufgets, sa.module,sl.sid, sql_text,se.OSUSER,se.PROGRAM
, 'ALTER SYSTEM KILL SESSION '''||se.sid||', '||se.serial#||''';'
from v$session_longops sl, v$sqlarea sa ,v$session se
where sl.sql_hash_value = sa.hash_value
and sl.sid=se.sid
and upper(substr(sa.module, 1, 4)) <> 'RMAN'
and substr(opname, 1, 4 ) <> 'RMAN'
and sl.start_time > trunc(sysdate)
and (sa.MODULE like '%JDBC%' or sa.MODULE like '%Dis%')
and to_char(start_time, 'yyyy-mm-dd hh24:mi:ss')<'2011-10-17 13:00:00'
order by start_time;
