[튜닝] SQL Trace

◆ SQL Trace란?
  - 모든 SQL 수행에 대한 Trace파일 생성
  - 인스턴스 레벨과 세션 레벨로 추적 가능. 하지만 인스턴스 레벨은 서버에 도는 것들을 전부 추적하므로 정상이든 비정상이든 부하가 굉장히 많이 걸리기 때문에 세션 레벨에서 추적함.
  - 구문분석, 실행, 인출 단계에서 어느 부분에서 시간이 오래 걸리는지에 대한 크기 및 시간 통계 정보도 표시
  - 파라미터 파일의 user_dump_dest에 생성됨


◆ Autotrace와의 차이점
  - Autotrace는 시간 정보는 나오지 않지만 SQL Trace는 각 단계(Parse, Execute, Fetch)에서의 시간 통계 정보도 나옴
  - Autotrace는 결과를 바로 보여주지만, SQL Trace는 결과를 특정 파일에 저장해둠.
  - SQL Trace는 사용자가 자신의 쿼리도 추적하지만, 다른 사용자의 쿼리 또한 추적 가능함.


◆ SQL Trace 기능 활성화

◇ 인스턴스 레벨
  - 파라미터 파일에서 아래의 내용 추가 후 DB재시작

SQL_TRACE=true

◇ 세션 레벨(현재 접속 중인 세션)

SQL> conn / as sysdba;
SQL> grant alter session to scott;
SQL> conn scott/tiger;
SQL> alter session set sql_trace=true;           // 이후 추적을 그만 두고 싶으면 false
SQL> alter session set timed_statistics=true;

이후 날리는 모든 쿼리들의 trace 결과가 udump 아래에 저장됨.

◇ 세션 레벨(임의의 세션 추적)
  - DBA만 가능

SQL> conn / as sysdba;
SQL> exec dbms_session.set_sql_trace(true);
SQL> select sid, serial#, username, machine
   from v$session;          // 현재 해당 서버에 접속해 있는 사용자들의 정보가 조회됨.
SQL> exec dbms_system.set_sql_trace_in_session (찾은sid, 찾은serial#, true);

이후 추적하고자 하는 세션에서 날리는 모든 쿼리들의 trace 결과가 udump 아래에 저장됨.

◆ SQL Trace 리포트 생성
  - user_dump_dest에서 해당 trace의 파일 확인
  - tkprof를 통해 리포트 생성

$ tkprof 원본파일 변환후파일 explain=scott/tiger sys=no

explain : 실행계획을 뜬 사용자 계정과 암호
sys : recursive sql 등 복잡한 정보들은 빼고 보여줌.


◆ 단계별 통계치 추출
  ◇ Parse
    - SQL문을 실행계획으로 변환하는 과정에서 발생하는 통계치
  ◇ Execute
    - 명령문을 실행하면서 발생하는 통계치
    - DML(Insert, Delete, Update)에 관련된 정보
  ◇ Fetch
    - 인출 시에 발생하는 통계치
    - Select에 관계된 정보


◆ TKPROF 통계정보 설명
  - 구문분석, 실행, 인출 단계별로 통계정보 표시

통계정보	설명
Count	각 처리 단계별 실행된 수
CPU	각 처리 단계별 CPU 소모시간(단위:초)
Elapsed	각 처리 단계의 시작에서 종료까지 총 경과된 시간(단위:초)
Disk	각 처리 단계별 물리적인 디스크 블록 접근 횟수
Query	각 처리 단계별 읽은 변겨오딘 버퍼 블록 수(Consistent Read)  읽기 일관성 관련 / 주로 Select문
Current	각 처리 단계별 현 세션에만 유효한 버퍼 블록을 접근한 수(Current Read)  주로 DML(Insert, Delete, Update) 작업시 발생
Rows	각 처리 단계별 읽은 총 행수  Fetch단계 : select에 의해 질의된 행 수  Execute단계 : DML문에서 처리된 행 수

좀 더 쉽게 말해서 은행으로 따지면, elapsed는 은행으로 출발하여 돈 찾고 다시 돌아오는동안 걸린 총 시간을 뜻하고, cpu는 순수하게 돈 찾는데만 걸린 시간을 뜻함.
cpu와 elapsed의 차이가 많이 나면 안좋은 것이다. 대략 2배 이상 차이나면 대기시간이 많은 것이다.
disk, query, current는 블록을 읽은 갯수를 의미함.
이 중 query, current는 DB캐시의 블록을 얘기하는 것인데, query는 CR블록에서 몇 개의 블록을 끌어왔는가에 대한 것이고, current는 자기가 DB캐시에서 변경한 블록을 의미한다.
rows는 데이터를 꺼낸 갯수임.

cpu > elapsed라면 오류이다. 원래 cpu <= elapsed이어야 하는데, tkprof는 여러 개의 결과를 sum해주므로 그 과정에서 위 오류가 나올 수 있지만 크게 신경쓰지 않아도 된다.


◇ TKPROF 예제

********************************************************************************

select *
from
 dept where deptno = 10


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.01          1          2          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.01          1          2          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS // 옵티마이저 모드가 CBO임을 나타냄
Parsing user id: 57  (SCOTT) // scott 사용자가 수행한 쿼리

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      0   TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (TABLE)
      0    INDEX (UNIQUE SCAN) OF 'PK_DEPT' (INDEX (UNIQUE))


3개의 블록을 가져와서 1건을 뽑아냄.
execute 단계에서 disk, query, current 값이 있다면 DML임.
parse, fetch 단계에서 disk, query, current 값이 있다면 select임.

********************************************************************************

◆ V$SQLAREA
악성쿼리들을 조회 가능한 동적뷰. 토드나 오렌지로 봐야 보기 쉽게 조회 가능.
  - SQL문의 Parse 정보
  - Execute 정보
  - SQL문이 재사용되지 못한 횟수
  - SQL문이 실행되면서 내부적으로 발생한 Sorting 횟수
  - 개발자들이 작성한 SQL문장 중에서 가장 많은 메모리 공간을 사용한 SQL문 분석
  - 가장 많은 디스크 I/O를 유발시킨 SQL문 분석