00:49 2017-01-10 화
권순용의 실전 SQL Tuning 6강
실행계획과 옵티마이저에 대해 배워보자.
실행계획
– 개념
– 생성법
– 해석하기
3/22
<실행계획>
실행계획은 sql이 어떻게 수행되었는지에 대한 경로를 보여준다.
실행 계획에 따라 SQL은 인덱스를 이용할 수도 있고 이용하지 않을 수도 있다.
이처럼 실행 계획을 통해 해당 SQL의 성능을 예측할 수 있다.
5/22
<실행계획>
SQL의 시작부터 종료까지의 실행 경로를 계획하는 것
원하는 데이터를 추출하는 경로를 보여주기 때문에, SQL의 튜닝은 실행 계획의 분석부터 시작.
6/22
Explain
AUTOTRACE
TKPROF Trace
10046
<Explain 명령어>
실행계획을 추출하는 명령어. 이게 실행계획 분석의 시작이다.
1. Plan Table 생성
(유저에게 플랜 테이블에 존재해야 한다.)
@$ORACLE_HOME/rdbms/admin/utxplan.sql (Unix)
@$ORACLE_HOME\rdbms\admin\utxplan.sql (NT)
2. 실행계획 확인 방법
EXPLAIN PLAN FOR
SELECT * FORM EMPLOYEES;
이렇게 쓰면 실행계획을 추출한다.
<AUTOTRACE 명령어>
1. Plan Table 생성
(유저에게 플랜 테이블에 존재해야 한다.)
@$ORACLE_HOME/rdbms/admin/utxplan.sql (Unix)
@$ORACLE_HOME\rdbms\admin\utxplan.sql (NT)
2. PLUSTRACE 권한 생성 및 부여 (DBA 권한을 가진 유저로 수행해야 함)
옵션
TRACEONLY – 실행 수행 후 계획 추출
TRACEONLY EXPLAIN – 실행 제외 계획 추출
3. AUTOTRACE 옵션
SET AUTOT TRACEONLY EXP
SELECT * FROM XMP;
8/22
<TKPROF Trace>
1. Plan Table 생성.
2. SQL_TRACE Parameter를 True로 설정.
(30% 정도의 부하가 증가될 수 있다. 실전에서는 실행 계획 검토하려는 세션에만 적용해야.)
3. SQL 실행
Trace를 추출하고자 하는 SQL 수행
4. TKPROF 실행
# tkprof tracefile outputfile sys=no explain=username/password
많은 시간이 소요되는 SQL은 TKPROF Trace보다는
AUTOTRACE 명령어로 실행 계획 최적화 후, TKPROF Trace 방식 사용이 유리하다.
* 세션별 Trace를 수행하는 방법
exec sys.dbms_system.set_sql_trace_in_session(SID, SERIAL#, TRUE);
SID와 SERIAL#은 해당 세션의 고유 번호이며 해당 값은 V$SESSION 에서 확인 가능
* 실행계 성능 데이터에서 CPU시간과 ELAPSED 시간의 차이가 많다면?
악성 SQL에서 자주 발생. CPU시간과 Query 시간과의 많은 차이는 과다한 데이터 블록 엑세스에 의해 발생함.
대개의 경우 악성 SQL인 경우가 대부분임.
<실행 성능 데이터>
Count : 총 건수
Query : (메모리에서 엑세스하는) 데이터 블록 개수
CPU : cpu사용시간
Current : DML시 엑세스하는 데이터 블록 개수
Elapsed : 수행시간
Rows : 추출된 데이터의 건수
Disk : 디스크에서 엑세스하는 데이터 블록 개수
* 토드 툴의 경우 실행 성능 데이터는 Trace 탭에서 확인 가능하다.
4. 10046 Trace
사용법 : ALTER SESSION SET EVENTS ‘10046 trace name context forever, level 9’ ;
Trace 파일 위치 : USER_DUMP_DEST 파라미터에 정의된 위치에 저장
TKPROF보다 좀 더 자세한 내용을 확인할 수 있다.
(오렌지나 토드 툴에 경우 Execution Plan 또는 Explain 항목이 존재하여 실행계획 확인 가능.)
11/22
실행계획 해석
1) 도식화
2) 해석
Execution Plan
0 SELECT STATEMENT
1 0 TABLE ACCESS (BY INDEX ROWID) OF ‘EMPLOYEES’
2 1 INDEX (RANGE SCAN) of ’employees_IDX
(좌측부터)
키 값 : 실행계획 내의 유니크 값(0, 1, 2…)
부모키 값: 해당 실행계획에 대한 부모키 값 (0, 1…)
실행계획 : 각각의 실행단계
실행계획의 해석
1. 키값을 이용한 도식화 : 키값을 구준으로 부모 키값을 연결해서 해석
2, 레벨을 이용한 해석 : 실행계획의 들여쓰기 기준에 의해 해석
SELECT
TABLE ACCESS
INDEX SCAN
도식화된 실행계획 해석시 다음의 우선순위 준수해서 실행계획을 해석하면 됨.
아래에서 위로 – 아래 있는 실행계획부터 해석
좌측에서 우로 – 동일 높이일 경우 좌측 실행 계획부터 해석
LEVEL에 의한 실행계획 해석 (아래쪽이 레벨이 낮은것)
조인을 사용하는 경우, 조인 방식에 의해 해석해야만 정확한 해석이 가능하다.
– 익숙해지면 도식화를 위한 이해 -> LEVEL에 의한 이해 로 가는게 해석 속도가 빨라진다.
SELECT STATEMENT
NESTED LOOPS
TABLE ACCESS (FULL) OF ‘TABV1’
TABLE ACCESS (FULL) OF ‘TABV2’
Level이 낮은 실행계획을 먼저 해석
동일 Level이라면 위치상 위에 있는 실행계획을 먼저 해석
조인방식 사용시 조인실행 방식에 의해 해석
15/22
악성 SQL 최적화 수행 순서
– TRACEONLY EXP : SQL 수행하지 않고 실행계획만 확인하여 실행 계획 최적화 수행
– TRACEONLY : 실행계획 최적화 후 SQL을 수행하여 세미한 최적화 수행함
SQL 최적화 시 위와 같이 1단계 수행후 2단계로 가게 됨. 경우에 따라서는 동시에 하기도 함.
16/22
AUTOTRACE 를 이용하여 실행계획만을 추출하고자 한다면 다음 중 어떤 옵션을 사용해야 할까요?
알맞은 옵션을 드래그 하여 넣어보세요.
on 옵션 : 결과도 추출
실행계획, 실행성능
– TRACEONLY EXP == 결과없이 실행계획만
– TRACEONLY ONLY == 결과없이 실행계획, 실행성능데이터
– OFF == 트레이스 모드가 종료된다.
18/22
실행계획은 왜 분석이 필요할까요?
– SQL이 인덱스를 사용했는지 아닌지에 대한 분석, 인덱스를 이용했다면 어떤 인덱스를 썼는가,
어떻게 이용했는가를 이해해야만 해당 SQL을 최적화할 수 있게 된다.
그렇게 때문에 SQL 성능을 분석하고 최적화하기 위해서는 실행계획 분석이 필수적이다.
AUTOTRACE ON : 실행하고, 실행계획 얻음
AUTOTRACE TRACE ONLY : 해당 SQL수행하고, 실행계획 얻음. 결과는 추출되지만 실제 화면에 디스플레이 되진 않는다.
AUTOTRACE TRACE ONLY EXP : 수행없음. 단지 실행계획만 얻는다.
*** 가장 안쪽의 들여쓰기 실행계획부터 해석하면 되고,
동일한 들여쓰기라면 위의 실행계획부터 해석한다.
퀴즈 1. 실행계획의 설명으로 올바른 것은?
1) SQL이 수행되어 추출되는 결과를 의미
2) SQL이 실행되는 경로를 의미
3) SQL의 구문 분석을 의미
4) SQL의 응답속도를 의미
답 : 2
– sql이 인덱스를 탔는지 안탔는지에 대한 내용도 실행계획을 통해 볼 수 있음.
sql이 어떻게 수행되었는지에 대한 경로를 보여주게 됨.
퀴즈 2. 실행 계획을 추출할 수 있는 방법이 아닌 것은?
1) Explain Paln For
2) 10046 Trace
3) Dump
4) Autotrace
답 : 3
실행 계획을 추출할 수 있는 방법에는 Explain 명령어, AUTOTRACE 명령어, TKPROF Trace 명령어, 10046 Trace 등이 있습니다.
퀴즈 3. 실행 계획의 해석 방법으로 올바른 것은?
1) Level을 이용한 실행계획 해석
2) 위에서부터 아래로 해석하는 Top down 방식
3) 아래서부터 위로 해석하는 Bottom up 방식
4) Case By Case 를 이용한 해석 방식
답 : 1
01:37 2017-01-10 화