-- 0. Select Trace info
SELECT * FROM V$PARAMETER WHERE NAME LIKE '%trace%';
-- 1. Service Name 조회 (SYSTEM)
SHOW PARAMETER SERVICE_NAME;
SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME LIKE '%service%';
-- 2. 현재 Trace 대상 Session 조회 (SYSTEM)
SELECT SID, SERIAL#, USERNAME, SERVICE_NAME, MODULE, ACTION
FROM V$SESSION
WHERE 1 = 1
AND USERNAME IS NOT NULL
--AND SERVICE_NAME <> 'SYS$BACKGROUND'
--AND TYPE = 'USER'
;
SID SERIAL# USERNAME SERVICE_NAME MODULE ACTION
---------- ---------- ---------------- ----------------- ---------------------------- ----------
9 17521 SYSTEM SYS$USERS SQL Develpoer
630 13267 SCOTT SYS$USERS SQL Developer
-- 3. Module, Action 명칭수정 (SCOTT)
BEGIN
DBMS_APPLICATION_INFO.SET_MODULE (
MODULE_NAME => 'SQL Develpoer Trace Test'
, ACTION_NAME => 'Trace Test'
);
END;
-- 4. 현재 Trace 대상 Session 조회 (SYSTEM)
SELECT SID, SERIAL#, USERNAME, SERVICE_NAME, MODULE, ACTION
FROM V$SESSION
WHERE 1 = 1
AND USERNAME IS NOT NULL
--AND SERVICE_NAME <> 'SYS$BACKGROUND'
--AND TYPE = 'USER'
;
SID SERIAL# USERNAME SERVICE_NAME MODULE ACTION
---------- ---------- ---------------- ----------------- ---------------------------- ----------
9 17521 SYSTEM SYS$USERS SQL Develpoer
630 13267 SCOTT SYS$USERS SQL Develpoer Trace Test Trace Test
-- 5. 특정세션에만 Trace 걸기 (SYSTEM)
BEGIN
DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE (
SERVICE_NAME => 'SYS$USERS'
, MODULE_NAME => 'SQL Develpoer Trace Test'
, ACTION_NAME => DBMS_MONITOR.ALL_ACTIONS
, WAITS => TRUE
, BINDS => TRUE
);
END;
-- 6. Trace File Path (SCOTT)
SELECT * FROM V$DIAG_INFO WHERE NAME = 'Default Trace File';
-- 7. Enabled Trace List (SYSTEM)
SELECT * FROM DBA_ENABLED_TRACES;
-- 8. 특정세션의 Trace 해제 (SYSTEM)
BEGIN
DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE (
SERVICE_NAME => 'SYS$USERS'
, MODULE_NAME => 'SQL Develpoer Trace Test'
, ACTION_NAME => DBMS_MONITOR.ALL_ACTIONS
);
END;
-- 9. Enabled Trace List (SYSTEM)
SELECT * FROM DBA_ENABLED_TRACES;
[오라클 성능고도화] 다른 세션에 TRACE 실행 및 확인법 (service, module, action) (176P)
|
2019.04.14 17:12:41
|
2019.04.15 23:41:18
|
421
|
Aiden
Total of Attached file
0.00 Bytes of 0 files
2022.07.01
2022.04.22
2020.01.20
2019.04.11
2019.03.04