SQL 트레이스
SQL 트레이스란
- SQL을 튜닝할 때 가장 많이 사용되는 도구이며, 사전 실행계획과 AutoTrace 결과만으로 부하원인을 찾을 수 없을 때 SQL 트레이스를 통해 쉽게 찾아낼 수 있다.
1. 자기 세션에 트레이스 걸기
현재 자신이 접속해 있는 세션에만 트레이스를 설정하는 방법 - 세션레벨
Event 비활성화
- SQL_TRACE = TRUE로 실행했을 경우
SQL> ALTER SESSION SET SQL_TRACE = FALSE;
- LEVEL 지정하여 활성화 했을 경우
SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';
위와 같이 트레이스를 설정하고 SQL을 수행한 후에는 user_dump_dest 파라미터로 지정된 서버 디렉토리 밑에 트레이스 파일(.trc)이 생성된다.
TKProf 유틸리티를 사용하면 트레이스 파일을 보기 쉽게 포맷팅 해준다.
sys=no 옵션은 SQL을 파싱하는 과정에서 내부적으로 수행되는 SQL문장을 제외 시켜준다.
NON-RECURSIVE > User Call
- User Call은 OCI(Oracle Call Interface)를 통해 오라클 외부로부터 들어오는 Call을 말한다.
- Peak 시간대에 시스템 장애를 발생시키는 가장 큰 주범은 User Call이다
- User Call이 많이 발생되도록 개발된 애플리케이션은 결코 좋은 성능을 낼 수 없다.
> DBMS 성능과 확장성을 높이려면 User Call을 최소화 하려는 것이 중요하다.
RECURSIVE > Recursive Call
- Recursive Call은 오라클 내부에서 발생하는 Call을 말한다.
- SQL파싱과 최적화 과정에서 발생하는 Data Dictionary 조회, PL/SQL로 작성된 사용자 정의 함수/프로시저/트리거 내에서의 SQL 수행이 여기에 해당된다.
- Recursive Call을 최소화 하려면, 바인드 변수를 적극적으로 사용해 하드파싱 횟수를 줄여야 한다.
- PL/SQL로 작성한 프로그램을 이해하고 시기 적절하게 사용한다.
Call 통계 컬럼들의 의미
| 항목 | 설명 |
| Call | 커서 상태에 따라 Parse, Execute, Fetch 세 개의 Call로 나누어 각각에 대한 통계정보를 보여준다. ● Parse : 커서를 파싱하고 실행계획을 생성하는 데 대한 통계 ● Execute : 커서의 실행 단계에 대한 통계 ● Fetch : 레코드를 실제로 Fetch하는 데 대한 통계 |
| Count | Parse, Execute, Fetch 각 단계가 수행된 횟수 |
| Cpu | 현재 커서가 각 단계에서 사용한 cpu line |
| Elapsed | 현재 커서가 각 단계를 수행하는 데 소요한 시간 |
| Disk | 디스크로부터 읽은 블록 수 |
| Query | Consistent 모드에서 읽은 버퍼 블록 수 |
| Current | Current 모드에서 읽은 버퍼 블록 수 |
| rows | 각 단계에서 일걱나 갱신한 처리 건수 |
이벤트 트레이스를 이용하여 SQL 트레이스를 켜는 방법
"10046" event는 SQL Trace 생성 이벤트 입니다. 실행한 SQL에 대해서 실행 계획 및 SQL 실행에 따른 여러 Resource의 사용정보 및 실행에 사용한 Bind 정보, 실행과정에서 발생되는 Wait Event 정보를 기록합니다.
레벨 설정을 통해 바인드 변수와 대기 이벤트 발생 현황까지 수집할 수 있따. 설정할 수 있는 레벨의 값은 1, 4, 8, 12이며
레벨 1은 지금까지 살펴본 일반적인 SQL 트레이스와 같다.
- level 0 > SQL_TRACE=FALSE와 동일
- level 1 > SQL_TRACE=TRUE와 동일, 일반적인 Trace 정보 제공(default)
- level 4 > level 1 + bind 변수정보
- level 8 > level 1 + wait event
- level 12 > level 1 + bind 변수 + wait event 정보
SQL*Net message to/from client 이벤트
1. Client가 Server Process에 Data 요청을 한다.
2. Server Process는 Data를 Fetch한 후 OS에서 Network Data 전송을 요청하고 OS로부터 응답이 올 떄 까지 SQL*Net message to client 이벤트를 대기한다. OS는 Oracle로부터 받은 Data를 Send Buffer에 채우고 Oralce에게 전송 완료되었다는 응답을 보낸다. 이 때 SQL*Net message to client 이벤트에 대한 대기가 끝난다.
3. Server Process는 OS에게 Client로부터 전송된 Network Data를 요청하고 OS로부터 응답이 올 때까지 SQL*Net message from client 이벤트를 대기한다. OS는 Receive Buffer에 Client가 보낸 Data가 도착하면 Oracle이 전송이 시작되었다는 것을 알린다. 이때 SQL*Net message from client 이벤트에 대한 대기가 끝난다. Oracle은 전송받은 Client의 요청을 처리한다. 다시 2번으로 돌아간다.
SQL*Net으로 시작하는 모든 대기 이벤트들은 위와 같은 매커니즘으로 동작합니다. 즉 Oracle Server Process와 OS간의 Network API Call에서 걸린 시간이 곧 대기시간입니다. 만일 주고받은 Data의 크기가 커서 한번의 API Call로 처리가 안되면 여러 번 호출이 이루어지고 그 때는 "more"라는 수식어가 이벤트 명에 붙게됩니다.
break/reset 이벤트는 1) 통신을 Break하겠다. 2) 그리고 Reset하겠다라는 일종의 대화를 의미합니다. 가령 Query를 수행하다가