SQL 튜닝 - 실행계획, 옵티마이저, 튜닝 절차

※ 튜닝의 전제조건: 최대한 하나의 SQL문으로 처리하고, 동일 데이터를 중복해서 읽지 않는 것

1. 실행계획

* 실행 계획(Execution Plan): 사용자가 작성한 SQL이 요구한 데이터를 추출하기 위해 옵티마이저가 작업의 방법과 순서를 결정하는 것

1) EXPLAIN PLAN

SQL문장을 해석하고 실행계획을 수립하여 실행계획 테이블에 저장해주는 명령(PLAN 테이블이 만들어져 있어야 함)

(예시)

EXPLAIN PLAN
INTO PLAN_TABLE FOR
SELECT A.ENAME, A.DEPTNO, B.DNAME
FROM EMP A, DEPT B
WHERE A.DEPTNO = B.DEPTNO;

Explained.

SELECT LPAD(' ', 2*(LEVEL-1))||OPERATION AS OPERATION, NVL(OPTIONS, ' ') AS OPTIONS, 
        NVL(OBJECT_NAME, ' ') AS OBJECT_NAME, POSITION AS POS,
        OBJECT_INSTANCE AS INST, ID, PARENT_ID AS P_ID
FROM PLAN_TABLE
START WITH ID = 0
CONNECT BY PRIOR ID = PARENT_ID;

위 방법은 매번 SQL 문장을 EXPLAIN PLAN 명령어와 함께 사용해야 한다.

2) SET AUTOTRACE

한 번의 명령어로 세션 단위에서 실행되는 SQL문의 실행계획을 SQL문 실행과 함께 볼 수 있도록 한 것

 

[과정]

* PLAN_TABLE을 생성 -> DB User에 PLUSTRACE ROLE 할당 -> SET AUTOTRACE ON -> SQL문 입력

SET AUTOTRACE ON;

SELECT A.ENAME, A.DEPTNO, B.DNAME
FROM EMP A, DEPT B
WHERE A.DEPTNO = B.DEPTNO;

 

* 옵션

- SET AUTOTRACE ON

- SET AUTOTRACE OFF

- SET AUTOTRACE TRACEONLY: SQL문의 결과 양이 많아서 디스플레이 시간이 많이 소요되거나, 실행계획만 보고 싶을 때 사용하는 옵션

 

3) EXECUTION PLAN 분석

SELECT ENAME, JOB, SAL, DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
AND EMP.EMPNO > 7800
AND EXISTS (SELECT *
            FROM SALGRADE
            WHERE EMP.SAL BETWEEN LOSAL AND HISAL);

* OPERATION

(4, 5, 7, 8, 11번째 줄, 굵은 글씨는 어떤 방법으로 액세스 했는지를 나타냄)

- TABLE ACCESS BY INDEX ROWID

- INDEX FULL SCAN(전체 접근)

- INDEX RANGE SCAN(범위 접근)

- TABLE ACCESS FULL(전체)

 

* Rows: 해당 쿼리에 영향 받는 Row 수

* Bytes: Rows * (1 Row의 총 길이), 쿼리 실행 시 발생하는 네트워크 트래픽(I/O 발생량)

* Cost: 해당 쿼리가 동작할 때 소요하는 비용

 

 

* 실행 순서

1) 테이블 액세스 프로세스와 해당 테이블의 인덱스 프로세스는 하나의 단위로 생각한다.

- ( ID 4, ID 5 ) , ( ID 7, ID 8 ), ( ID 11 )

2) 여러 문장 중에서 들여쓰기가 많이 되어 있는 문장이 먼저 실행되는 문장

3) 들여쓰기가 같은 레벨이라면 위에 있는 문장이 먼저 실행

[ 8 - 7 - 6 - 5 - 4 - 11 - 3 - 10 - 2 - 9 - 1 - 0 ]

2. 옵티마이저

1) RBO(Rule Base Optimizer) - 규칙 기준 접근 방식

* 경험적으로 순위가 매겨진 오퍼레이션에 기초한 실행 계획 선택

* 수립될 실행계획이 예측 가능하기 때문에 사용자가 원하는 처리 경로로 유도하기 용이

2) CBO(Cost Base Optimizer) - SYBASE의 경우, CBO만 지원

* 목적: 대상 Rows를 처리하는데 필요한 자원 사용을 최소화해서 데이터를 빠르게 처리하는 것

* 비용 산정 요소: 각종 통계 정보, SQL 형태, Hint, Optimizer_Mode, 연산자, Index, Cluster, DBMS 버전, CPU 용량, Memory 용량, Disk I/O, Link N/W 비용 등

