RAC SQL Trace

#핵심 요약

SQL Trace는 세션이 실행한 SQL, 실행 통계, 대기 정보를 Trace 파일에 남김
특정 세션이나 업무의 성능 저하 원인을 볼 때 씀

10046 이벤트 Level 12는 SQL 실행 정보에 대기 이벤트와 바인드 변수 정보까지 함께 모음

실습 Trace 파일은 방대하므로 TKPROF로 SQL별 실행 횟수, CPU 시간, 경과 시간, 논리, 물리 읽기를 정리해 튜닝 대상과 우선순위를 정함

RAC에서는 Trace 파일이 여러 인스턴스와 서버 프로세스에 흩어짐
tracefile_identifier로 파일 이름에 표시를 붙여 찾고, 서비스 단위 Trace는 trcsess로 하나로 모음

Trace는 문제 재현 구간만 짧게 수집하고 끝나면 바로 끔
오래 켜 두면 파일과 I/O, 관리 부담이 늘어남

#개념과 동작 원리

실습 업무 예: 주문 서비스는 1번 노드, 배송 서비스는 2번 노드에서 돌게 나눠 인터커넥트 부담을 줄인 환경에서 주문 서비스만 느리면, 주문 관련 SQL을 서비스 단위로 모아 시간 소비 순으로 비교하고 가장 느린 SQL부터 튜닝함
서비스는 장애나 재배치로 실행 위치가 바뀔 수 있으므로 srvctl status service, GV$SERVICES로 위치와 Trace가 생기는 인스턴스를 함께 확인함
RAC 서비스 구성은 고가용성과 재해복구 분야에서 다룸

트레이스 수집 대상인 RAC 서비스의 구성과 배치는 서비스와 로드 밸런싱에서 다룸

#필수 명령어 또는 SQL

#세션 단위 Trace

ALTER SESSION SET SQL_TRACE = true;
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
ALTER SESSION SET tracefile_identifier = mytrace;

SELECT ename, sal, job FROM emp WHERE deptno = 20;
SELECT empno, ename, sal FROM emp WHERE ename = 'ALLEN';

ALTER SESSION SET SQL_TRACE = false;
ALTER SESSION SET EVENTS '10046 trace name context off';

결과 해석:

실습에서는 SHOW PARAMETER user_dump_dest로 Trace 위치를 확인하고, 해당 인스턴스의 trace 디렉터리에서 MYTRACE가 붙은 파일(<INSTANCE>_ora_<PID>_MYTRACE.trc 형태)을 찾았음
실습 목록의 파일 번호와 분석 명령의 파일 번호가 서로 다름(확인 필요)

#서비스 단위 Trace

srvctl add service -d <DB_NAME> -s <INSTANCE_NAME> -r <INSTANCE1> -a <INSTANCE2>
srvctl start service -d <DB_NAME> -s <INSTANCE_NAME>
srvctl status service -d <DB_NAME> -s <INSTANCE_NAME>

결과 해석:

실습에서는 tnsnames.ora에 SCAN 주소와 SERVICE_NAME = <INSTANCE_NAME>, TAF(FAILOVER_MODE) 설정을 넣은 별칭을 만들고 tnsping으로 확인한 뒤, 실습 계정 세션 두 개를 그 별칭으로 접속했음

EXEC DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE('<INSTANCE_NAME>');
-- 각 실습 세션에서 업무 SQL 실행
EXEC DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE('<INSTANCE_NAME>');

SELECT value FROM v$diag_info;

trcsess output=<INSTANCE_NAME>.trc service=<INSTANCE_NAME>

결과 해석: <INSTANCE_NAME> 서비스로 생긴 여러 Trace 파일이 <INSTANCE_NAME>.trc 하나로 합쳐짐
실습에서는 합친 파일을 TKPROF로 분석하고, 경과 시간이 큰 SQL을 위로 올리려고 sort=exeela 옵션을 썼음
여러 인스턴스에 흩어진 파일을 trcsess가 어떻게 모으는지(디렉터리 범위)는 기재 없음

#TKPROF 인자(확인 필요 사항)

실습 명령은 tkprof <계정>/<비밀번호> sys=no trace=<파일> output=<결과 파일> sort=exeela 형태였음
기본 형식은 tkprof 입력파일 출력파일 [옵션]임
실습의 계정 인자와 trace= 표기는 기본 형식에 맞지 않아 실행 전 확인이 필요함