ACTIVE 세션 모니터링 뷰
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 ACTIVE 세션 모니터링 뷰 VW_DBA_ACTV_SESS[편집]
CREATE OR REPLACE FORCE VIEW VW_DBA_ACTV_SESS
AS
SELECT ROWNUM AS NO
, TBL."CPU(%)"
, TBL."SID"
, TBL."SQL_ID"
, TBL."KILL_SESSION"
, TBL."SQL_TEXT"
, TBL."PGA_ALLOC"
, TBL."WAIT_EVENT"
, TBL."LCET(s)"
, TBL."MODULE"
, TBL."MACHINE"
, TBL."OSUSER"
, TBL."PROGRAM"
, TBL."USERNAME"
, TBL."TYPE"
, TBL."STATUS"
, TBL."STATE"
, TBL."WAIT_TIME"
, TBL."SEC_IN_WAIT"
, TBL."LOGON"
--, TBL."KILL_SESSION_AWS",
, TBL."KILL_SPID"
, TBL."TRACEFILENAME"
, TBL."SERIAL"
, TBL."CPID"
, TBL."SPID"
, TBL."AUDSID"
, TBL."SQL_CHILD_NUMBER"
, TBL."TRACE_SCRIPT"
, TBL."TRACE_SCRIPT2"
, TBL."WAIT_DETAIL"
, TBL."SEQ#"
, TBL."SQL_PLAN_HASH_VALUE"
, TBL."SQL_HASH_VALUE"
, TBL."QCSID"
, TBL."DEGREE"
, TBL."SERVER_NUM"
FROM ( SELECT /*+ leading(s p sst1 sst2) */
(SELECT CASE
WHEN SUM (SESMET.CPU) = 0
THEN
TO_CHAR ('0', '9990.99')
ELSE
TO_CHAR (
TRUNC (
( SUM (SESMET.CPU)
/ (CPU_ALL_SUM))
* 1000)
/ 10
, '990.99')
END AS CPU_RATIO
FROM V$SESSMETRIC SESMET
WHERE SESMET.SESSION_ID = s.sid) AS "CPU(%)"
, s.sid AS sid
, s.sql_id
, TRIM ((SELECT SUBSTR (sql_text, 1, 50)
FROM v$sql sq
WHERE sq.sql_id = s.sql_id AND ROWNUM = 1)) AS sql_text
, LPAD (
TRIM (
TO_CHAR (ROUND (p.pga_alloc_mem / 1024 / 1024)
, '999,999'))
|| 'MB'
, 7
, ' ') AS pga_alloc
, w.event AS wait_event
, fn_sec_to_time (s.last_call_et) AS "LCET(s)"
, s.module AS module
, REPLACE (REPLACE (s.machine, 'CORP\', '')
, 'WORKGROUP\'
, '') AS MACHINE
, s.osuser AS osuser
, s.program AS program
, s.username AS username
, s.TYPE AS TYPE
, status AS status
, w.STATE AS STATE
, w.WAIT_TIME
, w.seconds_in_wait AS SEC_IN_WAIT
, TO_CHAR (logon_time, 'yyyymmdd HH24:MI:SS') AS LOGON
, -- 'exec rdsadmin.rdsadmin_util.kill('
-- || s.sid
-- || ','
-- || s.serial#
-- || ');'
-- AS kill_session_aws,
'alter system kill session '
|| ''''
|| s.sid
|| ','
|| s.serial#
|| ''''
|| ' ; ' AS Kill_Session
, 'kill -9 ' || p.spid AS kill_Spid
, ( (SELECT VALUE
FROM v$parameter
WHERE name = 'instance_name')
|| '_ora_'
|| p.SPID
|| CASE (SELECT NVL (VALUE, 'N/A')
FROM v$parameter
WHERE name = 'tracefile_identifier')
WHEN 'N/A'
THEN
''
ELSE
'_'
|| (SELECT VALUE
FROM v$parameter
WHERE name = 'tracefile_identifier')
END
|| '.trc') AS TraceFileName
, s.serial# AS Serial
, s.process AS cpid
, p.spid AS spid
, s.audsid
, s.SQL_CHILD_NUMBER
, 'exec sys.dbms_system.set_ev('
|| s.sid
|| ','
|| s.serial#
|| ',10046,8,'
|| ''''
|| ''''
|| ')' AS trace_script
, 'exec sys.dbms_system.set_sql_trace_in_session('
|| s.sid
|| ','
|| s.serial#
|| ',false'
|| ')' AS trace_script2
, '(p1,p2,p3)=('
|| w.p1text
|| ','
|| w.p2text
|| ','
|| w.p3text
|| ')=('
|| w.p1
|| ','
|| w.p2
|| ','
|| w.p3
|| ')' AS wait_detail
, w.seq#
, '' AS SQL_PLAN_HASH_VALUE
, s.sql_hash_value
, ps.qcsid
, ps.degree
, CASE
WHEN ps.sid IS NOT NULL
THEN
CASE
WHEN PS.SERVER# IS NULL THEN '[QC] 0'
ELSE '[SP] ' || TO_CHAR (PS.SERVER#)
END
ELSE
NULL
END AS SERVER_NUM
FROM v$session s
INNER JOIN v$session_wait w ON s.sid = w.sid
INNER JOIN v$process p ON s.paddr = p.addr
LEFT JOIN v$px_session ps
ON s.sid = ps.sid AND s.serial# = ps.serial#
, (SELECT SUM (CPU) AS CPU_ALL_SUM FROM V$SESSMETRIC)
CPU_STATUS
WHERE 1 = 1
AND s.status = 'ACTIVE'
AND s.TYPE = 'USER'
AND s.sid != (SELECT DISTINCT sid
FROM v$mystat
WHERE ROWNUM < 2)
AND s.username IS NOT NULL
AND w.event NOT IN
('queue messages'
, 'pipe get'
, 'jobq slave wait'
, 'Streams AQ: waiting for messages in the queue'
, 'gcs remote message'
, 'rdbms ipc message'
, 'class slave wait'
, 'slave wait')
ORDER BY "CPU(%)" DESC) TBL;2 KILLED 세션 모니터링 뷰 VW_DBA_ACTV_SESS[편집]
CREATE OR REPLACE FORCE VIEW VW_DBA_KILLED_DISCONNECT
AS
SELECT USERNAME
, STATUS
,'ALTER SYSTEM DISCONNECT SESSION '''
|| S.SID
|| ','
|| S.SERIAL#
|| ''' IMMEDIATE;' DISCONECT_CMD
FROM V$SESSION S
WHERE STATUS = 'KILLED' --<> 'ACTIVE'
;