ORACLE數據庫慢排除

1、查詢當前被鎖的進程、用戶、對象。

select sess.sid,
sess.serial#,
lo.oracle_username,
lo.os_user_name, 
ao.object_name,
lo.locked_mode
from v$locked_object lo, dba_objects ao, v$session sess
where ao.object_id = lo.object_id and lo.session_id = sess.sid; 

2、批量KILL進程

declare 
  v_minutes NUMBER:=1000;
  v_Str VARCHAR2(100);
  CURSOR C_users(v_minutes number) IS
   SELECT s.username, 
         s.status, s.machine, 'alter system kill session ' 
         ||''''||s.sid||','||s.serial# ||'''' operates
   FROM v$session s, v$process p
   WHERE TYPE = 'USER'
   AND p.addr = s.paddr
   AND status != 'KILLED'
   and exists
       (select 1
        from dba_ddl_locks a,
             v$session b
        where a.session_id=b.SID
        and b.USERNAME NOT IN ('IFSAPP','AUTOS','THK')  --
        and b.sid = s.sid)
   ORDER BY last_call_et desc;
BEGIN
   FOR T_users IN C_users(v_minutes) LOOP
     v_Str := T_USERS.OPERATES;
     BEGIN
     EXECUTE IMMEDIATE v_str;
     EXCEPTION 
      WHEN OTHERS THEN
       NULL;
     END;
   END LOOP;
END;

3、查詢用戶執行時間

select --l.*,o.owner object_owner, o.object_Name,mac.status,mac.osuser,mac.machine
       trunc(l.ctime/60,0) mm,(l.ctime/60-trunc(l.ctime/60,0))*60 ss,
       l.sid,mac.SERIAL# serial,l.type,mac.osuser,mac.machine,mac.USERNAME user_, o.object_Name,mac.status,
       mac.logon_time,'kill -9 '||(select spid from v$process p, v$session s where p.addr=s.paddr and s.sid =mac.sid) spid
       ,mac.program       --,mac.*,o.*
from (select l.* from v$lock l where l.type = 'TM') l,
     sys.all_objects o, sys.v_$session mac
where  l.id1 = o.object_id 
and    l.sid  = mac.sid;

4、查詢耗時較長的進程

SELECT  c.last_call_et,  b.value   ,c.PROGRAM,c.MACHINE  ,  c.status, c.*,  
c.SERIAL#,c.SCHEMANAME,c.TERMINAL 
FROM  v$statname a,v$sesstat b, v$session c 
WHERE  a.STATISTIC# =b.STATISTIC#    
AND  b.sid =c.sid 
AND a.name ='CPU used by this session'
AND Value > 100000  
ORDER BY Value DESC;
 
SELECT  c.last_call_et,  b.value  ,c.PROGRAM,c.MACHINE   ,  c.status, c.*,  
c.SERIAL#,c.SCHEMANAME,c.TERMINAL 
FROM  v$statname a,v$sesstat b, v$session c 
WHERE  a.STATISTIC# =b.STATISTIC#    
AND  b.sid =c.sid 
AND a.name ='consistent gets'
AND Value > 100000000  
ORDER BY Value DESC