오라클 튜닝(Autotrace, SQL Trace)

1. SQL 튜닝에 사용하는 툴
가. Autotrace
– SQL PLUS에서 기본 제공하는 기능
– 간단한 통계정보 표시
– 1개의 문장 통계정보만 표시
– SQL문장 실행 후 바로출력

나. SQL Trace

– 여러 SQL문장 통계정보 표시
– 자세한 통계정보 표시
– 별도의 가공을 거쳐 통계정보 출력

2. SQL PLUS의 Autotrace 기능 활성화
가. PLUSTRACE 권한생성

conn / as sysdba
@?/sqlplus/admin/plustrce.sql

나. 사용자에게 권한부여

grant plustrace to scott;

다. 해당사용자의 PLAN Table 생성

conn scott/tiger
@?/rdbms/admin/utlxplan.sql

라. AUTOTRACE 모드 설정

set autotrace on
set autot off


3. SQL Trace 기능 활성화

가. 인스턴스 레벨(파라미터 파일 수정)

SQL_TRACE=TRUE

나. 현재 접속중인 세션

grant alter session to scott;
alter session set sql_trace=true;
execute dbms_session.set_sql_trace(true);

다. 임의의 세션

execute dbms_system.set_sql_trace_in_session(sid, serial#, true);

라. SQL Trace 결과물 생성

$ tkprof testdb_ora_24607_GAP.trc stat.txt explain=scott/tiger sys=no

참고 : trace 파일에 접미사를 붙이는 방법
alter session set tracefile_identifier=’원하는접미사’;

4. Trace 분석

가. 실행한 쿼리문

select a.name, a.grade, a.userid, b.name
from student a, professor b
where a.profno=b.profno

나. 각 단계별로 읽어들인 블록 수

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 15 0 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 17 0 10

참고 :
count : 각 처리 단계별 실행 횟수
cpu : 각 처리 단계별 cpu 사용시간(초)
elapsed : 각 처리에 소요된 총 시간(초) <- cpu 작업시간, 대기시간 등 모두 합친 시간
disk : 디스크에 저장된 블록에 접근한 횟수
query : CR작업을 위해 접근한 블록 수
current : 현재 세션에서 처리하기 위해 접근한 블록 수
rows : 각 처리 단계별 처리한 총 행수

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 28 (SCOTT)

Rows Row Source Operation
------- ---------------------------------------------------
10 HASH JOIN (cr=15 pr=0 pw=0 time=411 us)
8 TABLE ACCESS FULL PROFESSOR (cr=7 pr=0 pw=0 time=56 us)
16 TABLE ACCESS FULL STUDENT (cr=8 pr=0 pw=0 time=23 us)

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
10 HASH JOIN
8 TABLE ACCESS (FULL) OF 'PROFESSOR' (TABLE)
16 TABLE ACCESS (FULL) OF 'STUDENT' (TABLE)

 

참고 : 실행순서
맨 처음행을 읽어들여 아래행과 비교한다.
아래 행이 들여쓰기가 된 경우 먼저 실행한다.
만일 들여쓰기 레벨이 같다면 위에 있는 행부터 차례대로 실행한다.

위에서는 예제에서는
TABLE ACCESS FULL PROFESSOR
TABLE ACCESS FULL STUDENT
HASH JOIN
SELECT STATEMENT
순으로 실행됐다.

You may also like...

답글 남기기

이메일 주소는 공개되지 않습니다. 필수 항목은 *(으)로 표시합니다