"오라클 튜닝 sql trace"의 두 판 사이의 차이

1.3.1 SQL 트레이스 수행[편집]

-- 0.트레이스 식별자 지정
alter session set tracefile_identifier=’DBCAFE_TUN_DUMP’; ==> orcl_ora_10024_"DBCAFE_TUN_DUMP".trc 형태로 저장됨.

-- 1. sql trace 시작 
ALTER SESSION SET SQL_TRACE = TRUE;

-- 2. event 를 level 12 로 설정.   이렇게 설정하면, SQL trace 정보, 실행계획, 대기 이벤트 정보를 볼 수 있다.

ALTER SESSION SET events '10046 trace name context forever, level 12';
--        · 10046 = Query가 수행되는 사건. 즉 Query를 수행하는 "사건"을 의미.
--        · trace name = Trace를 수행. 사건이 발생했을 때의 행위(Action)을 의미. 
--        · context forever, level 12 = 사건이 발생할 때마다 행위를 수행하되, 행위의 레벨을 12로 

-- 3.sql 실행 
select * from tb_xxx;

-- 4. sql trace 종료
ALTER SESSION SET SQL_TRACE = FALSE;


-- 5. 10046 event 끄기 
-- 꺼주지 않으면, 수행하는 모든 SQL 에 대한 정보를 모은다.
ALTER SESSION SET events '10046 trace name context off';


-- 6. trace 파일 확인 (12c 이상 에서 가능 )
SELECT * 
  FROM (
        SELECT *
          FROM V$DIAG_TRACE_FILE
         ORDER BY CHANGE_TIME DESC
       )
 WHERE ROWNUM <= 10

-- 7. trace 파일의 내용 추출 (12c 이상 에서 가능 )
SELECT replace(replace(payload,chr(13)),chr(10)) as payload
  FROM V$DIAG_TRACE_FILE_CONTENTS
 WHERE TRACE_FILENAME = 'xxxx.trc'
 ORDER BY LINE_NUMBER

-- 8. 텍스트를 파일로 저장후 윈도우용 tkprof로 명령 수행
tkprof xxx.trc xxx.txt sys=no

1.3.2 SQL로 트레이스 내용 조회 (12C 이후)[편집]

• 12c이후 부터는 서버(터미널)에서 하지 않아도 된다.

SELECT NAME,VALUE
  FROM V$DIAG_INFO
 WHERE NAME = 'Default Trace File';

SELECT *
  FROM V$DIAG_TRACE_FILE
-- WHERE TRACE_FILENAME = 'xxxx.trc'
;

SELECT *
  FROM V$DIAG_TRACE_FILE_CONTENTS
 WHERE ADR_HOME = '트레이스 디렉토리'
   AND TRACE_FILENAME = '트레이스파일명.trc';

• 해당 뷰가 없으면 오라클 버전이 12c 이상인지 확인

-- PC에서 보려면 window용 tkprof.exe가 있어야 내용을 볼수 있다.

-- (오라클 클라이언트를 관리자용으로 설치 해야 함)

-- PAYLOAD 컬럼이 트레이스 파일내용임 => 복사하여 트레이스파일.trc 형태로 저장한다

• 더 간단하게 조회

SELECT   REPLACE (REPLACE (payload, CHR (13)), CHR (10)) AS payload
    FROM v$diag_trace_file_contents
   WHERE adr_home = '트레이스 파일 디렉토리'
     AND trace_filename = '트레이스파일명.trc'
ORDER BY line_number;

SELECT NAME,VALUE
  FROM V$DIAG_INFO
 WHERE NAME = 'Diag Trace';

SHOW parameter user_dump_dest

1.3.3 트레이스 파일 변경 및 내용 확인[편집]

$> tkprof 트레이스파일명 변경할파일명 [explain= ] [table= ] [print= ] [insert= ] [sys= ] [sort= ] ...

$> tkprof 트레이스파일명 변경할파일명 sys=no 
-- tkprof scott/tiger sys=no trace=orcl_ora_7180.trc output = report01.txt

1.3.4 TKPROF 결과 값[편집]

1. Parse

    : SQL문이 파싱되는 단계에 대한 통계. 새로 파싱을 했거나 Shared SQL Pool에서 찾아 온 것도 같이 포함 된다.

2. Execute

    : SQL문의 실행 단계에 대한 통계. Update, Insert, Delete 문장들은 여기에 수행한 결과만 나온다.

3. Fetch

    : SQL문이 실행되면서 패치된 통계

4. count

    : SQL문이 파싱/실행/패치가 수행된 횟수

5. cpu

    : parse, execute, fetch가 실제로 사용한 CPU시간

6. elapsed

    : 작업의 시작에서 종료시까지 실제 소요된 시간

7. disk

    : 디스크에서 읽혀진 데이터 블럭의 수

8. query

    :'읽기 일관성' 모드에서 처리한 블록 수. 여기에는 블록을 '롤백'하기 위해 롤백(undo) 세그먼트에서 읽은 블록 수가 포함됨.

9. current

    : 'current' 모드에서 읽은 블록 수. current 모드 블록은 일관된 읽기 방식이 아닌 현재 있는 그대로 검색됩니다.

   일반적으로 쿼리에 대한 블록 검색은 쿼리가 시작되었을 때 존재했던 그대로 가져옵니다. 현재 모드 블록은 이전 시점이 아닌 현재 있는 그대로 가져옵니다.

   SELECT 중에 테이블의 전체 스캔을 수행하기 위해 데이터 사전을 읽어서 익스텐트 정보를 찾기 때문에 current 모드 검색이 표시될 수 있습니다(일관된 읽기가 아닌 'current' 정보가 필요합니다). 수정하는 동안 현재 모드에서 블록에 액세스하여 쓰기를 수행합니다.

   • 물리적 IO를 수행하는 데는 버퍼링된 읽기보다 시간이 오래 걸리므로 대부분의 경우 버퍼링된 읽기를 수행해야 합니다.

10. rows

     : SQL문을 수행한 결과에 의해 최종적으로 액세스된 로우의 수