행위

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

DB CAFE

(트레이스 파일 변경 및 내용 확인)
(SQL 트레이스)
 
(같은 사용자의 중간 판 27개는 보이지 않습니다)
1번째 줄: 1번째 줄:
 
== SQL 트레이스 ==
 
== SQL 트레이스 ==
  
 +
=== 트레이스 파일 시간 정보 포함 ===
 +
<source lang=sql>
 +
alter session set timed_statistics = TRUE;
 +
</source>
 +
 +
=== 트레이스 파일 크기 제한 ===
 +
<source lang=sql>
 +
-- 무제한
 +
alter session set max_dump_file_size = 'unlimited';
 +
 +
-- 100M
 +
alter session set max_dump_file_size = 100M;
 +
 +
</source>
 
=== 현재 세션 트레이스 설정 ===
 
=== 현재 세션 트레이스 설정 ===
  
 
==== SQL 트레이스 수행 ====
 
==== SQL 트레이스 수행 ====
 +
{{틀:타이틀 투명
 +
|제목='''event level '''
 +
# 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 + level 4(bind 변수) + level 8(wait event 정보)
 +
|아이콘=emoji_objects
 +
}}
 
<source lang=sql>
 
<source lang=sql>
 
-- 0.트레이스 식별자 지정
 
-- 0.트레이스 식별자 지정
29번째 줄: 52번째 줄:
 
ALTER SESSION SET events '10046 trace name context off';
 
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
 
</source>
 
</source>
  
==== SQL 트레이스 파일 생성 위치 확인 ====
+
==== SQL로 트레이스 내용 조회 (12C 이후)  ====
 +
* 12c이후 부터는 서버(터미널)에서 하지 않아도 된다.
 +
<source lang=sql>
 +
SELECT NAME,VALUE
 +
  FROM V$DIAG_INFO
 +
WHERE NAME = 'Default Trace File';
 +
</source>
 +
<source lang=sql>
 +
SELECT *
 +
  FROM V$DIAG_TRACE_FILE
 +
-- WHERE TRACE_FILENAME = 'xxxx.trc'
 +
;
 +
</source>
 +
 
 +
<source lang=sql>
 +
SELECT *
 +
  FROM V$DIAG_TRACE_FILE_CONTENTS
 +
WHERE ADR_HOME = '트레이스 디렉토리'
 +
  AND TRACE_FILENAME = '트레이스파일명.trc';
 +
</source>
 +
* 해당 뷰가 없으면 오라클 버전이 12c 이상인지 확인
 +
 
 +
-- PC에서 보려면 window용 tkprof.exe가 있어야 내용을 볼수 있다.
 +
 
 +
-- (오라클 클라이언트를 관리자용으로 설치 해야 함)
 +
 
 +
-- PAYLOAD 컬럼이 트레이스 파일내용임 =>  복사하여 트레이스파일.trc 형태로 저장한다
 +
* 더 간단하게 조회
 +
<source lang=sql>
 +
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;
 +
</source>
 +
===== SQL 트레이스 파일 생성 위치 확인 (11g )=====
 +
 
 +
* 11G이하 버전은 서버에서 작업 해야 한다
 +
 
 
<source lang=sql>
 
<source lang=sql>
 
SELECT NAME,VALUE
 
SELECT NAME,VALUE
43번째 줄: 122번째 줄:
  
 
==== 트레이스 파일 변경 및 내용 확인 ====
 
==== 트레이스 파일 변경 및 내용 확인 ====
$tkprof 트레이스파일명 변경할파일명 [explain= ] [table= ] [print= ] [insert= ] [sys= ] [sort= ] ...
+
$> tkprof 트레이스파일명 변경할파일명 [explain= ] [table= ] [print= ] [insert= ] [sys= ] [sort= ] ...
 
<source lang=sql>
 
<source lang=sql>
$tkprof 트레이스파일명 변경할파일명 sys=no  
+
$> tkprof 트레이스파일명 변경할파일명 sys=no  
 
