SQL 트레이스

• 실행계획과 Autotrace만으로 부하의 원인을 찾기 어려울 때 내부 수행 절차상
  어느 단계에서 부하를 일으키는 지 확인 가능한 도구이다.
• 버전에 상관없이 오래 전부터 사용하던 Oradebug 명령어가 존재하며 'oradebug help'를 입력하면 사용 방법을 알 수 있다.

(1) 자기 세션에 트레이스 걸기

순서

1. 다음과 같이 설정하고 SQL을 수행 ::

SQL> alter session set sql_trace = true;

2.user_dump_dest에 지정된 디렉토리 밑에 트레이스(.trc) 파일이 생성
3. 가장 최근에 수정되거나 생성 된 파일을 찾아 분석한다.

• • 파일 찾기 어려울 경우 아래 스크립트를 이용해 현재 트레이스 파일을 쉽게 찾을 수 있음

select r.value || '/' || lower(t.instance_name) || '_ora_' || ltrim(to_char(p.spid)) || '.trc' trace_file
	from   v$process p, v$session s, v$parameter r, v$instance t
	where  p.addr = s.paddr
	and    r.name = 'user_dump_dest'
	and    s.sid = (select sid from v$mystat where rownum = 1) ;

TRACE_FILE
---------------------------------------------------------
e:\app\yooriver\diag\rdbms\orcl\orcl\trace/orcl_ora_180.trc

>> 아래의 명령어 사용 시 식별자가 붙게 되므로 쉽게 찾을 수 있다.
SQL> alter session set tracefile_identifier ='yooriver'

>> orcl_ora_180_yooriver.trc

(2) 다른 세션에 트레이스 걸기

1. 오라클의 다양한 종류의 이벤트 트레이스

• 오라클은 다양한 종류의 이벤트 트레이스를 제공한다.
• 이벤트 트레이스에서 설정할 수 있는 레벨 값은 1,4,8,12로 설정 할 수 있다.
  (레벨4 이상으로 설정할 경우 파일의 크기가 급격하게 커질 수 있으므로 주의한다.)
  • 레벨1 : Default
  • 레벨4 : Bind Values
  • 레벨8 : 트레이스 레벨을 8로 설정 하면 이벤트 발생 현황까지 확인 가능하다.
    • wait event (이벤트 발생 현황 종류)
      • SQL*Net message to client : Client에게 메세지 송신후 Client로 부터 메세지 수신 완료 신호가 정해진 시간보다 늦게 도착한 경우 발생한다.
      • SQL*Net message from client : 오라클 서버 프로세스가 사용자에게 결과를 전달하고 다음 Fetch Call이 올때 까지 대기한 시간을 더한 값이다.
      • SQL*Net more data from client : Client에게 전송할 데이터가 남았는데 네트워크 부하로 전송하지 못할 때 발생하는 대기 이벤트이다.
      • db file sequential read : Single Block I/O 시 발생한다.
  • 레벨12 : Bind Values & Waits

[예시 1]

(오라클 9i) 특정 세션에서 심한 성능 부하를 일으키고 있는 상황에서 Serial 번호가 3번인 145번 세션에 레벨 12로 10046 트레이스를 수집하는 방법

• 순서

1. Serial 번호가 3번인 145번 세션에 레벨 12로 10046 트레이스 설정

exec dbms_system.set_ev(145,3,10046,12,'');

2. 트레이스 해제시에는 레벨을 0으로 변경한다.

[예시 2]

(오라클 10g) dbms_monitor 패키지를 사용

• 순서

1. 트레이스 설정시 session_trace_enable을 실행한다.

dbms_monitor.session_trace_enable(
	session_id => 145,
    serial_num => 3,
    waits      => true,
    binds      => true);
   end;

2. 트레이스 해제 시 session_trace_disable 을 실행한다.

   dbms_monitor.session_trace_disable(
      session_id => 145,
      serial_num => 3);
  end;   

(3) 시스템 전체 트레이스를 설정하기

⭐️⭐️ 심각한 부하를 일으키므로 부득이한 경우를 제외하고는 사용 해서는 안된다!! ⭐️⭐️

• 사용법

