[DB] 옵티마이저

[verify$y]·2025년 7월 31일
DBMSOptimizer

CS핵심개념

목록 보기
9/35

옵티마이저

옵티마이저란

  • SQL 실행 전 가장 효율적인 실행 계획을 결정하는 DBMS엔진
  • SQL을 가장 빠르고 적은 리소르로 실행하기 위한 실행계획(execution. plan) 을 수립
  • 통계정보를 가지고 다양한 후보 실행계획의 비용을 비교하여 최적의 계획을 선택

추가 설명

  • 가장 효율적인 방법으로 SQL을 수행할 최적이 처리 경로를 생성해주는 DBMS의 핵심엔진이다. 컴퓨터에서 CPU의 역할을 DBMS에서 수행한다.
  • 개발자가 SQL을 작성하고 실행하면 소프트웨어 실행파일처럼 즉시 실행되는 것이 아니라 옵티마이저라는 곳에서 쿼리문실행방식을 계획한다.
  • 이런 실행계획을 세운 뒤 시스템 통계정보를 활용하여 각 실행 계획의 예상 비용을 산정한 후 각 실행계획을 비교해서 최고의 효율을 가지고 있는 실행계획을 판별한 후 그 실행계획에 따라 쿼리를 수행한다.


옵티마이저의 목표

  • 쿼리를 가장 빠르게 실행하여 사용자에게 결과를 빠르게 제공하기
  • 주어진 리소스를 효율적으로 사용하여 다른 작업에 영향을 주지 않는다.
  • 데이터베이스의 구조와 통계정보를 사용하여 쿼리 실행 계획을 결정

작동순서

  • 사용자가 SQL을 작성
  • Parser가 SQL을 옵티마이저에게 전달하고
  • 옵티마이저에서 정해진 규칙에 따라 (규칙기반/ 바용기반) 쿼리 실행계획을 세운다. 이때 딕셔너리를 사용해서 통계낸 결과를 바탕으로 최적의 게획을 세운다.
  • 이 실행계획은 SQL 실행엔진에서 수행하여 결과를 사용자에게 리턴한다.



옵티마이저 유형

  • 옵티마이저는 실행계획 세우는 방식에 따라 규칙 위반 옵티마이저와 비용 기반 옵티마이저로 나뉜다.

규칙 옵티마이저 RBO

  • 정해진 규칙에 따라 실행계획을 결정(우선순위가 정해져있다)
  • 예측가능하지만 비용은 고려하지 않는다.

추가 설명

  • 실행속도가 빠른 순으로 규칙을 먼저 세워두고 우선순위가 앞서는 방법을 채택
  • 비용측정기술이 부족하던 과거에 많이 사용되는 방식이다.
  • 규칙에 우선순위를 부여했다.
  • 규칙 기반 옵티마이저에서는 우선순위를 바탕으로 실행계획을 미리 예측할 수 도 있다.
  • 하지만 단점도 존재한다.
  • 단점 :
    • 통계정보 반영못함
      • 테이블의 레코드수가 적을 떄 탐색할 경우, FULL TABLE STAN이 더 빠를 수도 있는데 INDEX로 탐색하는 경우가 있다.
    • 최신기능사용못함
      • 규칙 옵티마이저가 생긴 후 도입된 HASH JOIN 기능을 사용할 수 없다.



비용기반 옵티마이저 CBO

  • 대부분의 DBMS에서 사용한다.

  • 통계정보를 고려하여 옵티마이저에서 실행계획을 세운뒤 최소 비용의 실행계획을 수행한다.

  • 정량적인 데이터를 사용한다.

    • 비용을 예측하기 위해 규칙 기반 옵티마이저를 사용하지 않는 테이블, 인덱스 , 컬럼 등 다양한 객체통계정보, 시스템 통계정보를 사용한다.

  • 통계정보가 없는 경우, 비효율적인 실행계획을 세울 가능성이 높다.



비용기반 옵티마이저의 다양한 모드

  • CHOOSE: 통계없으면 규칙 기반사용하며 폐기되었다고 한다.
    • 잘 사용안한다.
    • 통계정보를 가져오지 못하는 경우 규칙 기반 옵티마이저를 작동시키는 모드
  • FIRST_ROWS :
    • 옵티마이저가 처리결과 중 첫 건을 출력하는데 시간을 최소화할 수 있는 실행계획을 세우는 모드
  • FIRST_ROWS(n) : 빠른 응답 중심
    • sql의 실행결과를 출력하는데 까지 걸리는 응답속도를 최적화하는 모드
  • ALL_ROWS:(기본값) : 전체 결과를 빠르게 처리
    • sql의 실행결과 전체를 빠르게 처리하는데 최적화된 실행계획을 세우는 모드
    • 마지막으로 출력될 행까지 최소한의 자원을 사용하여 최대한 빨리 가져오게 한다. 이 모드가 기본값으로 설정되어 있다.



