[DB/Oracle] Optimizer [5]

Oralce은 많은 기업이랑 개발자가 사용하는 강력한 관계형 데이터베이스 관리 시스템이다.
하지만 유로 버전이기 때문에 직장을 다니지 않는다면 접하지 않는 경우가 많고 입사 후에 처음 접하는 일이 많다.

처음 접하는 사람도 따라가기 쉽게 글을 작성해 보려고 한다.

요번 글에서 진행할 내용은 Optimizer이다.

---

1. Optimizer란?

가장 효율적인 방법으로 sql을 수행할 최적의 처리 경로를 생성해주는 DBMS의 핵심 엔진이다.

개발자가 작성한 SQL을 실행하면 DBMS에서는 옵티마이저를 기반으로 실행계획을 세워서 쿼리를 실행한다.

옵티마이저는 다음 3가지 단계를 따른다.

1. Parsing

SQL 문장을 처리하는 첫 단계로, 다음과 같은 작업이 수행된다.

• 문법 건사 : SQL 문자잉 올바른 문법인지 확인한다. 문법에 오류가 있으면 에러를 반환한다.
• 구조 분석 : SQL 문장을 내부적으로 Parse Tree로 변환한다.  
  EX) SELECT, FROM, WHERE, GROUP BY 등의 키워드가 노드로 표현된다.
• SQL 캐시 확인 : 동인할 SQL문장이 이미 존재하는지 확인한다. 있다면 캐시를 사용한다.

2. Optimization

옵티마이저는 SQL 실행 계획을 생성학 위해서 여러 후보 계획을 평가하고 비용이 가장 낮은 실행 계획을 선택한다.

과정을 2가지로 구분되는데 이는 아래서 더 자세히 설명한다.

 

여기서 제너레이터라는게 등장한다.

제너레이터

옵티마이저의 일부로 SQL 실행 계획 후보를 생성하고 비용을 비교해서 최적의 실행 경오를 결정하는 프로세스이다.

후보군중 최적 실행 계획을 선택하고 실행 계획 생성 단계에 넘긴다. (논리적인 실행 계획이다.)

3. 실행 계획 생성

2번 단계에서 선택된 샐행 계획이 최종 실행 계획으로 생성된다. (여기서 물리적인 실행 절차로 변경된다)

실행 계획에 따라서 데이터를 접근하고 조작한다.

비용 기반 최적화를 통해서 가장 비용이 낮은 실행 계획이 선택된다.

---

2. Optimizer의 실행 계획

SQL 옵티마이저는 실행 계획을 선택할 때 RBO와 CBO를 사용한다

2.1 Rule-Based Optimization (RBO)

RBO는 실행 계획을 선택할 때 미리 정의된 규칙을 기반으로 한다. (비권장)

• 특징 :
  - 사전 규칙을 기반으로 실행 계획을 결정한다.
  - 테이블과 인덱스의 특성을 고려한 우선순위에 따라 결정한다.
  - 정보가 필요하지 않아 간단하고 빠르지만 실제 최적화가 안될수도 있음
  - SQL, 데이터등 모든 환경이 동일해도 DBMS 버전을 업그레이드하면 옵티마이저 다르게 작동할 수 있다.
• 작동 방식 :
  정해진 규칙에 따라 실행 계획을 선택한다. 따라서 항상 동일한 규칙에 따라 결정된다.
  1. 인덱스 우선 : 인덱스가 있는 테이블 먼저 선택
  2. 테이블 스캔 : 인덱스가 없으면 전체 테이블 스캔
  3. Join 순서 : 작은 테이블 먼저 스캔 후 큰 테이블 Join
• 한계점 :
  - Oracle 9i (2001) 부터 RBO를 권장하지 않았으며 10g(2003) 부터 RBO를 공식적인 지원하지 않는다.
  - 테이블이 커지거나 복잡한 조인이 많아지면 최적의 실행 계획을 선택하지 못한다.
  ex) 테이블 데이터수가 적어서 Full Scan이 더 빠른데 인덱스를 타서 더 느려진다.

2. Cost-Based Optimization (CBO)

CBO는 실행 계획의 비용을 기반으로 최적의 실행 계획을 선택하는 방식이다.

• 특징 :
  - 통계 정보를 기반으로 실행 계획을 생성한다.
  - 통계 정보는 테이블 크기, 인덱스 존재 여부. 행 개수, 데이터 분포등을 고려한다.
  - 비용은 디스크 I/O, CPU, MEM 사용량 등을 종합적으로 고려해 계산한다.