* 성능을 최적의 상태로 유지시켜주기 위해 ANALYZE OBJECT 작업을 정기적으로 해주어야 한다.

 

* ANALYZE OBJECT

  - ANALYZE 명령어는 테이블, 인덱스, 클러스터의 통계 정보를 생성

  - 생성한 통계 정보는 CBO가 최소비용을 계산할 때 사용

  - 얻는 통계 정보

분류	정보
테이블	- 총 Row, Block 수 - 비어있는 Block에 쓰여질 수 있는 빈 공간의 평균 - Chain이 발생된 Row 수
인덱스	- Row의 평균 길이 - Index의 깊이 - Leaf Block, Distinct Key의 수 - Leaf Blocks/Key의 평균, Data Blocks/Key의 평균 - Clustering Factor - Max Key, Min Key 값
칼럼	- Distinct한 값의 수 - 히스토그램 정보
클러스터	Cluster key 당 길이의 평균

3) OPTIMIZER_MODE

* 종류

- CHOOSE: 사용된 TABLE 중 적어도 하나 이상의 통계 정보를 이용할 수 있다면 CBO를 사용, 그렇지 않고 사용되는 테이블 모두가 한 번도 ANALYZE 작업을 한 적이 없다면 RBO를 사용 / RULE을 제외한 힌트를 사용해도 CBO를 사용

- RULE: 통계 정보의 유무에 상관없이 RANK에 따른 실행계획을 세움

- FIRST_ROWS: CBO의 일종으로 FIRST_ROWS는 전체 응답 속도에 상관없이 일부 데이터를 먼저 보여줄 수 있도록 최적화된 모드, ON LINE 처리 시에 좋은 성능을 발휘한다.

- ALL_ROWS: CBO의 일종으로 결과에 대한 전체 응답 속도를 최소화하기 위해 사용, 전체 평균이나 합이 필요한 리포팅에 유용

CBO	- 하나 이상의 통계 정보 이용 - RULE을 제외한 힌트 사용 - ON LINE -> FIRST_ROWS(일부 데이터) - 전체 평균, 합 -> ALL_ROWS
RBO	ANALYZE 작업이 한 번도 없을 경우

 

* 레벨별 설정

- Instance Level(시스템 전체 설정): initSID.or에 설정

" OPTIMIZER_MODE = {RULE / CHOOSE / FIRST_ROWS / ALL_ROWS } "

 

- Session Level: ALTER SESSION 명령 사용

" ALTER SESSION SET OPTIMIZER_MODE = {RULE / CHOOSE / FIRST_ROWS / ALL_ROWS } "

cf) Session: 오라클은 사용자와 DB 접속이 이뤄지면 세션을 생성, 사용하는 동안 계속 유지되고 각 세션에는 SID, 시리얼번호가 부여된다.

 

- Statement Level(SQL문): Hint를 추가함으로써 지정

" SELECT /*+ first_rows */ ename from emp; "

 

3. 데이터베이스 튜닝 절차

1) 모델링 단계의 튜닝

- 비즈니스 규칙 튜닝

- 데이터 모델링 튜닝

 

2) S/W 단계의 튜닝

* DB Access 방식 튜닝

- 최상의 시스템 성능을 위해 DBMS의 기능을 충분히 활용하고 있는지 검증

- 온라인 작업과 배치 작업의 비율을 감안하여 옵티마이저 모드를 결정

 

* DB Object 튜닝

- 예) FTS(Full Table Scan)로 처리되는 테이블에 인덱스를 추가해서 인덱스를 경유하게 한다면 실행시간을 단축시킬 수 있다(인덱스의 추가나 변경 작업)

- 다양한 인덱스 사용(Bitmap 인덱스, 함수 기반 인덱스, Reverse Key Index, Cluster 인덱스 등)

 

* SQL 및 프로그램 튜닝(Statement 튜닝) - 힌트(Hint) 사용

- 힌트: 실행계획의 방향을 가이드 함. 특정 SQL문의 실행 계획을 고정시키는 역할

- 여러 개를 같이 사용 할 수 있으며, 서브쿼리, 인라인 뷰 단위로도 중첩해서 사용할 수 있음

- 문법이 틀리거나 오타가 있어도 에러를 반환하지 않음

- 대소문자를 구분하지 않고, 빈칸이 있어도 상관 없음.

- 힌트를 포함하는 주석은 SELECT, UPDATE, INSERT, DELETE 키워드 다음에만 사용 가능

