Oracle EBS TRACE 뜨는 방법과 성능 추적 완벽 정리

“이 화면이 왜 이렇게 느리죠?”

EBS 운영을 하다 보면 반드시 듣게 되는 질문입니다. 이때 감으로 SQL을 뜯어보기 시작하면 시간만 날아갑니다. 실제로 DB에서 무슨 일이 벌어졌는지 그대로 기록해주는 도구가 SQL Trace이고, EBS는 이 기능을 애플리케이션 레벨에서 켤 수 있도록 이미 다 만들어 두었습니다.

이 글에서는 EBS에서 trace를 켜는 방법을 Forms 화면 / Concurrent Program / OAF(Self-Service) / PL/SQL 직접 실행 네 가지 경로로 나눠서 정리하고, 생성된 trace 파일을 찾아서 tkprof로 분석하고 해석하는 것까지 다룹니다.

---

1. SQL Trace가 기록하는 것

SQL Trace는 내부적으로 event 10046을 사용합니다. 레벨에 따라 기록되는 정보가 달라집니다.

Level	기록 내용	언제 쓰나
1	기본 SQL, 실행 통계, 실행계획	어떤 SQL이 오래 걸리는지만 볼 때
4	Level 1 + 바인드 변수 값	파라미터에 따라 성능이 달라질 때
8	Level 1 + 대기 이벤트(Wait Event)	I/O·락 대기가 의심될 때
12	Level 1 + 4 + 8 (전부)	실무 기본값

특별한 이유가 없으면 Level 12로 뜹니다. 바인드 값과 대기 이벤트가 없으면 “SQL은 느린데 왜 느린지 모르겠다”는 상황이 반복됩니다.

다만 Level 12는 그만큼 trace 파일이 커집니다. 대량 배치에 걸면 수 GB가 나오는 경우도 있으니 뒤에 나오는 주의사항을 꼭 확인하세요.

---

2. Forms 화면에서 Trace 켜기 (가장 많이 쓰는 방법)

특정 화면 조회가 느릴 때 쓰는 방법입니다.

2-1. 사전 프로파일 설정

System Administrator 책임에서 Profile > System으로 이동해 아래 값을 설정합니다.

프로파일	값	레벨
Utilities:Diagnostics	Yes	User 또는 Responsibility
Hide Diagnostics menu entry	No	User

Utilities:Diagnostics가 No이면 Diagnostics 메뉴를 눌렀을 때 APPS 패스워드를 물어봅니다. 개발/테스트 환경에서는 Yes로 두는 편이 편합니다.

2-2. 실행 순서

1. 느린 화면을 연다 (아직 조회는 하지 않음)
2. 메뉴에서 Help > Diagnostics > Trace > Trace with Binds and Waits 선택
3. 팝업으로 trace 파일 경로와 파일명이 뜹니다 → 반드시 메모 또는 캡처
4. 문제가 되는 조회/저장 동작을 한 번만 수행
5. Help > Diagnostics > Trace > No Trace로 즉시 종료

메뉴에는 다음 항목들이 있습니다.

• No Trace
• Regular Trace (Level 1)
• Trace with Binds (Level 4)
• Trace with Waits (Level 8)
• Trace with Binds and Waits (Level 12)

2-3. 주의할 점

3번에서 뜨는 경로를 놓치면 나중에 파일을 찾느라 고생합니다. 만약 놓쳤다면 아래 쿼리로 현재 세션의 trace 파일을 확인할 수 있습니다.

sql

SELECT value AS trace_file
  FROM v$diag_info
 WHERE name = 'Default Trace File';

---

3. Concurrent Program Trace 켜기

배치나 리포트가 느릴 때 사용합니다. 방법은 두 가지입니다.

3-1. 프로그램 정의에서 Enable Trace 체크

1. System Administrator > Concurrent > Program > Define
2. 대상 프로그램 조회
3. Enable Trace 체크박스 선택 후 저장
4. 해당 요청 실행
5. 작업이 끝나면 반드시 체크 해제