-- 설정
SQL> alter system set sql_trace = true;
-- 해제
SQL> alter system set sql_trace = false;

(4) Service, Module, Action 단위로 트레이스 걸기

• 10g 부터 Service, Module, Action 별로 트레이스를 설정 및 해제 가능한 dbms_monitor 패키지가 존재한다.
• 현재 접속해있는 세션 뿐만 아니라 새로 커넥션을 맺는 세션도 자동으로 트레이스가 설정한다.
• v$session을 통해 Service, Module, Action을 확인할 수 있다.
• Action은 다음을 통해서 설정 변경이 가능하다.

dbms_application_info.set_action('action_name')

• 트레이스 설정 확인은 dba_enable_traces 뷰를 통해 확인 가능하다.
• dbms_monitor.serv_mod_act_trace 패키지
  • Service, Module, Action 단위의 v$sesstat통계 정보 수집이 가능하다.
  • v$serv_mod_stats뷰를 통해 수행통계를 확인할 수 있다.

순서

1. 트레이스 설정

begin
dbms_monitor.serv_mod_act_trace_enable (
	service_name => 'eCRM'  -- 대소문자 구분함
	,module_name  => dbms_monitor.all_module
	,action_name  => dbms_monitor.all_actions
	,waits        => true
	,binds        => true );
end;

2. 트레이스 해제

begin
dbms_monitor.serv_mod_act_trace_disable (
	service_name => 'eCRM'
	,module_name  => dbms_monitor.all_module
	,action_name  => dbms_monitor.all_actions);
end; 

(5) 트레이스 주요 항목 및 Tkprof

5-1 ) 순서

1. 출력할 트레이스 파일 지정하기
   C:\Documents and Settings\yooha > tkprof ora_trace.trc report.prf sys=no

   • C:\Documents and Settings\yooha 는 trc 파일이 위치한 디렉토리이다.
   • tkprof 는 트레이스 파일을 보기 쉽게 레포트화 시켜주는 명령어이다.

2. 아래와 같은 양식으로 출력된다.

================================================================================
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        2      0.00       0.00          0          8          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          8          0           1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 93

Rows     Row Source Operation
-------  ---------------------------------------------------
1         TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0 time=0 us cost=3 size=87 card=1)
================================================================================

5-2) Call "통계" 컬럼 의미

• call : 커서 상태에 따라 Parse, Execute, Fetch 세 개의 Call로 나누어 각각에 대한 통계정보를 보여준다.
  • Parse : 커서를 파싱하고 실행계획을 생성하는 데 대한 통계이다.
  • Execute : 커서의 실행 단계에 대한 통계이다.
  • Fetch : 레코드를 실제로 Fetch하는 데 대한 통계이다.
• count :: Parse, Execute, Fetch 각 단계가 수행된 횟수이다.
• cpu :: 현재 커서가 각 단계에서 사용한 cpu time이다.
• elapsed :: 현재 커서가 각 단계를 수행하는 데 소요된 시간이다. (⭐️ Call 단위로 측정한다.)

  ✅ Elapsed Time (⭐️ 중요!! )

  Elapsed Time = CPU Time + Wait Time = Response시점 - Call 시점

  • 하나의 SQL을 수행할 때 Total Elapsed Time은 수행 시 발생하는 모든 Call의 Elapsed Time을 더해서 구한다.
  • Call 단위로 측정한다.

• disk : 디스크로부터 읽은 블록 수
• query : Consistent 모드에서 읽은 버퍼 블록 수
• current : Current모드에서 읽은 버퍼 블록수
• rows :: 각 단계에서 읽거나 갱신한 처리건수

5-3) ⭐️ Auto Trace의 실행통계 항목과 Call통계 컬럼 비교 ⭐️

• db block gets = current
• consistent gets = query
• physical reads = disk
• SQL*Net roundtrips to/from client = fetch count
• rows processed = fetch rows

5-4) ⭐️ call 발생 횟수 ⭐️

• SELECT문 사용시 : 3번의 Call이 발생
  • SELECT문 = Parse Call + Execute Call + Fetch Call
• DML문 사용시 : 2번의 Call이 발생
  • DML문 = Parse Call + Execute Call