• 작동 방식 :
  - 통계 수집 : 테이블과 인덱스의 최신 통계 정보를 옵티마이저에 제공(아래 표 참고)
  - 후보 계획 생성 : 제너레이터를 통해 여러 실행 계획 후보군 생성
  - 비용 계산 : 각 후보 시랭 계획에 대해 비용을 평가
  - 최적의 실행 계획 선택 : 비용이 가장 낮은 실행 계획을 최종 선택
• EX :
  - 디스크 I/O : 테이블을 읽기 위해서 얼마나 많은 I/O 작업이 필요한가?
  - CPU 비용 : 데이터 필터링, 정렬, 조인 등 연산에 필요한 CPU 사용량
  - 네트워크 비용 : 분산 데이터의 경우 데이터 전송 비용
• 장점 :
  - 데이터 크기와 데이터 분포에 따라 최적의 실행 계획을 선택할 수 있다.
  - 복잡한 쿼리와 대규모 데이터에서도 효율적인 실행 계획을 생성한다.
• 단점 :
  - 통계 정보가 항상 올바르진 않는다.
  - 실행 계획 비용 계산에 시간이 걸릴수 있다.

주요 통계 정보들에는 다음과 같은 것들이 있다. 

구분	세부 통계 정보
테이블	테이블의 전체 행의 갯수
	테이블이 차지하고 있는 전체 블록 갯수
	테이블의 행들이 가지고 있는 평균 길이
컬럼	컬럼 값의 종류
	컬럼 내부 NULL 값의 분포도
	컬럼 값의 평균 길이
	컬럼 내부 데이터 분포의 추정치
인덱스	LEAF BLOCK 수 : 데이터를 보관하는 블록 수
	LEVELS : 인덱스 트리의 LEVEL 정보
	CLUSTERING FACTOR : 접근하고자 하는 데이터가 모여 있는 밀집도
시스템 통계 정보	I/O 성능 및 사용률
	CPU 성능 및 사용률

---

3. Optimizer의 한계

옵티마이저는 소프트웨어 엔진이라는 한계점이 있으며 완벽하지 않음을 이해해야 한다. 그래서 절대 맹신하지 말아야하며 옵티마이저가 비효율적으로 동작하고 있다면 Oracle의 힌트 같은 부가적인 장치로 올바르게 작동하도록 유도해야 한다.

1. 통계 정보의 부정확성
   - 문제 : 옵티마이저는 통계에 의존하며 이는 항상 잘못될 확률이 존재함 실제 시스템부하, I/O상황 메모리 사용량을 정확히 반영하는게 아닌 예측일뿐임
   - 해결 방법 : 정기적으로 DBMS_STATS같은걸 통해서 최신 통계를 유지함, 데이터 분포를 균형하게 변경
2. 실행 계획 탐색의 제약
   - 문제 : CBO는 비용 계산을 통해서 최적의 실행 계획을 찾지만, 제한된 범위에서 탐색한다. 즉 모든 실행 계획을 탐색하지 않는다
   - 해결 방법 : 옵티마이저 힌트를 사요앻서 실행 계획을 강제하자
3. 규칙 의존
   - 문제 : RBO에 비해서 자유로운 편히지만 부분적으로 CBO도 규칙에 의존한다.
4. 하드웨어 성능
   - 문제 : 옵티마이저는 기본적으로 옵티마이저 개발팀이 사용한 하드웨어에 맞춰져 있으며 이는 다양한 하드웨어 성능에 따라서 잘못된 실행계획을 수립할 확률이 생긴다.

---

4. 실행 계획 & 힌트

실행 계획을 알아보는 방식은 아래와 같다.

EXPLAIN PLAN FOR
--쿼리문
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

위에서설명한것처럼 사용하면된다. 

힌트를 생성하는 방식은 다음과 같다. 힌트이름 넣으면 된다.

SELECT /*+ HINT_NAME */ 컬럼명
FROM 테이블명
WHERE 조건;

예시 테이블

Join을 위해서 테이블을 2개 만들었다.

CREATE TABLE EMPLOYEES (
                           ID NUMBER PRIMARY KEY,
                           NAME VARCHAR2(50),
                           AGE NUMBER,
                           CITY VARCHAR2(50)
);

