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

2017-01-13 19:13:34来源:CSDN作者:lgz01人点击

可以通过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);


操作示例:




最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台