오라클 튜닝(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 기능 활성화
가. 인스턴스 레벨(파라미터 파일 수정)

나. 현재 접속중인 세션

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 결과물 생성

참고 : 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
순으로 실행됐다.