옵티마이저 내부 구조

  • Query Transformer
    • SQL을 변환 : 서브쿼리 → 조인, view merging)
  • Estimator
    • 비용 계산
  • Plan Generator
    • 후보 실행 계획 생성 후, 최적 계획을 선택한다.
  • Row Source Generator → SQL Engine 실행


용어

  • Cardinality : 조건에 따라 반환되는 예상 행 수
  • 이 Cardinality가 실행계획 비용산정에서 많은 부분을 차지한다.



옵티마이저 동작방식

  1. 사용자가 SQL을 입력
  2. Parser가 SQL Parsing 수행: Parse가 입력돤 SQL을 분석하여 문법검사와 구성요소를 파악하여 이를 parsing하여 parsing Tree를 생성하고 Parsed SQL를 리턴
  3. Optimizer가 Optimization을 수행 : Optimization에는 3가지가 있다. 실행게획을 리턴
    1. Query Transformer
    2. Estimator
    3. Plan Generator
    • Query Transformer: 파싱된 Sql을 보고 더 나은 실행계획을 가질 수 있는지 판단하여 변환작업을 거친다.
    • Estimator : 시스템 통계정보를 딕셔너리로부터 수집하여 SQL을 수행할 때 비용을 계산
    • Plan Generator : Estimator를 통해 계산된값들을 토대로 후보군을 선출
  4. Row-Source Generator: 옵티마이저가 생성한 실행게뢱을 sql 엔진이 실행할 수 있는 코드나 프로시저형태로 변환한다. 리턴값은 row-source
  5. Sql Engine : sql engine아 sql을 실행한다.


통계정보

  • DBMS에서 제공하는 통계정보로 주기적으로 갱신한다.
  • 통계정보의 생성주기, 수행시간을 스케줄러에 일괄적으로 등록하여 일정주기마다 통계정보를 수집하고 종료하도록 자동 통계정보를 생성할 수 있고 사용자가 직접 수동으로 통계정보를 생성할 수 도 있다.

주요 통계정보

  • (테이블) 테이블 행 수, 테이블이 차지하는 블록개수, 테이블의 행 평균길이
  • (컬럼) 컬럼값종류, 컬럼내부 NULL 분포도, 컬럼값 평균 길이, 컬럼 내부 데이버 분포의 추정치
  • (인덱스) Leaf blk, level, 데이터밀집도
  • (시스템) IO성능과 사용률, CPU성능과 사용률



옵티마이저 한계

  • 상관관계 미반영
    • 다중 컬럼 조건절에서 통계는 독립 변수로 간주함
  • 동시성 미반영
    • 운영환경에서 동시쿼리가 많으면 실제비용과 큰차이
    • 실제로 동시쿼리가 많아서 실제비용과 큰차이를 보인다.
  • 히스토그램 한계
    • 버킷수가 제한됨
      • skewed 왜곡된 데이터 예측 어렵다.
  • 통계정보 노후화
    - 주기적으로 갱신이 팔요하다

추가 설명

  • 컬럼의 통게정보만 가지고 조건절에서 사용된 조건을 만족하는 데이터의 양이 어느정보인지 알 수 없으므로 비용계산결과가 부정확할 수 있다.
  • 비용 산정시 쿼리문이 단독으로 실행된다고 가정하기에는 운영서버와 쿼리가 동시에 실행된다면 실제 비용은 달라질 수 있으며 히스토그램버킷이 최대 254개까지만 사용할 수 있어서 그 이상의 값을 가지느 경우 결과가 부정확하다.



예상질문과 답변

Q. 옵티마이저란 무엇이고 어떤역할을 하는지

  • 옵티마이저는 sql을 실행할때 가장 효율적인 실행계획을 수립하는 DBMS의 핵심 엔진이다.
  • 사용자가 sql을 입력하면 옵티마이저는 통계정보를 바탕으로 여러 실행계획을 세우고 비교하여
  • 가장 적은 리소스로 결과를 반환할 수 있는 계획을 채택한다.

Q. 비용 기반 옵티마이저는 어떤 방식으로 실행계획을 세우냐

  • 테이블 행수, 인덱스 밀도같은 통계정보를 고려하여 각 실행계획의 예상 I/O와 CPU비용을 계산한다.
  • Query Transfomer → Estimator → Plan Generator 단계를 거쳐 최소 비용의 실행계획을 선정

Q. 옵티마이저가 잘못된 통게정보를 사용하는 경우에 대해 설명해라
  • 통계정보가 오래되어 데이터분포가 정확치 않은 경우
  • 다중 컬럼 조건에서 컬럼 간 상관관계가 존재하거나 옵티마이저는 이를 독립 변수로 가정한 경우
  • 값 분포가 skewed되었는데 히스토그램 버킷 수가 부족하여 정확한 분포를 반영하지 못한 경우

