윤정호

문과 출신 이과형 인간.

2 분 소요

Optimizer

  • 옵티마이저는 SQL을 어떻게 실행할 것인지를 계획 한다.
  • 실행 계획을 수립하고 SQL을 실행하는 DBMS의 소프트웨어로서 DBMS의 심장부라 할 수 있다.
  • 동일한 결과가 나오는 실행 계획에 따라 성능이 달라지기 때문에 옵티마이저는 더욱 중요한 역할을 한다.
  • 옵티마이저는 데이터 딕셔너리에 있는 오브젝트 통계, 시트템 통계 등의 정보를 사용해서 예상되는 비용을 산정한다.
  • 여러 개의 실행 계획 중 최저 비용을 선택한다.
  • 옵티마이저가 비효율적인 실행 계획을 수립하면 SQL문을 수정한다거나 HINT를 준다.

옵티마이저 실행 계획 확인

  • 옵티마이저는 실행 계획을 PLAN_TABLE에 저장한다.
DESC PLAN_TABLE;
  • 또는 TOAD에서 실행계획을 확인 할 수 있다.

옵티마이저 종류

우선 종류를 알기 전에 SQL문이 어떻게 실행되는지 알아보자

  1. 개발자 SQL 작성
  2. Parsing (SQL 문법 검사, 구문 분석)
  3. OPTIMIZER
  4. 실행계획
  5. SQL 실행

의 순서로 실행된다.

옵티마이저는 파싱 이후에 비용 기반 또는 규칙 기반으로 실행 계획을 수립하게 된다.

규칙 기반 옵티마이저

규칙 기반 옵티마이저는 실행 계획을 수립할 때 15개의 우선순위를 기준으로 실행 계획을 수립한다.

요즘은 거의 사용하지 않는 방식이다.

옵티마이저우선순위

이미지출처

일반적인 상황에선 옵티마이저는 자동으로 비용기반으로 작업을 수행하기 때문에 규칙기반은 힌트 라는 것을 주어야 한다.

SELECT /*+ RULE */ * FROM 테이블 WHERE 조건;

위의 코드처럼 /*+ RULE */ 이라고 힌트를 주게 되면 옵티마이저는 규칙 기반으로 작업을 수행한다.

비용 기반 옵티마이저

비용 기반 옵티마이저는 오브젝트 통계 및 시스템 통계를 사용해서 총 비용을 계산한다.

SQL문을 실행하기 위해서 예상되는 소요시간or 자원의 사용량을 파악하여 가장 저렴한 쪽으로 계획을 수립한다.

통계를 기반으로 비용을 측정하기 때문에 통계 정보가 부적절할 경우 성능 저하가 발생할 수 있다.

인덱스

  • 인덱스는 데이터를 빠르게 검색할 수 있는 방법을 제공한다.
  • 인덱스 키로 정렬되어 있으며 오름차순 및 내림차순 탐색이 가능하다.
  • 하나의 테이블에 여러 개의 인덱스를 생성할 수 있고 하나의 인덱스는 여러개의 칼럼으로 구성될 수 있다.
  • 테이블을 생성할 때 Primary key는 자동으로 인덱스가 만들어진다.
  • 인덱스의 구조는 Root Block, Branch Block, Leaf Block으로 구성되어 있다.
  • 각 노드는 다음 단계의 주소를 가지고 있는 포인터로 되어있다.

B트리

이미지출처

Index 생성

  • 인덱스 생성은 CREATE INDEX 문을 사용해서 생성할 수 있다.
  • 한 개 이상의 칼럼을 사용해서 생성해야 한다.
  • 기본적으로 오름차순으로 정렬한다.
CREATE INDEX 인덱스이름 ON 테이블명 (칼럼명 정렬방식,칼럼명 정렬방식);

Index 스캔

  • 유일 스캔
    • 인덱스의 키 값이 중복되지 않는 경우, 해당 인덱스를 사용할 때 발생된다.
    • 중복되지 않을 때 ‘=’ 을 이용한다.
  • 범위 스캔
    • SELECT문에서 특정 범위를 조회하는 WHERE문을 사용할 경우 발생된다.
    • LIKE, BETWEEN 이 대표적이다.
    • 데이터의 양이 적은 경우는 TABLE FULL SCAN이 될 수 있다.
    • 범위 스캔은 Leaf Block의 특정 범위를 스캔한 것이다.
  • 전체 스캔
  • 전체 스캔은 인덱스에서 검색되는 인덱스 키가 많은 경우, Leaf Block의 처음부터 끝까지 전체를 읽는다.

옵티마이저 조인

Nested Loop 조인

  • Nested Loop 조인은 하나의 테이블에서 데이터를 먼저 찾고 그 다음 테이블을 조인하는 방식이다.
  • 먼저 조회 되는 것을 외부 테이블(Outer), 나중에 조회 되는 것을 내부 테이블(Inner)이라 부른다.
  • 외부테이블의 크기 작은 것이 스캔되는 범위가 적다.
  • Nested Loop 조인은 Random Access가 발생하는데 많을 수록 성능이 저하된다.
SELECT /*+ ordered use_nl(b) */ *
FROM 테이블 a. 테이블 b,
WHERE a.칼럼 = b.칼럼
AND a.칼럼 = 조건값;

위 코드처럼 힌트를 사용하여 Nested Loop 조인을 하게 만들 수 있다.

Sort Merge 조인

  • Sort Merge 조인은 두 개의 테이블을 SORT_AREA라는 메모리 공간에 모두 로딩하고 SORT를 수행한다.
  • Sort가 완료되면 두 개의 테이블을 병합(Merge)한다.
  • 정렬이 발생하기 때문에 데이터가 많을 수록 성능이 떨어진다.
  • 데이터 양이 너무 많으면 임시 영역에서 수행되며, 이는 디스크에서 실행 되기 때문에 성능이 급격히 떨어진다.

Hash 조인

  • Hash 조인은 두 개의 테이블 중에서 작은 테이블을 HASH 메모리에 로딩하고 두 개의 테이블의 조인 키를 사용해서 해시 테이블을 생성한다.
  • 해시 함수를 사용해서 주소를 계산하고 해당 주소를 사용해서 테이블을 조인하기 때문에 CPU 연산을 많이 한다.
  • 선행 테이블이 충분히 메모리에 로딩되는 크기여야 한다.

댓글남기기

참고