농부지기

☞ SQL TRACE

1. 정의 : SQL TRACE의 기능을 사용하면 개개의 SQL 문 실행시 실행과정의 및 결과등의 통계 정보를
          트레이스.파일에 출력한다. 이 결과를 모니터링하여 SQL튜닝을 실시해야 할 곳을 발견할
          수 있다.

2. 단점 : SQL TRACE를 하게 되면 속도가 느려 지므로 필요시에만  상태를 True로 하고 사용하지 않을
          경에는 False로 한다.   

■ 현재Session 만을 변경 
  SQL> alter session set timed_statistics=true;  --(CPU 시간을 알고 싶을 경우에 실행)
  SQL> alter session set sql_trace = true;
  SQL> select ... (SQL문 처리의 실행)
  SQL> alter session set sql_trace = false;
  SQL> exit

■ 임의Session을 변경
    SQL> alter session set timed_statistics=true; 
    SQL> EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid, serial#, TRUE); 
    SQL> select ... (처리의 실행)
    SQL> EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid, serial#, FALSE);

■ 인스턴스 전체를 변경
  init.ora ->
  timed_statistics=true
  sql_trace_true
  user_dump_dest ...

  RESTART INSTANCE

  여기서 초기화 파라미터 USER_DUMP_DEST(udump)에 트레이스·파일이 작성 된다.
  해당 트레이스·파일은 작성된 타임 스탬프로 확인한다.

  이 SQL 트레이스·파일은 SQL문 실행시에 순서대로 정보가 써지므로 이 파일을 보고 SQL문을
  분석하는 것은 곤란하다. 다음의 2.에서 소개하는 TKPROF라고 하는 유틸리티를 사용해 
  SQL 트레이스·파일을 정형하는 것으로 튜닝을 위한 분석을 실시하기 쉽게 한다.

2. TKPROF의 실행 방법
     - TKPROF로 SQL 트레이스를 포맷 해 보기 쉽게 한다

상기로 취득한 SQL 트레이스에 대해 TKPROF 커멘드를 실행하면 SQL 트레이스가  알기 쉽게 포맷 됩니다.

% tkprof <상기로 작성된 트레이스·파일> <임의의 파일명>
  (예： % tkprof oracle_ora_8138.trc tkprof.txt)

※ TKPROF 커멘드에는 많은 옵션이 있습니다.자세한 것은 Oracle8 or Oracle8i 「퍼포먼스를 위한 설계 및 튜닝」또는 Oracle9i  「데이타베이스·퍼포먼스·가이드 및 레퍼런스」의 "SQL 트레이스 기능과 TKPROF의 사용 방법" - 순서 3:TKPROF에 의한 트레이스·파일의 포맷을 참조.

3. 통계 정보를 해석한다
   - TKPROF 실행 후의 트레이스·파일의 대략적인 견해(처리에 걸린 시간) 

☞ SQL TRACE시 취득 정보

◆ Parse
   - SQL문이 파싱되는 단계에 대한 통계
   - 새로 파싱을 했거나, 공유 풀에서 찾아 온 것도 포함됨.
   - 단, PL/SQL 내에서 반복 수행(Loop)된 SQL이나 PL*SQL에서 보존 커서(Hold cursor)를 지정한
     경우에는 한 번만 파싱됨.

◆ Execute
   - SQL문의 실행단계에 대한 통계임.
   - UPDATE, INSERT, DELETE 문들은 여기에 수행한 결과가 나타나게 됨.
   - 전체범위 방식으로 처리된 결과가 여러 건인 경우는 주로 여기에 많은 값이 나타나며
     Fetch에는 아주 적은 값이 나타남.

◆ Fetch
   - SQL문이 실행되면서 Fetch된 통계임. 
   - 부분범위 방식으로 처리된 SELECT 문들이나 전체범위 처리를 한 후 한 건을 추출하는 경우        (AGGREGATE, 전체집계, COUNT 등)는 주로 여리게 많은 값들이 나타나고 EXECUTE에는 아주
     적은 값이 나타남.