체크를 풀지 않으면 이후 모든 실행이 trace를 남기면서 서버 디스크를 채웁니다. 실제 운영 사고로 이어지는 대표적인 실수입니다.

3-2. 요청 ID로 trace 파일 찾기

Concurrent Program의 trace 파일은 DB 서버에 생성됩니다. 요청 ID만 알면 아래 쿼리로 파일명을 정확히 특정할 수 있습니다.

sql

SELECT 'Request ID  : ' || req.request_id                    AS info FROM DUAL UNION ALL
SELECT 'Trace ID    : ' || req.oracle_process_id             FROM fnd_concurrent_requests req WHERE req.request_id = &request_id
UNION ALL
SELECT 'Trace Flag  : ' || req.enable_trace
  FROM fnd_concurrent_requests req WHERE req.request_id = &request_id;

전체 정보를 한 번에 보려면 아래 쿼리가 편합니다.

sql

SELECT req.request_id,
       req.oracle_process_id,
       req.enable_trace,
       prog.user_concurrent_program_name AS program_name,
       dest.value || '/' || LOWER(dbnm.value) || '_ora_'
                  || req.oracle_process_id || '.trc' AS trace_file_name,
       ses.sid,
       ses.serial#,
       ses.module
  FROM fnd_concurrent_requests      req,
       fnd_concurrent_programs_vl   prog,
       v$session                    ses,
       v$process                    proc,
       v$parameter                  dest,
       v$parameter                  dbnm
 WHERE req.request_id            = &request_id
   AND req.concurrent_program_id = prog.concurrent_program_id
   AND req.program_application_id = prog.application_id
   AND req.oracle_process_id     = proc.spid(+)
   AND proc.addr                 = ses.paddr(+)
   AND dest.name                 = 'user_dump_dest'
   AND dbnm.name                 = 'db_name';

oracle_process_id가 곧 OS 프로세스 ID(spid)이고, trace 파일명이 <dbname>_ora_<spid>.trc 형태로 만들어지기 때문에 가능한 방식입니다.

3-3. 이미 실행 중인 요청에 trace 걸기

프로그램을 다시 돌릴 수 없는 상황이라면 실행 중인 세션에 직접 걸 수 있습니다.

sql

-- 위 쿼리에서 얻은 sid, serial# 사용
BEGIN
  DBMS_MONITOR.SESSION_TRACE_ENABLE(
      session_id  => &sid,
      serial_num  => &serial,
      waits       => TRUE,
      binds       => TRUE);
END;
/

-- 종료
BEGIN
  DBMS_MONITOR.SESSION_TRACE_DISABLE(
      session_id => &sid,
      serial_num => &serial);
END;
/

---

4. OAF(Self-Service) 페이지 Trace 켜기

OAF 화면은 Forms와 달리 JDBC Connection Pool을 쓰기 때문에 세션 단위 trace가 까다롭습니다. 사용자 한 명의 동작이 여러 DB 세션에 흩어질 수 있습니다.

4-1. FND: Diagnostics 활용

1. 프로파일 FND: Diagnostics를 Yes로 설정 (User 레벨 권장)
2. OAF 페이지 하단에 About this Page 링크가 생김
3. About this Page 화면의 진단 탭에서 SQL Trace 옵션 선택 가능

버전에 따라 메뉴 위치가 다르므로 없다면 다음 방법을 씁니다.

4-2. Initialization SQL Statement – Custom (가장 확실한 방법)

이 프로파일에 등록한 SQL은 해당 사용자가 만드는 모든 DB 세션 시작 시점에 실행됩니다. OAF든 Forms든 상관없이 걸립니다.

프로파일: Initialization SQL Statement - Custom (내부명 FND_INIT_SQL) 레벨: 반드시 User 레벨로, 테스트 대상 사용자에게만 설정

sql

