옵티마이저

• 옵티마이저의 동작 과정

  1. 사용자가 SQL 입력
     • 사용자가 SQL을 실행 하면 DBMS 내부에서 SQL이 처리되는 과정 시작
  2. 파서(Parser) 단계
     • 사용자의 SQL 문법을 검사하고 내부적인 구문 트리(Parse Tree)로 변환하는 과정
     • 오타, 문법 오류 등이 있는지 검사하며 오류가 없다면 다음 단계 진행
  3. 옵티마이저(Optimizer) 단계
     • SQL을 실행하기 전에 가장 효율적인 실행 계획을 수립하는 단계
  4. 실행 계획(Execution Plan) 수립 및 SQL 실행
     • 옵티마이저가 생성한 계획을 SQL 실행 엔진(SQL Execution Engine)이 수행
     • 이 단계에서 실제 데이터 검색 및 조작이 이루어짐
     • 실행된 SQL의 결과는 다시 사용자에게 반환

• 최적화란 행동을 할 때, 어떻게 할 것인지를 정하는 것.

  • 성능 모델링과는 다르다. SQL문을 실행할 때, 어떻게 하면 효율적으로 할 수 있을지에 대한 고민이 최적화이며, 최적화를 하는 기계가 옵티마이저.
  • 옵티마이저의 핵심은 SQL문을 실행하기 “전”에 최적의 실행 계획을 찾는 것.

    • 단, 옵티마이저가 항상 최적의 실행 계획을 선택하는 것은 아니다. 따라서 SQL 힌트(Hint) 를 사용하여 강제로 특정 계획을 유도할 수 있다.

      SELECT /*+ FULL(emp) */ * FROM employees emp;
      

    • FULL(테이블명) → 강제적으로 Full Table Scan 실행

    • INDEX(테이블명, 인덱스명) → 특정 인덱스를 사용하도록 강제

    • LEADING(테이블명) → 조인의 선행 테이블을 지정

    • ORDERED → 조인의 순서를 지정

    • USE_NL(테이블명) → Nested Loop Join을 강제

• 옵티마이저는 하나의 프로세스인 만큼, 시행하는데 시간이 걸린다. 비용을 계산하는 과정을 감수할 필요가 있을 때 시행하는 것. 인덱스와 유사한 의미로 생각하자.

• 규칙 기반 옵티마이저

  

  • RBO, Rule-Based Optimizer
  • 규칙에 기반하여 옵티마이저를 구동한다.
  • 규칙기반 옵티마이저는 이미 규칙이 규정되어있으며, 그 규칙에 맞춰서 시행된다.
  • 오라클 9i 이전 버전에서 사용되었으며, 10g 이후 버전에서는 공식적으로 지원되지 않는다.

• 비용 기반 옵티마이저

  • CBO, Cost-Based Optimizer

  • 비용 기반 옵티마이저는 통계 정보(Statistics)에 기반해서 시행된다.

  • CBO는 실행 계획을 평가할 때 비용(Cost) 개념을 사용한다.

    • 딕셔너리(Dictionary)에 저장된 테이블 및 인덱스의 통계 정보를 사용하여 최적의 계획 결정
    • 실행 계획 수립에는 다음과 같은 정보 활용
      1. 통계 정보(Statistics)
         • 테이블의 행(Row) 개수
         • 특정 컬럼의 데이터 분포도
         • 인덱스 존재 여부
      2. 조인 방식 결정
         • Nested Loop Join
         • Sort Merge Join
         • Hash Join
      3. 액세스 방법 결정
         • Full Table Scan
         • Index Scan
         • Bitmap Index 사용 여부

  • 따라서 통계에 기반하여 비용이 많이 드는 방식과 적게 드는 방식을 선정, 적게 드는 방식으로 진행된다.

  • 구조

    

    • 질의 변환기 : SQL문을 보다 작업이 용이한 형태로 변환
    • 대안 계획 생성기 : 동일한 결과를 생성하는 다양한 대안 계획 생성
      • 가능한 모든 계획을 생성하지는 않으므로(리소스 낭비), 최적 대안이 누락되는 경우도 있음
    • 비용 예측기 : 다양한 통계정보를 활용하여 대안 계획의 비용 예측

• 실행 계획

  

  • 조인 기법 : NL Join, Sort Merge Join, Hash Join등을 사용한다.
  • 엑세스 기법 : Index Scan, Full Table Scan의 방식으로 인덱스 스캔
  • 최적화 정보 : → 최적화 정보는 실제 실행 결과가 아닌, 통계 정보(딕셔너리)바탕의 예측치다. → Cost : 상대적인 비용 정보 (숫자가 낮을수록 유리) → Card : 주어진 조건을 만족하는 행의 수 (숫자가 낮을수록 유리) → Bytes : 결과 집합이 차지하는 메모리의 양 (숫자가 낮을수록 유리)

인덱스

• 인덱스는 검색 속도 향상을 위한 기술이자 일종의 책갈피와 같은 역할을 한다.

• 데이터 - ROWID(주소) 쌍으로 저장된다.