◆ COUNT
   - SQL문이 파싱된 횟수, 실행된 횟수, FETCH가 수행된 횟수

◆ CPU
   - PARSE, EXECUTE, FETCH가 실제로 사용한 CPU 시간(1/100초 단위)

◆ ELAPSED
   - 작업의 시작에서 종료시까지 실제 소요된 총 시간

◆ DISK
   - 디스크에서 읽혀진 데이타 블록의 수

◆ QUERY
   - 메모리 내에서 변경되지 않은 블록을 읽거나 다른 세션에 의해 변경되었으나 아직 Commit되지
     않아 복사해 둔 스냅샷 블록을 읽은 블록의 수
   - SELECT 문에서는 거의가 여기에 해당하며 UPDATE, DELETE, INSERT시에는 소량만 발생됨.

◆ CURRENT
   - 현 세션에서 작업한 내용을 Commit하지 않아 오로지 자신에게만 유효한 블록(Dirty Block)을
     액세스한 블록 수
   - 주로 UPDATE, INSERT, DELETE 작업시 많이 발생
   - SELECT문에서는 거의 없으나 아주 적은 양인 경우가 대부분임.

◆ Logical I/O : QUERY + CURRENT

◆ ROWS
   - SQL문을 수행한 결과에 의해 최종적으로 액세스된 ROW의 수
    - 서브쿼리에 의해서 추출된 ROW는 제외됨.
   - 만약 SUM, AVG, MIN, COUNT 등의 그룹함수를 사용한 경우라면 큰 의미가 없음.

▣ 재귀적 콜(RECURSIVE CALL)이란：
   유저가 발행한 SQL문을 실행하기 위해서 Oracle는 추가의 문장을 발행해야만 하는 경우가 있다.
   이러한 문장을 재귀적 콜 또는 재귀적 SQL문 이라고 한다. 예를 들어 충분한 공간이 없는 테이블에
   행을 삽입하려고 하면, Oracle는 재귀적 콜을 실행해 동적으로 공간을 할당한다. SQL 트레이스
   기능이 사용 가능으로 되어 있을 때 재귀적 콜이 발생하면 TKPROF는 재귀적 콜의 원인이 된
   문장에 대해 재귀적 SQL문의 통계를 표시한다.

▣ 재귀적 콜의 분별법：
   TKPROF로 출력되고 있는 SQL문이 재귀적 SQL문인가 그렇지 않은가는 각 SQL문의 최후의 「
   recursive depth」정보로 판단한다. 이 값이 1이상의 경우, 그 SQL문은 재귀적 SQL문으로서의
   통계 정보가 된다.

▣ 「recursive depth」(이하, DEPTH)은, SQL 문이 발행된 재귀 레벨을 나타낸다. 예를 들어 값 0 은
    유저가 그 문장을 발행한 것을 나타내 보인다. 값 1은 Oracle이 값0의 문장(유저 발행의 문장)을
    처리하는 재귀적 콜로서 그 문장을 생성했다란 것을 나타낸다.값n 는 Oracle 가 그 문장을 값 n-1
    의 문장을 처리하는 재귀적 콜로서 생성한 것을 나타낸다.
    주의：내부표에 대한 처리만이 재귀적 SQL문이 되는 것은 아니다.

    PL/SQL 블록으로
      스토어드·프로그램(패키지, 프로시저, 팬쿠션등)을 부르는 처리를 실시했을 경우
      스토어드·프로그램 안에서 실행되는 SQL문은 SELECT나 UPDATE여도 재귀적 SQL문
                 으로 간주해져 재귀적 SQL문의 통계 정보로서 출력됩니다.

☞ ORANGE TOOL에서 SQL TRACE하기

1. SQL문을 KEY IN 후 처음 TRACE를 할 경우
    (아래 결과는  
       - Oracle이  Local PC인  windows XP에 설치 되었다.
       - TABLE FULLSCAN 이다
       - Record 갯수가  334개 결과가 나오는 SQL문장의 TRACE결과이다
     

2. SQL문을 KEY IN 후 두 번째 부터의 TRACE결과