[오라클] 현재 ACTIVE SESSION 조회 스크립트

1. 현재 ACTIVE SESSION 확인

-- 현재 ACTIVE SESSION 확인
select sid, serial#, machine, username, osuser, schemaname, service_name, process, program, last_call_et, status
from v$session
where status = 'ACTIVE'
-- and type = 'USER'
;

-- 현재 세션의 sid, serial#, pid 조회 
select s.sid, s.serial#,
(select spid from v$process p where p.addr = s.paddr) as "PID(SPID)"
from v$session s
where sid = (select sid from v$mystat where rownum = 1);

-- active session별 cpu time, memory 사용량 조회 
select s.sid, s.serial#, p.spid as "os pid", s.username, s.module, s.sql_id, event, seconds_in_wait,
st.value/100 as "cpu sec",
round(pga_used_mem/1024/1024) "pga_tot(mb)",
round(pga_used_mem/1024/1024) "pga_per_sess(mb)"
from v$sesstat st, v$statname sn, v$session s, v$process p
where sn.name = 'CPU used by this session' -- cpu
and st.statistic# = sn.statistic#
and st.sid = s.sid
and s.paddr = p.addr
--and s.last_call_et < 1800 -- active within last 1/2 hour
--and s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
and s.status = 'ACTIVE'
order by st.value;

-- active session별 CPU usage (background process제외 user session only)
SELECT s.username, t.sid, s.serial#, SUM(VALUE/100) as "cpu usage (seconds)"
FROM v$session s, v$sesstat t, v$statname n
WHERE t.STATISTIC# = n.STATISTIC#
AND NAME like '%CPU used by this session%'
AND t.SID = s.SID
AND s.status='ACTIVE'
AND s.username is not null
GROUP BY username,t.sid,s.serial#;

-- ACTIVE인 client memory 사용량 
-- 참고: machine - 컴퓨터의 시스템 속성에서 볼 수 있는 컴퓨터 이름 
--       terminal - machine과 같을 수 있지만 도메인 명을 포함하지 않음. 터미널 이름 설정되지 않을 경우 null(unknown) 가능 
select machine,status,count(*) cnt, 
       round(sum(pga_used_mem)/1024/1024) "pga_tot(mb)",
       round(sum(pga_used_mem)/count(*)/1024/1024) "pga_per_sess(mb)"
from v$session s, v$process p
where 1=1
and s.status='ACTIVE'
and s.paddr=p.addr
and type <> 'BACKGROUND'
group by machine,status
order by 1;

-- 세션 상태별, 타입별 개수 확인 
SELECT COUNT (*) total_sessions,
       COUNT (DECODE (status, 'ACTIVE', 1, NULL)) active_cnt,
       COUNT (DECODE (status, 'INACTIVE', 1, NULL)) inactive_cnt,
       COUNT (DECODE (TYPE, 'BACKGROUND', 1, NULL)) background_cnt,
       COUNT (DECODE (TYPE, 'USER', 1, NULL)) user_cnt
  FROM v$session;

-- 현재 active인 세션이 날리는 쿼리 내용 확인 
select /*+ ordered */ 
      a.sid, a.serial#, a.status, a.process, a.username, a.osuser, b.sql_text, c.program
  from v$session a, v$sqlarea b, v$process c
 where a.sql_hash_value = b.hash_value
   and a.sql_address = b.address
   and a.paddr = c.addr
   and a.status = 'ACTIVE'
-- and a.sid in (select sid from v$mystat where rownum<=1);
;

-- 세션에서 수행하는 커맨드 종류만 확인 (select, insert등) 
select
   osuser,
   substr(s.username,1,18) username,
   substr(s.program,1,15) program,
   decode(s.command,
       0,'No Command',
       1,'Create Table',
       2,'Insert',
       3,'Select',
       6,'Update',
       7,'Delete',
       9,'Create Index',
      15,'Alter Table',
      21,'Create View',
      23,'Validate Index',
      35,'Alter Database',
      39,'Create Tablespace',
      41,'Drop Tablespace',
      40,'Alter Tablespace',
      47, 'PL/SQL EXECUTE',
      53,'Drop User',
      62,'Analyze Table',
      63,'Analyze Index',
         s.command||': Other') command
from 
   v$session     s,
   v$process     p,
   v$transaction t,
   v$rollstat    r,
   v$rollname    n
where s.paddr = p.addr
and   s.taddr = t.addr (+)
and   t.xidusn = r.usn (+)
and   r.usn = n.usn (+)
and command  <> 0
order by 1
;