SQL AutoTrace & SQL Trace

SQL 실행 계획 확인 방법

SQL 튜닝을 위해서 필요한 기본적인 툴들을 사용

1) SQL *Plus 의 AutoTrace

- 가장 일반적인 방법

- 실행 결과, 실행 계획, 통계 정보

2) SQL Trace 파일 생성 및 TKPROF 사용

- 모든 SQL 문장에 대한 추적 파일 생성

- TKPROF로 추적 파일 내용 분석

- 실제 튜닝에서 가장 많이 활용하는 툴

3) Oracle Enterprise Manager; SQL 스크래치 패드

4) EXPLAIN PLAN 명령어 사용, PLAN_TABLE에 실행 정보 저장

※ 오라클 관리용 프로그램으로 쓰는 프로그램들 --> TOAD(외국), ORANGE(한국)

이것들을 통해 실행계획을 쉽게 만들 수 있음.?

SQL *Plus AutoTrace

기능 활성화

1. PLUSTRACE 권한 생성 -> 한번만 하면 됨

SYS>conn / as sysdba

SYS>@?/sqlplus/admin/plustrce.sql

?란 /home/oracle 약자라고 생각하면 됨

2. 해당 사용자에게 PLUSTRACE 권한 부여

SYS>grant PLUSTRACE to scott ;

3. 해당 사용자에서 PLAN TABLE 생성

SYS>conn scott/tiger

SCOTT>@?/rdbms/admin/utlxplan.sql

Plan Table은 실행계획을 우리가 볼수있게끔 결과가 출력되는 테이블을 말함

가장 마지막 실행 계획만 조회 가능

create table PLAN_TABLE (

             *

ERROR at line 1:

ORA-00955: name is already used by an existing object

--> 이런식으로 에러가 나오는 경우도 있음..

10g부터는 plan_table과 public synonym이 자동으로 생성된다고 한다.

Autotrace 모드 설정

실행 계획을 저장 --> Session level이기 때문에 접속할 때마다 쳐야한다

> set autotrace on 또는

> set autot on

Autotrace 해석방법

Execution Plan 에서

작업 순서를 알 수 있음, 오른쪽으로 들여쓰기 한 것이 먼저 수행하는 것

level이 똑같으면 위에것 부터 수행하고 아래껄 수행한다.

예) 

0 select statement

1 merge join

2 table access~~

3 index~~

4 sort~~

5 table~~

이런식으로 결과가 나왔다고 했을 때,

작업된 순서는 3 ->2 ->5 ->4 ->1 ->0

Index만 읽어서 데이터 찾을 수 있다면 table까지 읽지 않아도 알 수 있다

index는 이미 데이터들을 정렬해 뒀기 때문에 table만 읽는 (full scan) 것보다 훨씬 빠름

Statistics 에서

Recursive calls: Dictionary 얼마나 조회했나, soft parse를 했다면 숫자가 적게 나옴 -> 속도 ↑

Consistent gets: Database Buffer Cache에서 몇개의 Block을 읽었는가

Physical reads: 하드디스크에서 읽은 개수

※ shared pool 초기화 명령어

alter system flush shared_pool ;

SQL Trace

Autotrace는 원하는 결과가 안나오는 경우도 있음, 또한 쿼리 날렸던 시점만 가능, 자기것만 조회 가능

SQL Trace는 모든 SQL 수행에 대한 Trace 파일을 생성

다른 유저뿐 아니라 과거 쿼리의 내용까지도 볼 수 있음

인스턴스 레벨 추적도 가능하지만 많은 부하가 발생해 세션 레벨로 추적한다.

구문 분석, 실행 및 인출 단계에 대한 크기 및 시간 통계 정보를 알 수 있음 즉, 세부적인 결과 인출 가능

이를 통해 어디서 시간이 많이 걸리고 왜 그만큼 걸렸는지 분석할 수 있다.

관련 파라미터 파일

USER_DUMP_DEST에 추적 파일 생성 됨

TIMED-STATISTICS = true -> 정확한 시간 통계치

기능 활성화

인스턴스 레벨

초기화 파라미터 수정 --> SQL_TRACE = TRUE

하지만 부하문제로 세션 레벨 사용

세션 레벨, 현재 접속중인 세션

SYS> grant alter session to scott

SYS> conn scott/tiger

SCOTT> alter session set tracefile_identifier='AAA'

SCOTT> alter session set sql_trace=ture

SCOTT> alter session set timed_statistics=true

기능 활성화 후 쿼리를 날리고 나서

trace 파일들은 USER_DUMP_DEST에 저장이됨

경로를 확인하려면 

> show parameter user_dump_dest ; --> SYS 계정으로 칠 것

USER_DUMP_DEST에는 trace 파일이 이미 많이 존재함

구별을 주기 위해서 

alter session set tracefile_identifier='AAA'

이 작업을 해준 것! 이렇게 해야 해당 쿼리에 대한 trace 파일 찾기가 수월하다.

SQL TRACE는 Trace file(.trc)을 열어서 읽어야 함

ls *AAA* --> 이렇게 검색해서 열면 결과가 자세히 나옴

하지만!!! 두둥 읽을수가 업숴.. 뭔말이여?

그래서 TKPROF를 통해 리포트를 생성한다

$ tkprof trace파일이름 a.txt explain=scott/tiger sys=no

sys=no는 리커시브 쿼리는 빼라는 뜻

trace파일을 a.txt로 읽기 쉽게 번역해준다.

TKPROF 통계 정보 분석

1) PARSE

- SQL 문을 실행계획으로 변환하는 과정에서 발생하는 통계치

2) EXECUTE

- 명령문을 실행하면서 발생하는 통계치

- INSERT, UPDATE, DELETE에 관련된 정보 

3) FETCH

- 인출시에 발생하는 통계치

- 주로 SELECT에 관계된 정보

- DML 작업들은 FETCH 작업이 없기 때문에 COUNT 되지 않는다.

4) COUNT

- 각 처리 단계 별 실행된 수

5) CPU

- 순수 작업 시간 (CPU 소모 시간)

6) Elapsed

- 총 소유 시간 (시작에서 종료까지 총 경과시간)

- 먼저 CPU와 Elapsed의 차이를 살펴봐야함 -> 차이가 많이나면 기다리는게 많다는 의미

   5배 차이가 난다고 하면, CPU안으로 데이터가 안들어와서 5배는 더 기다렸다는 의미

   이유는 네트워크 문제거나 디스크에서 퍼오는 데에 문제 등 어떤 문제가 있는 것, 성능 ↓

   기다린 이유를 찾는 기술이 바로 OWI = Oracle Wait Interface

7) DISK

- 물리적인 디스크 블록 접근한 횟수

8) QUERY

- 다른 세션에 의해서 변경되었으나 아직 commit 되지 않아 복사해둔 snapshot 블록 수 

    -> CR블록 = 다른 사용자들도 읽을 수 있음

- 주로 SELECT 문 작업시 발생

- DISK와 QUERY 비교했을 때 (항상 DISK < QUERY)

   예를 들어 DISK= 1 , QUERY= 4 라고 할 때,

   DISK에서는 1개의 블록을 읽고 나머지 3개는 DB Buffer Cache에 있는 블록을 읽었다라고 해석할 수 있음

9) CURRENT

-  현 세션에서 작업한 내용을 Commit하지 않아 자신만 읽을 수 있는 블록에 접근한 수 

- 주로 DML 작업시 발생

10) ROWS

- 읽은 총 ROW 수

- 즉, 최종적으로 엑세스된 총 ROW 수 라고 보면 됨