• B-Tree 인덱스

  

  • 일치(=)검색과 범위(BETWEEN, <>)검색에 모두 적합하다.
  • 테이블의 어느 데이터에 접근하더라도 동일한 성능을 보장한다.
  • 루트 노드로부터 이진 탐색 알고리즘을 활용하여 노드를 확장, 탐색한다.
  • 데이터의 DML 작업이 복잡하다.
  • 데이터의 경합 상황(Contention)이 발생하기 쉽다.
    • 경합 상황 발생하는 경우
      1. 순차적으로 증가하는 값으로 인덱스를 구성할 때 (AUTO_INCREMENT, TIMESTAMP, SEQUENCE)
      2. 동시 삽입/삭제가 많은 경우
      3. B-Tree 리밸런싱(Rebalancing)으로 인한 성능 저하

• 비트맵 인덱스

  

  • 비트맵 인덱스는 인덱스 키 값 + 시작 ROW_ID + 끝 ROW_ID + 비트맵 엔트리로 구성
  • 비트를 이용하여 컬럼값을 저장하고, ROW_ID를 자동으로 생성
  • 분포도가 낮은 컬럼에 적용할 때 특히 유용 (성별, 결혼여부 등)
  • OR 연산자를 포함하는 여러 개의 WHERE 조건을 자주 사용할 때 유리
  • 이는 비트 연산을 통해 결과를 빠르게 병합할 수 있기 때문에 JOIN 성능 향상에 기여할 수 있기 때문
  • DML 작업에서는 무의미.
  • 비트를 직접 관리하므로 저장공간이 크게 감소하고 비트 연산 수행 가능

• REVERSE KEY 인덱스

  

  • B-TREE 인덱스와 거의 유사하지만 인덱스 키 값은 반대로 구성, B-TREE 인덱스를 생성
  • 구조는 B-TREE 인덱스와 같고 단지 저장되는 데이터만 역으로 리프블록에 저장
  • 순차적으로 증가하는 데이터의 경우 일반적인 B-TREE 인덱스는 우측 리프 블록으로 데이터 경합이 발생하는데, 리버스 키 인덱스의 경우 모든 인덱스 블록으로 추가가 발생하여 경합 상황을 방지할 수 있다.

예제

테이블 구조

C_CD	VARCHAR2(20 BYTE)	No		1	회사코드
EMP_ID	VARCHAR2(20 BYTE)	No		2	사번
STAT_CD	VARCHAR2(20 BYTE)	Yes		3	재직상태
ENTER_TYPE	VARCHAR2(20 BYTE)	Yes		4	입사유형
BIZPL_CD	VARCHAR2(20 BYTE)	Yes		5	사업장코드
RE_GRD_CD	VARCHAR2(20 BYTE)	Yes		6	채용등급코드
ENTER_YMD	VARCHAR2(8 BYTE)	Yes		7	입사일
GRP_YMD	VARCHAR2(8 BYTE)	Yes		8	그룹입사일
RETIRE_YMD	VARCHAR2(20 BYTE)	Yes		9	퇴사일
PER_NO	VARCHAR2(36 BYTE)	Yes		10	주민번호
GENDER_TYPE	VARCHAR2(20 BYTE)	Yes		11	성별유형
EMP_NM	VARCHAR2(300 BYTE)	Yes		12	사원명
CHA_EMP_NM	VARCHAR2(300 BYTE)	Yes		13	한자사원명
ENG_EMP_NM	VARCHAR2(300 BYTE)	Yes		14	영문사원명
BIRTH_YMD	VARCHAR2(8 BYTE)	Yes		15	생일
LNS_TYPE	VARCHAR2(20 BYTE)	Yes		16	음양유형
MARRY_YN	VARCHAR2(1 BYTE)	Yes		17	결혼여부
MARRY_YMD	VARCHAR2(20 BYTE)	Yes		18	결혼일
NTNL_CD	VARCHAR2(20 BYTE)	Yes		19	국가코드
OFFICE_TEL_NO	VARCHAR2(300 BYTE)	Yes		20	구내전화번호
TEL_NO	VARCHAR2(300 BYTE)	Yes		21	전화번호
EGC_TEL_NO	VARCHAR2(300 BYTE)	Yes		22	긴급전화번호
MOBILE_NO	VARCHAR2(300 BYTE)	Yes		23	이동전화
MAIL_ADDR	VARCHAR2(300 BYTE)	Yes		24	메일주소
PHOTO_PATH	VARCHAR2(300 BYTE)	Yes		25	사진PATH
LOC_CD	VARCHAR2(20 BYTE)	Yes		26	지역코드
INS_USER_ID	VARCHAR2(20 BYTE)	Yes		27	입력자ID
INS_YMDHMS	TIMESTAMP(6)	Yes		28	입력일시
MOD_USER_ID	VARCHAR2(20 BYTE)	Yes		29	작업자ID
MOD_YMDHMS	TIMESTAMP(6)	Yes		30	수정일시
INS_GYMDHMS	TIMESTAMP(6) WITH TIME ZONE	Yes		31	입력일시_현지
MOD_GYMDHMS	TIMESTAMP(6) WITH TIME ZONE	Yes		32	수정일시_현지
TIMEZONECD	VARCHAR2(20 BYTE)	Yes		33	TIMEZONE코드

• RowData : 500만 건
• 입력 데이터 예시