/*+ HINT */

--+HINT

(예시)

CREATE TABLE TEST (
	ID		NUMBER NOT NULL PRIMARY KEY,
    NAME	VARCHAR2(30)
    );
    
INSERT INTO test
SELECT 2, '2나' FROM DUAL
UNION ALL
SELECT 4, '4라' FROM DUAL
UNION ALL
SELECT 5, '5마' FROM DUAL
UNION ALL
SELECT 1, '1가' FROM DUAL
UNION ALL
SELECT 3, '3다' FROM DUAL; -- INSERT INTO VALUES (1, 2), (3, 4); 방식 X

힌트를 사용해서 정렬을 해본다.

CREATE INDEX IDX_TEST_TEST_NAME ON TEST(NAME);

SELECT * FROM TEST WHERE NAME > ' ';

정렬 완료

- 힌트 종류

HINT	내용	사용법
1. INDEX ACCESS OPERATION 관련 HINT
INDEX	INDEX를 순차적으로 스캔	INDEX(TABLE명, INDEX명)
INDEX_DESC	INDEX를 역순으로 스캔	INDEX_DESC(TABLE명, INDEX명)
INDEX_FFS	INDEX FAST FULL SCAN	INDEX_FFS(TABLE명, INDEX명)
PARALLEL_INDEX	INDEX PARALLEL SCAN	PARALLEL_INDEX(TABLE명, INDEX명)
NOPARALLEL_INDEX	INDEX PARALLEL SCAN 제한	NOPARALLEL_INDEX(TABLE명, INDEX명)
AND_EQUALS	INDEX MERGE 수행	AND_EQUALS(INDEX_NAME1, INDEX_NAME2)
FULL	FULL SCAN	FULL(TABLE명)
2. JOIN ACCESS OPERATION 관련 HINT
USE_NL	NESTED LOOP JOIN	USE_NL(TABLE1, TABLE2)
USE_MERGE	SORT MERGE JOIN	USE_MERGE(TABLE1, TABLE2)
USE_HASH	HASH JOIN	USE_HASH(TABLE1, TABLE2)
HASH_AJ	HASH ANTIJOIN	HASH_AJ(TABLE1, TABLE2)
HASH_SJ	HASH SEMIJOIN	HASH_SJ(TABLE1, TABLE2)
NL_AJ	NESTED LOOP ANTI JOIN	NL_AJ(TABLE1, TABLE2)
NL_SJ	NESTED LOOP SEMI JOIN	NL_SJ(TABLE1, TABLE2)
MERGE_AJ	SORT MERGE ANTIJOIN	MERGE_AJ(TABLE1, TABLE2)
MERGE_SJ	SORT MERGE SEMIJOIN	MERGE_SJ(TABLE1, TABLE2)
3. JOIN시 DRIVING 순서 결정 HINT
ORDERED	FROM절의 앞에서부터 DRIVING	/*+ ORDERED */
DRIVING	해당 테이블을 먼저 DRIVING	DRIVING(TABLE)
4. 기타 HINT
APPEND	INSERT시 DIRECT LOADING	/*+ APPEND */
PARALLEL	SELECT, INSERT 시 여러 개의 프로세스로 수행	PARALLEL(TABLE, 개수)
CACHE / NOCACHE	데이터를 메모리에 캐싱 / 캐싱 X	/*+ RESULT CACHE*/
PUSH_SUBQ / NO_PUSH_SUBQ	SUBQUERY를 먼저 수행, 최대한 빠르게 수행하여 MAIN TABLE이 다른 테이블과 조인하기 전에 필터하여 최대한 건수를 줄여줄 필요가 있을 때.	/*+ PUSH_SUBQ*/
REWRITE / NOREWRITE	QUERY REWRITE 수행 / 수행 X	/*+ REWRITE [(VIEW...)]*/
USE_CONCAT	ACCESS 경로를 변경, WHERE절에 있는 OR를 UNION-ALL 연산을 이용하도록 실행 계획을 세우는 역할, OR 혹은 IN 조건을 별도로 분리하여 각각 실행한 후 이를 연결하는 실행 계획	/*+ USE_CONCAT*/

 

※ 출처: https://blog.naver.com/batcan/130029661866

오라클 힌트

3) H/W 단계의 튜닝

* 메모리 튜닝

* 물리적 구조 및 디스크 I/O 튜닝

* 자원 경합에 대한 프로세스 튜닝: Block I/O, Shared Pool, Lock, Latch에 대한 경합

* H/W 시스템에 특화된 튜닝