세션 모니터링 쿼리

현재 ACTIVE인 세션 중에 어떤 SQL을 수행하고, 어떤 유저에서 수행중인지 등 기본적인 정보를 쉽게 알기위한 쿼리입니다.

 

 

 

쿼리

select 
p.inst_id,
p.spid,
s.sid,
s.serial#,
s.process,
s.status as SESS_STATUS,
t.status as TX_STATUS,
s.username,
s.osuser,
substr(s.machine,instr(s.machine,'\',-1)+1) machine,
s.module,
sc.command_name,
s.event,
s.sql_id,
TO_CHAR(SYSDATE - (S.LAST_CALL_ET/86400), 'YYYYMMDD HH24:MI:SS') AS EXEC_TIME,
s.seconds_in_wait wait_time,
S.LAST_CALL_ET,
round(pga_used_mem/1024/1024)||' MB' sess_pga, 
substr(q.sql_text,1,25) sql_text,
'alter system kill session '''||s.sid|| ',' ||s.serial#||',@'||s.INST_ID||''' immediate;' kill


from gv$session s, gv$process p,gv$sess_io i,gv$transaction t , gv$sqlarea q, gv$sqlcommand sc
where s.sid = i.sid(+)
and s.paddr = p.addr(+)
and s.saddr = t.ses_addr(+)
and s.sql_id = q.sql_id(+)
and s.command = sc.command_type
and s.inst_id = p.inst_id and s.inst_id = i.inst_id (+) and s.inst_id = t.inst_id (+) and s.inst_id = q.inst_id (+) and s.inst_id = sc.inst_id 

---
AND S.TYPE   != 'BACKGROUND'
and S.status = 'ACTIVE'
--and s.username='DEV'
order by LAST_CALL_ET desc;

 

결과값.

INST_ID   SPID                               PROCESS                            SESS_STATUS TX_STATUS        USERNAME     OSUSER              MACHINE           MODULE                                       COMMAND_NAME   EVENT                          SQL_ID        EXEC_TIME         WAIT_TIME LAST_CALL_ET SESS_PGA                                    SQL_TEXT                                                                                             KILL                                                                                                                                                               
--------- ---------------------------------- ---------------------------------- ----------- ---------------- ------------ ------------------- ----------------- -------------------------------------------- -------------- ------------------------------ ------------- ----------------- --------- ------------ ------------------------------------------- ---------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------ 
        1 13173144                           1120:14576                         INACTIVE    ACTIVE           DEV          110420201094793     K10420201094793   Trusted Orange for ORACLE (Unicode) DBA      INSERT         SQL*Net message from client    5pctm7zpx10g3 20230117 14:39:48       970          970 10 MB                                       INSERT INTO ORD_ITEM_RANG                                                                            alter system kill session '2467,43274,@1' immediate;                                                                                                              

1 rows selected.

 

 

 

 

 

 

필요 딕셔너리 뷰

1.v$session(gv$session)

V$SESSION displays session information for each current session.

각 세션의 정보를 보여줍니다.

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-SESSION.html#GUID-28E2DC75-E157-4C0A-94AB-117C205789B9c

Database Reference

 

2.v$process(gv$process)

V$PROCESS displays information about the currently active processes.

active 한 프로세스의 정보를 보여줍니다.

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-PROCESS.html

Database Reference

 

3.v$sess_io(gv$sess_io)

V$SESS_IO displays I/O statistics for each user session.

각 유저 세션의 I/O 정보를 보여줍니다.

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-SESS_IO.html

Database Reference

 

4.v$sqlcommand(gv$sqlcommand)

V$SQLCOMMAND displays the mapping between SQL opcodes and names.

SQL opcode와 이름을 매핑해주는 딕셔너리 뷰입니다.

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-SQLCOMMAND.html

Database Reference

 

5.v$transaction(gv$transaction)

V$TRANSACTION lists the active transactions in the system.

active한 트랜잭션을 보여주는 딕셔너리 뷰입니다.

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-TRANSACTION.html

Database Reference

 

6.v$sqlarea(gv$sqlarea)

V$SQLAREA displays statistics on shared SQL areas and contains one row per SQL string. It provides statistics on SQL statements that are in memory, parsed, and ready for execution.

 

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-SQLAREA.html

Database Reference