oracle管理性能语句

向6720

查看用户为user的session
select * from gv$session t where t.USERNAME='user';

根据用户来统计session数量
select count(1),username from gv$session t group by username

未释放连接数，当前正在执行的sql语句
select b.SQL_TEXT,b.SQL_FULLTEXT,t.* from gv$session t,gv$sql b where t.USERNAME='IRDP' and t.SQL_ADDRESS = b.ADDRESS;

是否有锁表或者列
SELECT /*+ rule */
s.username,
decode(l.type,'TM','TABLE LOCK','TX','ROW LOCK',NULL) LOCK_LEVEL,
o.owner,
o.object_name,
o.object_type,
s.sid,
s.serial#,
s.terminal,
s.machine,
s.program,
s.osuser
FROM v$session s,v$lock l,dba_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username is NOT Null

查询session资源损耗
select * from (select a.sid,a.MACHINE,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value ,
      a.USER#,a.USERNAME
    from v$session a,v$process b,v$sesstat c  
    where c.statistic#=12 and c.sid=a.sid and  a.paddr=b.addr order by value desc) f
    where f.value>0 and f.prog='JDBC Thin Client';