SQL튜닝 이론 - 트랜잭션 최적화를 위한 DBMS 옵티마이저 작동원리 이해하기(비용기반 옵티마이저, 규칙기반옵티마이저, ALL_ROWS, FIRST_ROWS_N)

SQL튜닝 이론 - 트랜잭션 최적화를 위한 DBMS 옵티마이저 작동원리 이해하기(비용기반 옵티마이저, 규칙기반옵티마이저, ALL_ROWS, FIRST_ROWS_N)

 

옵티마이저의 종류

 

옵티마이저는 비용기반 옵티마이저와 규칙기반 옵티마이저로 구분할 수 있습니다.

 

비용기반 옵티마이저(이하 CBO)는 쿼리 최적화를 위해 후보군이 될만한 실행계획을 도출하고, 데이터 딕셔너리에 수집해 둔 통계정보를 이용해 각 실행계획의 예상비용을 산정한 후, 그중 가장 낮은 비용의 실행 계획 하나를 선택하는 옵티마이저입니다. CBO 사용하는 통계정보로는 데이터량, 컬럼 값의 수, 컬럼 값 분포, 인덱스 높이, 클러스터링 팩터 등이 있습니다.

 

규칙기반옵티마이저(이하 RBO) 는 액세스 경로에 대한 우선순위규칙에따라 실행계획을 만듭니다. RBO는 데이터 특성을 나타내는 통계정보를 활용하지 않고 규칙에만 의존하기 때문에 대량 데이터를 처리하는데 한계가 있습니다. 예를 들어 테이블에 인덱스가 있으면 무조건 사용합니다. FULL TABLE SCAN 은 INDEX RANGE SCAN 보다 순위가 낮기 때문입니다. 하지만 조건을 만족하는 레코드가 전체 레코드

의 90% 이상이라면 인덱스 스캔은 좋은 선택으로 볼 수 없습니다.

 

SELECT *
FROM 고객
ORDER BY 고객명;

 

RBO는 정렬연산에 무조건 인덱스를 사용합니다. 인덱스 컬럼에 대한 ORDER BY 순위가 FULL TABLE SCAN 순위보다 높기 때문입니다. 부분범위 처리가 가능한 상항에서 인덱스를 통해 정렬을 생략하면 성능을 높이는데 도움이 되지만, 전체범위를 인덱스로 엑세스 하는 것은 좋은 선택이 아닙니다. CBO는 전체범위처리(ALL_ROWS) 와 부분범위처리(FIRST_ROWS)에 대해 별도의 옵티마이저 모드를 두고있습니다.

 

RBO에서는 인덱스 칼럼에 대한 BETWEEN 조건이 부등호 조건보다 우선순위가 높습니다. 이래의 쿼리에서 RBO 의 실행계획은 비효율을 유발합니다.

 

select *
from 사원
where 연령 >= 60
and 연봉 between 3000 and 6000

 

데이터 특성상 연령이 60대인 사람이 연봉 3000에서 6000 사이의 사람보다 적을 수 밖에 없는데 RBO는 이를 고려하지 못합니다.

옵티마이저 모드

 

RBO는 조회범위에 따라 3가지 유형의 옵티마이저 모드를 제공합니다.

 

- ALL_ROWS : 전체 처리속도 최적화

- FIRST_ROWS : 최초 응답속도 최적화

- FIRST_ROWS_N : 최초 N건 응답속도 최적화

 

옵티마이저 모드를 ALL_ROWS 로 설정하면 옵티마이저는 쿼리 결과집합 전체를 읽는 목적에 최적화된 실행계획을 선택합니다. 옵티마이저 모드를 FIRST_ROWS 로 설정하면 옵티마이저는 쿼리 결과집합 중 앞쪽 일부를 읽는 목적에 최적화된 실행계획을 선택합니다. 옵티마이저 모드를 FIRST_ROWS_N으로 설정하면 옵티마이저는 쿼리 결과집합 중 사용자가 지정한 N개를 읽는 목적에 최적화된 실행계획을 선택합니다.

 

FIRST_ROWS를 ALL_ROW와 비교하면, TABLE FULL SCAN 보다 인덱스 스캔을 더 많이 선택하고 해시조인, 소트머지조인보다 NL조인을 더 많이 선택하는 경향이 있습니다. FIRST_ROWS 모드로 레코드를 끝까지 읽는다면 FULL SCAN에 비해 오히려 전체 수행 속도가 더 느려지게됩니다.

 

FIRST_ROWS는 곧 DEPRECATED 될 모드로 FIRST_ROWS_N 모드를 사용해야 합니다.  ALTER SYSEM OR ALER SESSION 명령어로 옵티마이저 모드를 지정할 때 가능한 N 값은 [1,10,100,1000] 네 가지 입니다.

 

alter session set optimizer_mode = first_row_1;
alter session set optimizer_mode = first_row_10;
alter session set optimizer_mode = first_row_100;
alter session set optimizer_mode = first_row_1000;

 

아래와 같이 FIRST_ROWS 힌트로 설정할 때는 괄호 안에 0 보다 큰 어떤 정수값이라도 입력할 수 있습니다.

 

select /*+first_rows(30)*/ col1, col2, col3 from t where...

 

