oracle查找当前比较消耗资源的session

可以通过v$session 看到当前session信息，可以从v$sesstat中看到session的状态。通过和v$statname进行联合查询，就可以得到当前session的获得资源的信息：

1.找到当前所有session IO信息
select ses.username,ses.sid,ses.status,
   max(decode(sta.name,'session logical reads',sest.value)) as "LOG IO",
   max(decode(sta.name,'physical reads',sest.value)) as "PHY IO",
   round(max(decode(sta.name,'session logical reads',sest.value))
        /(3600*24*(sysdate-ses.logon_time)),2) as "LOG IO/S",
   round(max(decode(sta.name,'physical reads',sest.value))
        /(3600*24*(sysdate-ses.logon_time)),2) as "PHY IO/S",
        trunc(60*24*(sysdate-ses.logon_time)) as "Minutes"
        from v$session ses,v$sesstat sest,v$statname sta
        where ses.sid=sest.sid and sest.statistic#=sta.statistic#
           and sta.name in ('session logical reads','physical reads')
           and ses.username is not null
   group by ses.username,ses.sid,ses.status,ses.logon_time
   order by 1,2

操作示例：

2．然后通过找到的sid，可以查看该session正在执行什么sql，以及sql的执行计划：

select sql_fulltext from v$sqlarea
  where (hash_value,address) =
         (select decode(sql_hash_value,0,prev_hash_value,sql_hash_value) as hash_value,
                decode(sql_hash_value,0,prev_sql_addr,sql_address) as sql_addr
                 from v$session where sid=&sid);
                 

操作示例：

SELECT operation, options, object_name, cost FROM v$sql_plan
   WHERE (hash_value,address)=
  (select decode(sql_hash_value,0,prev_hash_value,sql_hash_value) as hash_value,
       decode(sql_hash_value,0,prev_sql_addr,sql_address) as sql_addr
        from v$session where sid=&sid);

操作示例：