Oracle  是运行在操作系统上的，也要结合看看主机和存储的使用效率。Oracle层面可以查看会话，也可以后期跑跑awr看看

====会话相关

1.查询长事务

set line 178

col UNITS  for a10

col MESSAGE for a50

select sid,TOTALWORK,UNITS,START_TIME,TIME_REMAINING,ELAPSED_SECONDS,MESSAGE from v$session_longops order by START_TIME asc;

2.查询等待事件

select sid,event from v$session_wait where sid in (32,56);

3.根据系统pid查询

select a.SID,a.SERIAL#,a.username,a.sql_id,b.pid,b.PROGRAM from v$session a,v$process b where a.PADDR = b.addr and b.spid = &spid;

select a.SID,a.SERIAL#,a.username,a.sql_id,b.pid,b.spid,b.PROGRAM from v$session a,v$process b where a.PADDR = b.addr and a.sid=&sid;

根据系统pid查询SQL

select d.username,a.sql_id ,a.SQL_FULLTEXT from v$sqlarea a ,

(select b.SID,b.SERIAL#,b.username,b.sql_id,c.spid from v$session b,v$process c where b.PADDR =c.addr and c.spid = &spid) d  where d.sql_id=a.sql_id;

select d.username,a.sql_id ,a.SQL_FULLTEXT from v$sqlarea a ,

(select b.SID,b.SERIAL#,b.username,b.sql_id,c.spid from v$session b,v$process c where b.PADDR =c.addr and b.sid = &sid) d  where d.sql_id=a.sql_id;

4.查看等待事件

select event from v$session_wait where sid = &sid;收起