Oracle session分析

session

set linesize 300 pages 999             
col sid format 99999                   
col s# format 99999                    
col username format a10                
col spid for a10                       
col event format a36                   
col machine format a20                 
col program for a40                    
col p123 format a20                    
col wt format 999                      
col SQL_ID for a18                     
alter session set cursor_sharing=force;
SELECT /* XJ LEADING(S) FIRST_ROWS */ S.SID, 
    S.SERIAL# S#, 
    P.SPID, 
    S.MACHINE, 
    S.program, 
    S.EVENT, 
    S.P1 || '/' || S.P2 || '/' || S.P3 P123, 
    S.WAIT_TIME WT, 
    NVL(SQL_ID, S.PREV_SQL_ID) SQL_ID, 
    NVL(S.USERNAME, SUBSTR(P.PROGRAM, LENGTH(P.PROGRAM) - 6)) USERNAME 
FROM V$PROCESS P, V$SESSION S 
WHERE P.ADDR = S.PADDR 
AND S.STATUS = 'ACTIVE' 
AND P.BACKGROUND IS NULL; 

---

set linesize 200 pagesize 900
col message for a80
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select START_TIME,LAST_UPDATE_TIME, TIME_REMAINING, ELAPSED_SECONDS,sofar,totalwork, message from v$session_longops  where sid=&sid and SERIAL#=&siral and sofar<>totalwork order by LAST_UPDATE_TIME;   

---

cpu占比： 

set linesize 200
col username for a10
col event for a35
col program for a20 truncate
col cpu_p for 99.99
col  SQL_ID for a25
select ta.*,round(ta.cpu_time/tb.total_cpu * 100,1) cpu_usage from
(select s.username,s.program,s.event,s.sql_id,sum(trunc(m.CPU)) CPU_TIME,count(*) sum
        --,sum(m.PHYSICAL_READS) P_READ,sum(LOGICAL_READS) L_READ,
  from v$sessmetric m ,v$session s
where ( m.PHYSICAL_READS >100
       or m.CPU>100
       or m.LOGICAL_READS >100)
       and m.SESSION_ID = s.SID
       and m.SESSION_SERIAL_NUM = s.SERIAL#
       and s.status = 'ACTIVE'
       and username is not null
group by s.username,s.program,s.event,s.sql_id
order by 5 desc) ta,(select sum(cpu) total_cpu from v$sessmetric) tb
where rownum < 30;
 

---

查看连接：

set linesize 200 pagesize 900
col machine for a55
col username for a15
col module for a50
select inst_id, machine, status, count(1) from gv$session group by inst_id,machine,status;
col machine for a30
select inst_id, status, machine, username, module, count(*) from gv$session group by inst_id,status,machine,username,module order by 1,2,3,4;
select inst_id, machine, status, count(1) from gv$session group by inst_id,machine,status order by 1,4,3,2;
select username, count(*) from gv$session group by username;
set linesize 300
select * from v$resource_limit where RESOURCE_NAME in ('processes','sessions') order by 1;
select distinct username from v$session;