INSERT INTO EMPLOYEES VALUES (1, 'Alice', 25, 'Seoul');
INSERT INTO EMPLOYEES VALUES (2, 'Bob', 30, 'Busan');
INSERT INTO EMPLOYEES VALUES (3, 'Charlie', 35, 'Incheon');
INSERT INTO EMPLOYEES VALUES (4, 'David', 40, 'Daegu');
INSERT INTO EMPLOYEES VALUES (5, 'Eve', 45, 'Daejeon');

COMMIT;

CREATE INDEX IDX_EMP_AGE ON EMPLOYEES (AGE);

직원 테이블이며 ID와 AGE 에 Index 처리가 되어있다.

CREATE TABLE CITY (
                      CITY_NAME VARCHAR2(50) primary key ,
                      POPULATION NUMBER,
                      REGION VARCHAR2(50)
);

INSERT INTO CITY VALUES ('Seoul', 10000000, 'Capital');
INSERT INTO CITY VALUES ('Busan', 3500000, 'South');
INSERT INTO CITY VALUES ('Incheon', 3000000, 'West');
INSERT INTO CITY VALUES ('Daegu', 2500000, 'East');
INSERT INTO CITY VALUES ('Daejeon', 1500000, 'Center');

COMMIT;

CITY 테이블이며 CITY_NAME에 INDEX가 생성되어 있다.

 

기준 SQL 쿼리

기준을 잡는 쿼리는 이렇게 생겼다. 인덱스를 타는걸 확인할 수 있다.

EXPLAIN PLAN FOR
SELECT ID, NAME, AGE
FROM EMPLOYEES
WHERE AGE BETWEEN 30 AND 40;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

1. Full Table Scan 강제

인덱스를 타지 않고 FULL SCAN을 해버린다.

-- 1. Full Table Scan 강제
-- EMPLOYEES 테이블 전체 스캔을 강제
EXPLAIN PLAN FOR
SELECT /*+ FULL(EMPLOYEES) */ ID, NAME, AGE
FROM EMPLOYEES
WHERE AGE BETWEEN 30 AND 40;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

2. 특정 INDEX 사용 강제

어떤 일이 생겨도 인덱스를 강제로 사용한다.

-- 2. 특정 인덱스 사용 강제
-- IDX_EMP_AGE 인덱스를 사용하여 조건에 맞는 데이터를 조회
EXPLAIN PLAN FOR
SELECT /*+ INDEX(EMPLOYEES IDX_EMP_AGE) */ ID, NAME, AGE
FROM EMPLOYEES
WHERE AGE BETWEEN 30 AND 40;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

기준 SQL 쿼리

기준을 잡는 쿼리는 이렇게 생겼다. HASH JOIN 을 진행한다.

EXPLAIN PLAN FOR
SELECT E.ID, E.NAME, E.AGE, C.CITY_NAME, C.POPULATION, C.REGION
FROM EMPLOYEES E
         JOIN CITY C ON E.CITY = C.CITY_NAME
WHERE C.POPULATION > 2000000;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

1. 병렬 쿼리

지금은 데이터 셋이 적어서 딱히 체감이 되지 않을 것이다.

-- 1. 병렬 쿼리 (PARALLEL 힌트 사용)
-- CITY와 EMPLOYEES 테이블에 각각 병렬 4 프로세스를 강제 실행
EXPLAIN PLAN FOR
SELECT /*+ PARALLEL(CITY, 4) PARALLEL(EMPLOYEES, 4) */
    E.ID, E.NAME, C.CITY_NAME, C.POPULATION
FROM EMPLOYEES E
         JOIN CITY C ON E.CITY = C.CITY_NAME
WHERE C.CITY_NAME = 'Busan';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

2. 조인 순서 강제

원래는 CITY를 먼저 봤으나 EMPLOYEES를 먼저 보도록 강제

둘중 작은 데이터를 가진 테이블을 먼저 보는게 좋을수도 있다.

임플로이를 먼저보는경우 CITY의 테이블 조건을 먼저 확인하지 않아서 불필요한 조인이 발생할 수 있다. (지금 코드가 비효율적이라는 뜻)

-- 2. 조인 순서 강제 (LEADING 힌트 사용)
-- LEADING(C)를 사용하여 EMPLOYEES 테이블을 먼저 접근하도록 강제
EXPLAIN PLAN FOR
SELECT /*+ LEADING(E) */
    E.ID, E.NAME, E.AGE, C.CITY_NAME, C.POPULATION, C.REGION
FROM EMPLOYEES E
         JOIN CITY C ON E.CITY = C.CITY_NAME
WHERE C.POPULATION > 2000000;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);