SQL 트레이스

• SQL 엔진은 Parse, Execute, Fetch 를 실행
  • CPU를 사용하여 특정 처리 수행
  • 다른 자원을 사용 (예: 디스크)
  • 동기화 지점 거침 (예: 래치)

• SQL 트레이스 목적
  • 응답시간의 서비스/대기 시간 분리
  • 사용된 자원, 동기화 작업의 상세 정보 제공

• TKPROF 로 추출할 수 있는 정보
  • SQL 텍스트, 일부 실행 통계, 처리시 발생한 대기 이벤트, 파싱 단계 정보, 실행 계획
  • 실행 SQL 및 재귀 SQL 모두 정보 제공

• SQL 트레이스는 10046 디버깅 이벤트 기반

• 레벨?
  • 레벨 4 부터 확장 SQL 트레이스
  • 레벨 조합 가능 : 레벨12 = 레벨4 + 레벨8

SQL 트레이스 활성화: ALTER SESSION 구문

-- LEVEL 1
ALTER SESSION SET sql_trace = TRUE

-- LEVEL 12
ALTER SESSION SET events '10046 trace name context forever, level 12'

-- 비활성화
ALTER SESSION SET events '10046 trace name context off'

-- ALTER SYSTEM 도 가능 : 큰 오버헤드, 명령 이후 생성된 세션만 동작

SQL 트레이스 활성화: DBMS_MONITOR 패키지

• 세션 속성(식별자, 서비스명, 모듈명, 액션명)에 따라 SQL 트레이스 적용 가능
• 커넥션 풀 환경에서 유용

세션 레벨

-- session_id, serial_num 미 지정시 현재 세션 적용
dbms_monitor.session_trace_enable(session_id => 127, serial_num => 29, waits => TRUE, binds => FALSE, plan_stat => 'first_execution');
dbms_monitor.session_trace_disable(session_id => 127, serial_num => 29);

• SQL 트레이스 활성화 확인 : v$session.(sql_trace, sql_trace_waits, sql_trace_binds, sql_trace_plan_stats)
  • 적어도 하나의 SQL 구문 실행 후 값 제공
• RAC 에서는 타겟 세션이 위치한 인스턴스에서 시행

클라이언트 레벨

-- v$session.client_identifier 가 "helicon.antognini.ch' 설정 된 모든 세션 적용 (RAC 에서 모든 인스턴스 대상, DB 재시작 시 설정 지속)
-- 대상 세션 조회 : dba_enabled_traces
dbms_monitor.client_id_trace_enable(client_id => 'helicon.antognini.ch', waits => TRUE, binds => TRUE, plan_stat => 'first_execution');
dbms_monitor.client_id_trace_disable(client_id => 'helicon.antognini.ch');

컴포넌트 레벨

-- 기본값 없음 : service_name
-- 기본값 있음 : module_name (any), action_name (any), instance_name (any)
dbms_monitor.serv_mod_act_trace_enable(service_name => 'DBM11203.antognini.ch', module_name => 'mymodule', action_name => 'myaction', waits => TRUE, binds => TRUE, plan_stat => 'all_execution');
dbms_monitor.serv_mod_act_trace_disable(service_name => 'DBM11203.antognini.ch', module_name => 'mymodule', action_name => 'myaction');

-- dba_enabled_traces.trace_type  : SERVICE, SERVICE_MODULE, SERVICE_MODULE_ACTION

데이터베이스 레벨

-- 모든 파라미터 기본 값 있음, 다른 레벨에서 enable 한 세션은 영향 못줌
dbms_monitor.database_trace_enable(waits => TRUE, binds => TRUE, instance_name => 'DBM11203', plan_stat => 'first_execution');
dbms_monitor.database_trace_disable(instance_name => 'DBM11203');

SQL 트레이스 활성화: DBMS_SESSION 패키지

-- 현재 세션 전용
dbms_session.session_trace_enable(waits => TRUE, binds => TRUE, plan_stat => 'all_executions');
dbms_session.session_trace_disable;

트리거에서 SQL 트레이스 활성화 하기

CREATE ROLE sql_trace;

CREATE OR REPLACE TRIGGER enable_sql_trace AFTER LOGON DATABASE
BEGIN
  IF (dbms_session.is_role_enabled('SQL_TRACE'))
  THEN
    EXECUTE IMMEDIATE 'ALTER SESSION SET timed_statistics = TRUE';
	EXECUTE IMMEDIATE 'ALTER SESSION SET max_dump_file_size = unlimited';
	dbms_monitor.session_trace_enable;
  END IF;
END;

-- ALTER SESSION, sql_trace 권한 부여
-- 대안 : AP 에서 파라미터에 따라 SQL 트레이스 직접 수행

트레이스 파일에 시간 정보 포함시키기

• timed_statistics = TRUE
  • 경과시간(elapsed_time) 및 CPU시간(cpu_time) 관찰 가능
• 설정 방법

-- statistics_level = basic 이면 FALSE typical/all 이면 TRUE
ALTER SESSION SET timed_statistics = TRUE;

트레이스 파일의 크기 제한하기

-- K, M 설정 가능, 초과 시 ORA-48913 발생
ALTER SESSION SET max_dump_file_size = 'unlimited';

트레이스 파일 찾기

• 10.2 버전
  • user_dump_dest : dedicated 서버 프로세스
  • background_dump_dest : background 서버 프로세스 (v$bgprocess 에 모두 나타나지 않음)

• 11.1 버전 - ADR(Automatic Diagnostic Repository) 도입
  • diagnostic_dest + v$diag_info
  • v$process.tracefile

• 트레이스 파일 이름
  • (instance_name)(process name)(process id)_(tracefile identifier).trc
    • (process name) : 프로세스 소문자 이름(v$shared_server.name, v$bgprocess.name), dedicated 서버 프로세스는 ora 적용
    • (process id) : 시스템 레벨 PID (v$process.spid, 윈도우는 스레드 ID)
    • (tracefile identifier) : 옵션, 최대 255자 식별자, 세션 레벨 파라미터 (v$process.traceid)