-- tkprof scott/tiger sys=no trace=orcl_ora_7180.trc output = report01.txt
 
-- tkprof scott/tiger sys=no trace=orcl_ora_7180.trc output = report01.txt
 
</source>
 
</source>
60번째 줄: 139번째 줄:
 
  - current  : 현 세선에서 작업한 내용을 커밋하지 않아 오로지 자신에게만 유효한 블럭(Dirty Block)을 액세스한 블럭 수. 주로 Update, Insert, Delete 작업시 많이 발생 한다
 
  - current  : 현 세선에서 작업한 내용을 커밋하지 않아 오로지 자신에게만 유효한 블럭(Dirty Block)을 액세스한 블럭 수. 주로 Update, Insert, Delete 작업시 많이 발생 한다
 
  - rows      : SQL문을 수행한 결과에 의해 최종적으로 액세스된 로우의 수
 
  - rows      : SQL문을 수행한 결과에 의해 최종적으로 액세스된 로우의 수
[출처] [Oracle] SQL 실행계획 / 트레이스 방법|작성자 sophieYeom
+
 
 +
[[category:oracle]]

2024년 1월 25일 (목) 14:26 기준 최신판

thumb_up 추천메뉴 바로가기


1 SQL 트레이스[편집]

1.1 트레이스 파일 시간 정보 포함[편집]

alter session set timed_statistics = TRUE;

1.2 트레이스 파일 크기 제한[편집]

-- 무제한 
alter session set max_dump_file_size = 'unlimited';

-- 100M
alter session set max_dump_file_size = 100M;

1.3 현재 세션 트레이스 설정[편집]

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

 emoji_objects event level

  1. level 0  : SQL_TRACE=FALSE 와 동일.
  2. level 1  : SQL_TRACE=TRUE 와 동일 일반적인 Trace 정보 제공(default)
  3. level 4  : level 1 + bind 변수 정보
  4. level 8  : level 1 + wait event 정보
  5. level 12  : level 1 + level 4(bind 변수) + level 8(wait event 정보)

-- 0.트레이스 식별자 지정
alter session set tracefile_identifier=’test’; ==> orcl_ora_10024_test.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;
1.3.2.1 SQL 트레이스 파일 생성 위치 확인 (11g )[편집]
  • 11G이하 버전은 서버에서 작업 해야 한다
SELECT NAME,VALUE
  FROM V$DIAG_INFO
 WHERE NAME = 'Diag Trace';

or

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 결과 값[편집]

- Parse     : SQL문이 파싱되는 단계에 대한 통계. 새로 파싱을 했거나 Shared SQL Pool에서 찾아 온 것도 같이 포함 된다.
- Execute : SQL문의 실행 단계에 대한 통계. Update, Insert, Delete 문장들은 여기에 수행한 결과만 나온다.
- Fetch     : SQL문이 실행되면서 페치된 통계
- count    : SQL문이 파싱/실행/페치가 수행된 횟수
- cpu        : parse, execute, fetch가 실제로 사용한 CPU시간
- elapsed : 작업의 시작에서 종료시까지 실제 소요된 시간
- disk       : 디스크에서 읽혀진 데이터 블럭의 수
- query    : 메모리내에서 변경되지 않은 블럭을 읽거나 다른 세션에 의해 변경되었으나 아직 커밋되지 않아 복사해 둔 스냅샷 블럭을 읽은 블럭 수. SELECT문에서는 대부분 여기에 해당하며 Update, Insert, Delete 작업시에는 소량만 발생 합니다.
- current  : 현 세선에서 작업한 내용을 커밋하지 않아 오로지 자신에게만 유효한 블럭(Dirty Block)을 액세스한 블럭 수. 주로 Update, Insert, Delete 작업시 많이 발생 한다
- rows      : SQL문을 수행한 결과에 의해 최종적으로 액세스된 로우의 수