FIRST_ROWS는 사용자가 데이터를 어느 정도 읽다가 멈출지를 지정하지 않았으므로 정확한 비용을 산정하기 어렵습니다. 반면 FIRST_ROWS_N은 읽을 데이터 건수를 지정하였으므로 더 정확한 비용 산정이 가능합니다. FIRST_ROWS_N은 FIRST_ROW에 비해 더 완벽한 CBO 모드로 작동합니다.

 

 옵티마이저에 영향을 주는 요소

 

SQL과 연산자의 형태

 

결과가 같더라도 SQL을 어떤 형태로 작성했는지에 따라 옵티마이저의 선택이 달라집니다. 옵티마이저의 선택은 쿼리의 성능에 영향을 줍니다.

 

인덱스, IOT, 클러스터, 파티션, MV 등 옵티마이징 팩터

 

옵티마이징 팩터를 어떻게 구성했는지에 따라 실행계획과 성능이 크게 달라집니다.

 

제약설정

 

테이블에 설정한 PK, FK, CHECK, NOT NULL 제약들은 데이터 무결성을 보장해줄 뿐아니라 옵티마이저가 쿼리 성능을 최저화하는데 중요한 메타정보로 활용됩니다.

 

통계정보

 

통계정보는 옵티마이저에 큰 영향을 줍니다. 옵티마이저가 사용하는 통계정보에 문제가 생기면 성능이 크게 저하됩니다. 성능 저하를 유발하는 상황은 다음과 같습니다.

 

- 특정 테이블 통계정보를 갑자기 삭제한다.
- 대량 데이터를 지웠다가 다시 입력하기 직전, 데이터가 없는 상황에서 DBMS가 통계정보를 수집한다.
- 오랫동안 갱신하지 않던 특정 테이블의 통계정보를 어느날 갑자기 재수집한다.
- 통계정보 없이 관리하던 테이블에 인덱스를 재생성한다.
- 테이블이나 인덱스를 재생성하면서 파티션 단위로만 통계정보를 수집한다.

 

옵티마이저 최적화 방안

 

최소한의 블록만 읽도록 쿼리 작성

 

데이터베이스의 성능은 I/O 효율에 의해 좌우됩니다. 동일한 레코드를 반복적으로 읽지 않고 최소블록만 읽도록 쿼리를 작성합니다.

 

SELECT ROWNUL AS NO, 등록일자, 번호, 제목, 회원명, 게시판유형명, 질문유형명, 아이콘, 댓글수
FROM (
    SELECT
    A.등록일자, A.번호, A.제목, B.회원명, C.게시판유형명, D.질문유형명
    , GET_ICON(D.질문유형코드) AS 아이콘, (SELECT ... FROM ...) 댓글수
    FROM 게시판 A, 회원 B, 게시판유형 C, 질문유형 D
      WHERE A.게시판유형 = :TYPE
      AND B.회원번호 = A.회원번호
      AND C.게시판유형 = A.게시판유형
      AND D.질문유형 = A.질문유형
      ORDER BY  A.등록일자 DESC, A.질문유형, A.번호
)
WHERE ROWNUM <= (:PAGE * 10)

 

위 쿼리의 성능저해요소는 다음과 같습니다.

 

- 모든 조인레코드에 대해 사용자정의함수와 스칼라 서브쿼리를 수행합니다.
- 모든 레코드에 대해 조인할 필요없이 출력 대상을 확정하고 조인하는 것이 효율적입니다.

 

쿼리를 다음과 같이 개선할 수 있습니다.

 

SELECT *
FROM (
  SELECT A.등록일자, A.번호, A.제목, B.회원명, C.게시판유형명, D.질문유형명,
  GET_ICON(D.질문유형코드) 아이콘, (select...from ...) 댓글수
  FROM 
  (
    SELECT A.*, ROWNUM NO
    FROM
    (
      SELECT 등록일자, 번호, 제목, 회원명, 게시판유형명, 질문유형명
      FROM 게시판
      WHERE A.게시판유형 = :TYPE
      AND 작성자번호 IS NOT NULL
      AND 게시판유형 IS NOT NULL
      AND A.질문유형 IS NOT NULL
      ORDER BY 등록일자 DESC, 질문유형, 번호
    ) A
    WHERE ROWNUM <= (:PAGE * 10)
  )A, 회원 B, 게시판유형 C, 질문유형 D
  WHERE A.NO >= (:PAGE - 1) * 10 + 1
  AND B.회원번호 = A.작성자번호
  AND C.게시판유형 = A.게시판유형
  AND D.질문유형 = A.질문유형
  ORDER BY A.등록일자 DESC, A.질문유형, A.번호

)
WHERE NO >= (:PAGE - 1) * 10 + 1

 

최적의 옵티마이징 팩터 제공

 

쿼리의 최적화를 수행하는 것은 옵티마이저지만, 이를 위해 적절한 수단을 제공하는 것은 사용자의 몫입니다. 적절한 옵티마이징 팩터 제공을 통해 옵티마이저가 빠른 경로를 찾을 수 있는 환경을 제공해야 합니다. 대표적인 옵티마이징 팩터는 다음과 같습니다.

 

- 전략적 인덱스 구성
- DMBS 제공 기능 활용
- 옵티마이저 모드 설정
- 정확하고 안정적인 통계정보 활용