BEGIN
  FND_CTL.FND_SESS_CTL('','','','TRUE','',
    'ALTER SESSION SET tracefile_identifier=''PUNES_OAF''
       EVENTS=''10046 TRACE NAME CONTEXT FOREVER, LEVEL 12''');
END;

작은따옴표가 두 번씩 들어가는 점에 주의하세요. 값을 저장한 뒤 해당 사용자가 로그아웃 후 재로그인해야 적용됩니다.

tracefile_identifier를 지정하면 파일명이 <dbname>_ora_<spid>_PUNES_OAF.trc 형태가 되어 수많은 trace 파일 중에서 즉시 찾을 수 있습니다. 이 옵션은 꼭 쓰시길 권합니다.

작업이 끝나면 프로파일 값을 반드시 비워야 합니다. 지우지 않으면 그 사용자의 모든 세션이 계속 trace를 남깁니다.

4-3. 여러 trace 파일 합치기 (trcsess)

Connection Pool 때문에 trace가 여러 파일로 쪼개졌다면 trcsess로 병합합니다.

bash

cd $ORACLE_BASE/diag/rdbms/<dbname>/<instance>/trace

# tracefile_identifier로 필터링된 파일들을 병합
trcsess output=merged_oaf.trc clientid='PUNES_TEST' *.trc

# 또는 module 기준
trcsess output=merged_oaf.trc module='XXPN_MEAL_ORDER' *.trc

tkprof merged_oaf.trc merged_oaf.txt sys=no sort=fchela

clientid 기준으로 묶으려면 애플리케이션 코드에서 미리 식별자를 설정해두면 좋습니다.

sql

DBMS_SESSION.SET_IDENTIFIER('PUNES_TEST');

4-4. SQL Trace vs FND Log (헷갈리기 쉬움)

OAF 디버깅에는 성격이 다른 두 가지가 있습니다.

구분	SQL Trace (10046)	FND Debug Log
기록 대상	DB에서 실행된 SQL과 대기	코드에서 FND_LOG.STRING으로 남긴 메시지
저장 위치	DB 서버 trace 파일	FND_LOG_MESSAGES 테이블
용도	성능 분석	로직 흐름 추적

FND Log를 켜려면 아래 프로파일을 설정합니다.

• FND: Debug Log Enabled = Yes
• FND: Debug Log Level = Statement (가장 상세)
• FND: Debug Log Module = % 또는 xxpn%

조회 쿼리:

sql

SELECT log_sequence, timestamp, module, log_level, message_text
  FROM fnd_log_messages
 WHERE user_id = &user_id
   AND timestamp > SYSDATE - 1/24
 ORDER BY log_sequence;

성능이 문제면 SQL Trace, 로직이 문제면 FND Log입니다. 둘을 섞어 쓰면 원인을 못 찾습니다.

---

5. PL/SQL에서 직접 Trace 켜기

패키지나 프로시저를 SQL*Plus에서 단독으로 테스트할 때 씁니다.

sql

-- 파일 식별자 지정 (필수는 아니지만 강력 권장)
ALTER SESSION SET tracefile_identifier = 'PUNES_20260908';

-- Trace 시작
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

-- 또는 패키지 방식
EXEC DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => TRUE);

-- 테스트 대상 실행
EXEC XXPN_MEAL_ORDER_PKG.create_order(p_order_id => 12345);

-- Trace 종료
ALTER SESSION SET EVENTS '10046 trace name context off';
-- 또는
EXEC DBMS_SESSION.SESSION_TRACE_DISABLE;

DBMS_MONITOR를 쓰면 서비스/모듈/액션 단위로도 걸 수 있습니다. EBS는 Concurrent Program 실행 시 module을 자동으로 세팅하므로 유용합니다.

sql

BEGIN
  DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(
      service_name => 'EBSPROD',
      module_name  => 'XXPNMEALORD',
      waits        => TRUE,
      binds        => TRUE);
END;
/

---

6. Trace 파일 위치 찾기

11g 이후는 ADR(Automatic Diagnostic Repository) 구조를 씁니다.

sql

-- 현재 세션의 trace 파일 (가장 간단)
SELECT value FROM v$diag_info WHERE name = 'Default Trace File';

-- 디렉터리 전체
SELECT name, value FROM v$diag_info;

-- 구버전 파라미터
SHOW PARAMETER user_dump_dest;

일반적인 경로:

$ORACLE_BASE/diag/rdbms/<db_name>/<instance_name>/trace/

파일명 규칙:

<instance>_ora_<spid>.trc
<instance>_ora_<spid>_<tracefile_identifier>.trc

서버에서 최근 파일부터 찾으려면:

bash

cd $ORACLE_BASE/diag/rdbms/<dbname>/<instance>/trace
ls -lrt *.trc | tail -20

# 식별자로 찾기
ls -lrt *PUNES*.trc

---

7. tkprof로 분석하기

원본 .trc 파일은 사람이 읽기 어렵습니다. tkprof로 가공합니다.

bash

tkprof ebsprod_ora_12345_PUNES.trc output.txt \
       sys=no \
       explain=apps/<password> \
       sort=fchela,exeela,prsela

주요 옵션:

옵션	설명
sys=no	재귀 SQL(딕셔너리 조회) 제외 — 거의 항상 사용
sort=fchela	Fetch 경과시간 내림차순 정렬
sort=exeela	Execute 경과시간 순 (INSERT/UPDATE 분석 시)
explain=	실행계획 추가 (선택)
aggregate=no	동일 SQL을 합치지 않고 개별 표시
waits=yes	대기 이벤트 표시 (기본값 yes)

explain= 옵션은 분석 시점의 실행계획을 보여주므로 실제 실행 당시와 다를 수 있습니다. trace 파일에 직접 기록된 Row Source Operation이 더 신뢰할 만합니다.

---

8. tkprof 결과 해석하기

출력 예시입니다.

SELECT order_id, meal_code, qty
  FROM xxpn_meal_orders
 WHERE flight_date = :b1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      201     12.43      45.87      82150     985320          0        3000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      203     12.43      45.87      82150     985320          0        3000

Misses in library cache during parse: 1

Rows     Row Source Operation
-------  ---------------------------------------------------
   3000  TABLE ACCESS FULL XXPN_MEAL_ORDERS (cr=985320 pr=82150 time=45.8s)

Elapsed times include waiting on following events:
  Event waited on                    Times Waited   Max. Wait  Total Waited
  ------------------------------     ------------  ----------  ------------
  db file scattered read                     5121        0.09         33.21

각 컬럼의 의미

컬럼	의미	볼 포인트
cpu	CPU 사용 시간(초)	높으면 연산·정렬 과다
elapsed	실제 경과 시간(초)	elapsed − cpu = 대기 시간
disk	물리적 읽기 블록 수	높으면 디스크 I/O 병목
query	Consistent 모드 논리적 읽기	튜닝의 핵심 지표
current	Current 모드 읽기 (주로 DML)
rows	처리된 행 수

판단 기준

1) query 대비 rows 비율

위 예시는 3,000행을 얻는 데 985,320 블록을 읽었습니다. 행당 328블록입니다. 인덱스가 없거나 잘못 탄 전형적인 패턴입니다. 보통 행당 논리적 읽기가 수십을 넘으면 의심해봅니다.

2) elapsed − cpu

45.87 − 12.43 = 33.44초가 대기입니다. 아래 wait event 섹션에서 db file scattered read(Full Scan에 따른 다중 블록 읽기)가 33.21초를 차지하는 게 확인됩니다. 원인과 증상이 일치합니다.

3) Parse count와 library cache miss

Parse count가 Execute count와 비슷하게 크다면 하드 파싱이 반복되는 상황입니다. 바인드 변수를 쓰지 않고 리터럴을 붙여 SQL을 만드는 코드가 있는지 확인해야 합니다.

4) Row Source Operation

실제 실행 통계입니다. cr(consistent reads), pr(physical reads), time을 단계별로 보여주므로 실행계획의 어느 단계에서 시간을 쓰는지 정확히 짚을 수 있습니다.

자주 만나는 대기 이벤트

이벤트	의미	대응 방향
db file sequential read	단일 블록 읽기 (인덱스 접근)	정상이나 과다하면 인덱스 선택도 확인
db file scattered read	다중 블록 읽기 (Full Scan)	인덱스 추가 또는 조건 재검토
direct path read	병렬/대량 스캔	대량 처리면 정상
enq: TX - row lock contention	행 잠금 대기	커밋 주기, 동시성 로직 점검
log file sync	커밋 완료 대기	루프 안 커밋 여부 확인
SQL*Net message from client	클라이언트 응답 대기	DB 문제 아님 (애플리케이션 측)

마지막 항목이 특히 중요합니다. SQL*Net message from client가 압도적으로 크면 DB는 놀고 있었고 병목은 미들티어나 네트워크에 있다는 뜻입니다. 이걸 모르면 엉뚱한 SQL을 튜닝하게 됩니다.

---

9. Trace 없이 빠르게 확인하는 방법

Trace는 파일 접근 권한이 필요해서 번거로울 때가 있습니다. 대안입니다.

9-1. 실제 실행 통계 바로 보기

sql

SELECT /*+ gather_plan_statistics */ ...
  FROM xxpn_meal_orders
 WHERE flight_date = :b1;

SELECT * FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

E-Rows(예상)와 A-Rows(실제)를 비교할 수 있어서 옵티마이저의 오판을 바로 확인할 수 있습니다.

9-2. Real-Time SQL Monitoring

sql

SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(
         sql_id => 'abc123xyz',
         type   => 'HTML') FROM DUAL;

5초 이상 걸리거나 병렬로 수행된 SQL이 자동 수집됩니다. 다만 Tuning Pack 라이선스가 필요합니다.

9-3. AWR / ASH

Diagnostic Pack 라이선스가 필요합니다. 라이선스 없이 조회하면 감사 대상이 될 수 있으니 도입 전 반드시 확인하세요.

---

10. 실무 체크리스트와 주의사항

켜기 전에

• 운영계라면 반드시 DBA와 사전 협의
• 대상 사용자/프로그램을 최소 범위로 한정 (User 레벨 프로파일 사용)
• tracefile_identifier 지정
• max_dump_file_size 확인 (UNLIMITED면 디스크 여유 확인)

sql

SHOW PARAMETER max_dump_file_size;

끄는 것을 잊지 말 것

가장 흔한 사고가 trace를 켜둔 채 잊는 것입니다. 대량 배치가 Level 12로 며칠 돌면 수십 GB가 쌓이고, diag 영역이 가득 차면 DB 자체가 영향을 받습니다.

• Concurrent Program의 Enable Trace 체크 해제
• Initialization SQL Statement – Custom 프로파일 값 삭제
• Forms는 No Trace 선택

성능 오버헤드

Level 12는 모든 바인드 값과 대기 이벤트를 파일에 기록하므로 실행 시간이 눈에 띄게 늘어납니다. 측정된 절대 시간보다는 어디에 시간이 몰렸는지 비율을 보는 게 맞습니다.

재현 조건 맞추기

trace는 실행된 것만 기록합니다. 느린 상황을 정확히 재현해야 의미 있는 데이터가 나옵니다. 개발계에서 데이터 10건으로 뜬 trace로는 운영계 100만 건의 문제를 알 수 없습니다.

---

마무리

정리하면 흐름은 항상 같습니다.

1. 어디가 느린지 범위를 좁힌다 (화면 / 배치 / OAF)
2. 해당 경로에 맞는 방법으로 Level 12 trace를 켠다
3. 문제 동작을 한 번 수행하고 즉시 끈다
4. tkprof로 가공한다
5. query 대비 rows, elapsed − cpu, 대기 이벤트 순으로 읽는다

이 순서만 지켜도 “느린 것 같다”는 막연한 보고가 “이 SQL이 인덱스를 안 타서 985,000 블록을 읽고 있습니다”라는 구체적인 원인으로 바뀝니다. 그때부터가 진짜 튜닝의 시작입니다.

---

참고

• Oracle Support Doc ID 296559.1 — FAQ: Common Tracing Techniques in Oracle E-Business Suite
• Oracle Support Doc ID 179848.1 — Tracing Concurrent Requests
• Oracle Database Performance Tuning Guide — SQL Trace and TKPROF