Q.옵티마이저에 영향을 줄 수 있는 방법은

  • 인덱스 생성, 제거
  • 힌트(/INDEX*/, /FULL, /**LEADING/)
  • 파티셔닝 설계
  • 서브쿼리에서 JOIN구조로 변환
  • 이 실행계획에 영향을 준다.



옵티마이저 튜닝시 주의할 점

  • 쿼리 튜닝전 실행게획 필수 분석
  • Cardinality 잘못추정하면, 특정 파라미터 조정을 고려해야한다.
  • 자주 사용하는 테이블이나 컬럼의 통계는 자동 수집 주기를 조정하거나 수동으로 보강해야한다.
  • 옵티마이저 힌트사요은 최후의 수단임.


옵티마이저 힌트란

  • 힌트는 옵티마이저에게 특정 실행계획을 강제하는 지시문이ㅏ다.
  • sql내부에서 /* asdf / 으로 사용(주석형식)
  • 예시 :
    SELECT /*+ INDEX(emp emp_idx_deptno) */ * 
FROM emp 
WHERE deptno = 10;
  • 종류
힌트설명
FULL(table)테이블을 Full Table Scan으로 읽도록 지시
INDEX(table index_name)특정 인덱스를 사용하도록 유도
NO_INDEX(table index_name)특정 인덱스 사용 금지
USE_NL(table)Nested Loop Join 사용
USE_HASH(table)Hash Join 사용
LEADING(table1 table2)조인 순서를 지정 (왼쪽부터 먼저 읽음)
MERGE(table)View Merge 유도
PARALLEL(table N)병렬 처리 사용 (N은 병렬 Degree)



EXPLAIN PLAN 실행계획

SQL 실행 계획을 확인하여 성능 병목 구간을 파악해야한다.

  • Table Access Full
    • 전체 테이블 스캔
  • Tabel Access By Index Rowid
    • 인덱스로 접근 후 테이블 데이터를 조회
  • Index Range Scan
    • 조건에 맞는 범위만 인덱스로 스캔
  • Index Full Scan
    • 무조건 인덱스를 처음부터 끝까지 스캔
  • Hash Join
    • 메모리 해시 테이블을 생성 후 조인
    • 대규모 데이터에 적합
  • Merge Join
    - 정렬된 데이터를 병합하며 조인


실행계획 튜닝

예시 1: 인덱스 누락으로 fulll scan 발생하는 경우 → mail 컬럼에 인덱스를 생성하여 해결

-- 문제
SELECT * FROM users WHERE email = 'abc@example.com';

-- 해결
-- 1. email 컬럼에 인덱스 생성
CREATE INDEX idx_users_email ON users(email);
-- 2. 힌트 추가 (선택)
SELECT /*+ INDEX(users idx_users_email) */ * FROM users WHERE email = 'abc@example.com';

문제설명

  • users 테이블의 email 컬럼은 자주 조회되는 검색 조건이지만, 인덱스가 없음
  • 옵티마이저는 이 쿼리를 실행할 때 TABLE ACCESS FULL 방식으로 처리
  • 즉, 전체 테이블을 스캔하며 조건에 맞는 행을 찾음 → 성능 저하

예시2 : 조인 순서로 인한 성능저하 → `customers` 테이블에서 `grade = 'VIP'` 조건을 먼저 적용해 소수의 VIP 고객만 추출 
-- 문제: 대용량 테이블 먼저 읽음
SELECT *
FROM orders o JOIN customers c ON o.customer_id = c.id
WHERE c.grade = 'VIP';

-- 해결: 조인 순서 조정 힌트
SELECT /*+ LEADING(c o) USE_NL(o) */ *
FROM customers c JOIN orders o ON o.customer_id = c.id
WHERE c.grade = 'VIP';

문제설명

  • 옵티마이저가 orders 테이블을 먼저 읽는 조인 순서를 택하면, 모든 주문에 대해 고객 테이블을 조인 → 비효율
  • 특히 orders가 수백만 건이고, VIP 고객이 적은 경우 불필요한 연산이 많아짐

해결방법

  • customers를 먼저 필터링해서 VIP 고객만 추린 후 조인
  • 힌트를 사용하여 조인 순서를 강제하고, 조인 방식을 NESTED LOOP로 유도

예시3 : Hash join강제 → HASH JOIN을 유도하여 한쪽 테이블을 메모리 해시 테이블로 구성, 다른 테이블과 조인

-- 큰 테이블 간 조인 시
SELECT /*+ USE_HASH(a) */ *
FROM large_a a JOIN large_b b ON a.key = b.key;

문제 설명

  • NESTED LOOP JOIN은 한쪽 테이블의 각 행마다 다른 테이블을 스캔 → 비효율적
  • 특히 인덱스 미사용 또는 양쪽 테이블이 커서 인덱스 효용이 없을 때 발생



정리

  • 힌트는 옵티마이저의 실행 계획 선택에 직접적인 영향
  • EXPLAIN PLAN은 성능 문제 진단의 기초
  • 인덱스, 조인 순서, Join 방식 튜닝이 실무 튜닝의 핵심 포인트
profile
[verify$y]
welcome
이전 포스트

[DB] 공유락, 배타락

다음 포스트

[DB] 내부조인, 외부조인

0개의 댓글