[1] 옵티마이저 소개

(1) 옵티마이저(Optimizer)란?

• SQL을 가장 빠르고 효율적으로 수행할 최적(최저비용)의 처리경로를 생성해 주는 DBMS 내부 핵심엔진
• 사용자가 구조화된 질의언어(SQL)로 결과집합 요구시, 이를 생성하는데 필요한 처리경로를 옵티마이저가 자동으로 생성
• 실행계획(Execution Plan) : 옵티마이저가 생성한 SQL 처리경로

  ✅ 옵티마이저의 SQL 최적화 과정 (비용기반 옵티마이저 기준)

  1. 사용자가 던진 쿼리수행을 위해, 후보군이 될만한 실행계획 검색
  2. Data Dictionary에 미리 수집해 놓은 오브젝트 통계 및 시스템 통계정보를 이용해 각 실행계획의 예상비용 산정
  3. 각 실행계획을 비교해서 최저비용을 갖는 하나를 선택

(2) 옵티마이저 종류

1. 규칙기반 옵티마이저(Rule-Based Optimizer, 이하 RBO)

• 다른 말로 ‘휴리스틱(Heuristic) 옵티마이저’라고 불리며,
• 미리 정해 놓은 규칙에 따라 액세스 경로를 평가하고 실행계획 선택.

  미리 정해 놓은 규칙이란?

  • ✅ 액세스 경로별 우선순위
  • 인덱스 구조, 연산자, 조건절 형태가 순위를 결정짓는 주요인이 된다.

2. 비용기반 옵티마이저(Cost-Based Optimizer, 이하 CBO)

• ‘비용(Cost)’이란, 쿼리를 수행하는데 소요되는 일량 또는 시간
• 말 그대로 비용을 기반으로 최적화를 수행
• CBO가 실행계획을 수립할 때 판단 기준이 되는 비용은 어디까지나 예상치이다.
  • 미리 구해놓은 테이블과 인덱스에 대한 여러 통계정보를 기초로 각 오퍼레이션 단계별 예상 비용을 산정하고, 이를 합산한 총비용이 가장 낮은 실행계획을 선택
  • 비용을 산정할 때 사용되는 오브젝트 통계 항목으로는 레코드 개수, 블록 개수, 평균 행 길이, 칼럼 값의 수, 칼럼 값 분포, 인덱스 높이(Height), 클러스터링 팩터 같은 것들이 있음
• 오브젝트 통계뿐만 아니라 최근에는 하드웨어적 특성을 반영한 시스템 통계정보(CPU 속도, 디스크 I/O 속도 등)까지 이용
• 역사가 오래된 Oracle은 RBO에서 출발하였으나 다른 상용 RDBMS는 탄생 초기부터 CBO 채택했다.

  ✅ 오브젝트 통계 항목

  • 레코드 개수
  • 블록 개수
  • 편균 행 길이
  • 칼럼 값의 수
  • 칼럼 값의 분포
  • 인덱스 높이
  • 클러스터링 팩터
  • 시스템 통계정보( CPU 속도, 디스크 I/O 속도 등 )

(3) SQL 최적화 과정

✅ 옵티마이저의 SQL 최적화 과정 (비용기반 옵티마이저 기준)

1. 사용자가 던진 쿼리수행을 위해, 후보군이 될만한 실행계획 검색
2. Data Dictionary에 미리 수집해 놓은 오브젝트 통계 및 시스템 통계정보를 이용해 각 실행계획의 예상비용 산정
3. 각 실행계획을 비교해서 최저비용을 갖는 하나를 선택

역할

• Parser : SQL문장을 이루는 개별 구성요소를 분석하고 파싱해서 파싱 트리를 만든다.( Syntax(문법), Semantic(의미) )
• Query Transformer : 파싱된 SQL을 좀 더 일반적이고 표준적인 형태로 변환한다.
• Estimator : 오브젝트 및 시스템 통계정보를 이용해 쿼리 수행 각 단계의 선택도, 카디널리티, 비용을 계산하고, 궁극적으로는
  실행계획 전체에 대한 총비용을 계산해 낸다.
• Plan Generator : 하나의 쿼리를 수행하는데 있어, 후보군이 될만한 실행계획들을 생성해 낸다.
• Row-Source Generator : 옵티마이저가 생성한 실행계획을 SQL 엔진이 실제 실행할 수 있는 코드(또는 프로시저 ) 형태로 포맷팅한다.
• SQL Engine : SQL을 실행한다.

(4) 최적화 목표

1. 전체 처리속도 최적화

• 쿼리 최종 결과집합을 끝까지 읽는 것을 전제로, 시스템 리소스(I/O, CPU, 메모리 등)를 가장 적게 사용하는 실행계획을 선택한다.
• Oracle, SQL Server 등을 포함해 대부분 DBMS의 기본 옵티마이저 모드는 전체 처리속도 최적화에 맞춰져 있다.

ALTER SYSTEM SET OPTIMIZER_MODE = ALL_ROWS;	--시스템 레벨
ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS;	--세션 레벨
SELECT /*+ ALL_ROWS */ * FROM T WHERE ... ;	--SQL 레벨

2. 최초 응답속도 최적화

• 전체 결과집합 중 일부만 읽다가 멈추는 것을 전제로, 가장 빠른 응답 속도를 낼 수 있는 실행계획을 선택
• Oracle 옵티마이저에게 최초 응답속도 최적화를 요구하려면, 옵티마이저 모드를 first_rows 사용
• SQL 서버 에서는 테이블 힌트로 fastfirstrow 지정
• Oracle에서 옵티마이저 모드를 first_rows_n으로 지정 시, 사용자가 전체 결과집합 중 처음 n개 로우만 읽고 멈추는 것을 전제로 가장 빠른 응답 속도를 낼 수 있는 실행계획을 선택

ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_10;
SQL> ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_10;

세션이 변경되었습니다.

SQL> SHOW PARAMETER OPTIMIZER_MODE;

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
optimizer_mode                       string                 FIRST_ROWS_10

SELECT /*+ FIRST_ROWS(10) */ * FROM T WHERE ... ;

-- MS-SQL
SELECT * FROM T WHERE OPTION( fast 10 );

[2] 옵티마이저 행동에 영향을 미치는 요소

1. SQL과 연산자 형태

• 결과가 같더라도 SQL을 어떤 형태로 작성했는지 또는 어떤 연산자를 사용했는지에 따라 옵티마이저의 선택이
  변할 수 있다.
  ☞ 쿼리 성능에 영향을 줌

2. 옵티마이징 팩터

• 쿼리를 똑같이 작성하더라도 인덱스, IOT, 클러스터링, 파티셔닝, MV 등을 어떻게 구성했는지에 따라 실행계획과 성능이 크게 달라질 수 있다.

3. DBMS 제약 설정

• 개체 무결성, 참조 무결성, 도메인 무결성 등을 위해 DBMS가 제공하는 PK, FK, Check, Not Null 같은 제약 설정 기능을 이용할 수 있다.
• 이들 제약 설정은 옵티마이저가 쿼리 성능을 최적화하는 데에 매우 중요한 정보를 제공한다.
  • 예를 들어, 인덱스 칼럼에 Not Null 제약이 설정돼 있으면
    옵티마이저는 전체 개수를 구하는 Count 쿼리에 이 인덱스를 활용할 수 있다.

4. 옵티마이저 힌트

• ⭐️ 옵티마이저의 판단보다 사용자가 지정한 옵티마이저 힌트가 우선이다.

5. 통계정보

• 통계정보가 옵티마이저에게 미치는 영향력은 절대적이다.
• 통계정보는 CBO의 판단 기준이다.

6. 옵티마이저 관련 파라미터

• SQL, 데이터, 통계정보, 하드웨어 등 모든 환경이 동일하더라도 DBMS 버전을 업그레이드하면 옵티마이저가 다르게 작동할 수 있다.
  • 옵티마이저 관련 파라미터가 추가 또는 변경되면서 나타나는 현상이다.

7. DBMS 버전과 종류

• 옵티마이저 관련 파라미터가 같더라도 버전에 따라 실행계획이 다를 수 있다.
• 같은 SQL이더라도 DBMS 종류에 따라 내부적으로 처리하는 방식이 다를 수 있다.

[3] 옵티마이저의 한계

1. 옵티마이징 팩터의 부족

• 사용자가 적절한 옵티마이징 팩터를 제공하지 않는다면 결코 좋은 실행계획을 수립할 수 없다.

  ✅ 옵티마이징 팩터의 종류

  • 인덱스
  • IOT
  • 클러스터링
  • 파티셔닝 등

2. 통계정보의 부정확성

• 100% 정확한 통계정보를 유지하기는 현실적으로 불가능하다.
• 특히, 칼럼 분포가 고르지 않을 때 칼럼 히스토그램이 반드시 필요한데, 이를 수집하고 유지하는 비용이 많이 든다.

[예시] 직급과 월급의 관계

• 칼럼을 결합했을 때의 모든 결합 분포를 미리 구해두기 어려운 것도 큰 제약 중 하나다.
• 이는 상관관계에 있는 두 칼럼이 조건절에 사용될 때 옵티마이저가 잘못된 실행계획을 수립하게 만드는 주요인이다.

select * from 사원 where 직급 = '부장' and 연봉 >= 5000;

• 직급 {부장, 과장, 대리, 사원}의 집합으로 각각 25%의 비중을 갖는다.

• 전체 사원이 1,000명이고 히스토그램상 '연봉 >= 5000' 조건에 부합하는 사원 비중이 10%이므로
  ☞ 옵티마이저는 위 쿼리 조건에 해당하는 사원 수를 25(=1,000×0.25×0.1)명으로 추정한다.

• 하지만 잘 알다시피 직급과 연봉 간에는 상관관계가 매우 높아서,
  만약 모든 부장의 연봉이 5,000만원 이상이라면 실제 위 쿼리 결과는 250(=1,000×0.25×1)건이다.

• 이런 조건절에 대비해 모든 칼럼 간 상관관계와 결합 분포를 미리 저장해 두면 좋겠지만 이것은 거의 불가능에 가깝다.

• 테이블 칼럼이 많을수록 잠재적인 칼럼 조합의 수는 기하급수적으로 증가하기 때문이다.

3. ⭐️ 바인드 변수 사용 시 균등분포 가정

• 아무리 정확한 칼럼 히스토그램을 보유하더라도 바인드 변수를 사용한 SQL에는 무용지물이다.
  조건절에 바인드 변수를 사용하면 옵티마이저가 균등분포를 가정하고 비용을 계산하기 때문이다.

4. 비현실적인 가정

• 옵티마이저는 쿼리 수행 비용을 평가할 때 여러 가정을 사용하는데, 그 중 일부는 상당히 비현실적이어서 종종 이해할 수 없는 실행계획을 수립하곤 한다.
  • 예전 Oracle 버전에선 Single Block I/O와 Multiblock I/O의 비용을 같게 평가했다.
  • 데이터 블록의 캐싱 효과도 고려하지 않았다.
• DBMS 버전이 올라가면서 이런 비현실적인 가정들이 계속 보완되고 있지만 완벽하지는 않다.

5. 규칙에 의존하는 CBO

• 아무리 비용기반 옵티마이저라 하더라도 부분적으로는 규칙에 의존한다.
  • 최적화 목표를 최초 응답속도에 맞추면(Oracle에서는 optimizer_mode = first_rows)
    order by 소트를 대체할 인덱스가 있을 때 무조건 그 인덱스를 사용한다.
    이를 휴리스틱(Heuristic) 쿼리 변환이라고도 한다.

6. 하드웨어 성능

• 옵티마이저는 기본적으로 옵티마이저 개발팀이 사용한 하드웨어 사양에 맞춰져 있다.
• 따라서 실제 운영 시스템의 하드웨어 사양이 그것과 다를 때 옵티마이저가 잘못된 실행계획을 수립할 가능성이 높아진다.
• 또한 애플리케이션 특성(I/O 패턴, 부하 정도 등)에 의해서도 하드웨어 성능은 달라진다.

[4] 통계정보를 이용한 비용계산 원리

(1) 통계 정보

• 실행계획을 수립할 때 CBO는 SQL 문장에서 액세스할 데이터 특성을 고려하기 위해 통계정보를 이용한다.
• 최적의 실행계획을 위해 통계정보가 항상 데이터 상태를 정확하게 반영하고 있어야 한다.
• DBMS 버전이 올라갈수록 자동 통계관리 방식으로 전환했다.
  (하지만 가끔 DB 관리자의 수동 수집 관리가 필요하다.)

(2) 통계 정보의 종류

1. 선택도 (Selectivity)

• 전체 대상 레코드 중에서 특정 조건에 의해 선택될 것으로 예상되는 레코드 비율이다.
• 선택도를 가지고 카디널리티를 구한다. 이후 다시 비용을 구해 인덱스 사용 여부, 조인 순서와 방법 등을 결정한다.
• 최적의 실행계획을 수립하는 데 있어 가장 중요한 요인이다.
• 실행계획 수립 절차
  • 선택도 → 카디널리티 → 비용 → 액세스 방식, 조인 순서, 조인 방법 등 결정
• 히스토그램이 있으면 그것으로 선택도를 산정한다.
  • 단일 칼럼에 대해서는 비교적 정확하다.
• 히스토그램이 없거나, 조건절에 바인드 변수를 사용하면 옵티마이저는 데이터 분포가 균일하다고 가정한 상태에서 선택도를 산정한다.

  ✅ 히스토그램 없이 등치(=) 조건에 대한 선택도를 구하는 공식

  선택도 = 1 / Distinct Value 개수

2. 카디널리티 (Cardinality)

• 특정 액세스 단계를 거치고 난 후 출력될 것으로 예상되는 결과 건수이다.
• 칼럼 히스토그램이 없을 때 ‘=’ 조건에 대한 선택도가 1/num_distinct 이므로 카디널리티는 아래와 같이 구해진다.

  ✅ 카디널리티

  = 총 로우 수 × 선택도
  = num_rows / num_distinct

[예시] 부서 칼럼의 Distinct Value 개수가 10 일 경우

select * from 사원 where 부서 = :부서;

• 선택도는 0.1 ( = 1 / 10 )이다.
• 즉, 총 사원 수 1,000명일 때, 카디널리티 = 100
  0 옵티마이저는 위 조건절에 의한 결과집합 100건을 예상하게된다.

[예시] 조건절이 두 개 이상일 때

• 조건절이 두 개 이상일 때, 각 칼럼의 선택도와 전체 로우 수를 곱한다.

select * from 사원 where 부서 = :부서 and 직급 = :직급;

• 직급의 도메인이 {부장, 과장, 대리, 사원}이면 Distinct Value 개수가 4이다.
• 선택도는 0.25 ( = 1 / 4 ), 카디널리티는 25 ( = 1000 × 0.1 × 0.25)가 된다.

3. 히스토그램

• 미리 저장된 히스토그램 정보가 있으면, 옵티마이저는 그것을 사용해 더 정확하게 카디널리티를 구할 수 있다.
• 특히, 분포가 균일하지 않은 칼럼으로 조회할 때 효과를 발휘한다.

히스토그램의 두 가지 유형

<1> 도수분포 히스토그램

• 값 별로 빈도수(frequency number)를 저장하는 히스토그램
• 칼럼이 가진 값의 수가 적을 때 사용된다.
• 칼럼 값의 수가 적기 때문에 각각 하나의 버킷을 할당(값의 수 = 버킷 개수)하는 것이 가능하다.

<2> 높이균형 히스토그램

해당 그림에서 x 축은 연령대를 의미하며, age = 40 인 레코드 비중이 50%로 총 20개 중 10개 버킷을 차지한다.

• 칼럼이 가진 값의 수가 아주 많아 각각 하나의 버킷을 할당하기 어려울 때 사용된다.
• 히스토그램 버킷을 값의 수보다 적게 할당하기 때문에 하나의 버킷이 여러 개 값을 담당
• 빈도 수가 많은 값(popular value)에 대해서는 두 개 이상의 버킷이 할당된다.

[예시] 값의 수가 1,000개인데 히스토그램을 위해 할당된 버킷 개수가 100개일 경우

• 하나의 버킷이 평균적으로 10개의 값을 대표한다.
  • 높이균형 히스토그램에서는 말 그대로 각 버킷의 높이
• 각 버킷은 {1/(버킷 개수) × 100} % 의 데이터 분포를 갖는다.

  ✅ 각 버킷(→ 값이 아니라 버킷)이 갖는 빈도수 = { (총 레코드 개수) / (버킷 개수) }

4. 비용

‘비용(Cost)’이란?

• 쿼리를 수행하는데 소요되는 일량 또는 시간(예상치)
• CBO는 비용(Cost)을 기반으로 최적화를 수행하고 실행계획을 생성한다.

옵티마이저 비용 모델

• I / O 비용 모델 : 예상되는 I/O 요청(Call) 횟수만을 쿼리 수행 비용으로 간주해 실행계획을 평가한다.
• CPU 비용 모델 : I / O 비용 모델에 시간 개념을 더해 비용 산정

인덱스를 경유한 테이블 액세스 비용

• I/O 비용 모델에서의 비용은 디스크 I/O Call 횟수이다.
  • ⭐️ 논리적/물리적으로 읽은 블록 개수가 아닌 I/O Call 횟수
• 인덱스를 경유한 테이블 액세스 시, Single Block I/O 방식이 사용된다.
  • 디스크에서 한 블록을 읽을 때마다 한 번의 I/O Call을 일으키는 방식
  • 읽게 될 물리적 블록 개수가 I/O Call 횟수와 일치한다.

✅ 인덱스를 이용한 테이블 액세스 비용 공식

비용 = blevel -- 인덱스 수직적 탐색 비용
 + (리프 블록 수 × 유효 인덱스 선택도)  -- 인덱스 수평적 탐색 비용
 + (클러스터링 팩터 × 유효 테이블 선택도)  -- 테이블 Random 액세스 비용

Full Scan에 의한 테이블 액세스 비용

• 테이블 전체를 순차적으로 읽어 들이는 과정에서 발생하는 I/O Call 횟수로 비용을 계산
• Full Scan할 때는 한 번의 I/O Call로써 여러 블록을 읽어 들이는 Multiblock I/O 방식을 사용
  • 총 블록 수를 Multiblock I/O 단위로 나눈 만큼 I/O Call 발생

[예시] 100블록을 8개씩 나누어 읽을 때

• 13번의 I/O Call이 발생한다.
• I/O Call 횟수로써 Full Scan 비용을 추정한다.
• Multiblock I/O 단위가 증가할수록 I/O Call 횟수가 줄고 예상비용도 줄게 된다.

[5] 옵티마이저

(1) 옵티마이저 힌트

• 통계정보가 정확하지 않거나 기타 다른 이유로 옵티마이저가 잘못된 판단을 할 수 있다.
  이 때 프로그램이나 데이터 특성 정보를 정확히 알고 있는 개발자가 직접 인덱스를 지정하거나 조인 방식을 변경함으로써 더 좋은 실행계획으로 유도하는 메커니즘이 바로 힌트이다.
• 힌트 종류와 구체적인 사용법은 DBMS마다 천차만별이다.

1. Oracle 힌트

힌트 기술 방법

SELECT /*+ LEADING(e2 e1) USE_NL(e1) INDEX(e1 emp_emp_id_pk) USE_MERGE(j) FULL(j) */ 

             e1.first_name, e1.last_name, j.job_id, sum(e2.salary) total_sal 

FROM    employees e1, employees e2, job_history j 

WHERE e1.employee_id = e2.manager_id AND e1.employee_id = j.employee_id AND e1.hire_date = j.start_date 

GROUP BY e1.first_name, e1.last_name, j.job_id 

ORDER BY total_sal;

힌트가 무시되는 경우 : 다음과 같은 경우에 Oracle 옵티마이저는 힌트를 무시하고 최적화를 진행한다.

• 문법적으로 틀린 힌트 기술
• 의미적으로 틀린 힌트 기술
  • 예) 서브쿼리에 unnest와 push_subq를 같이 기술한 경우
    (unnest되지 않은 서브쿼리만이 push_subq 힌트의 적용 대상)
• 잘못된 참조 사용
  • 없는 테이블이나 별칭(Alias)을 사용
  • 없는 인덱스명을 지정한 경우 등
• 논리적으로 불가능한 액세스 경로
  • 조인절에 등치(=) 조건이 하나도 없는데 Hash Join으로 유도
  • null 허용칼럼에 대한 인덱스를 이용해 전체 건수를 세려고 시도하는 등

    select /*+ index(e emp_ename_idx) */ count(*) from emp e

• 버그

⭐️ 위 경우에 해당하지 않는 한 옵티마이저는 힌트를 가장 우선적으로 따른다.

• 옵티마이저는 힌트를 선택 가능한 옵션 정도로 여기는 게 아니라 사용자로부터 주어진 명령어(directives)로 인식한다.

2. SQL Server 힌트

SQL Server는 문법이나 의미적으로 맞지 않게 힌트를 기술하면 프로그램에 에러가 발생한다.

SQL Server에서 옵티마이저 힌트를 지정하는 방법

<1> 테이블 힌트

• 테이블명 다음에 WITH절을 통해 지정한다.
  • fastfirstrow, holdlock, nolock 등

<2> 조인 힌트

• FROM절에 지정하며, 두 테이블 간 조인 전략에 영향을 미친다.
  • loop, hash, merge, remote 등

<3> 쿼리 힌트

• 쿼리당 맨 마지막에 한번만 지정할 수 있는 쿼리 힌트는 OPTION절을 이용한다.

3. Oracle과 SQL Server 힌트 차이점

• Oracle은 사용자가 힌트를 잘못 기술하거나 잘못된 참조를 사용하더라도 에러가 발생하지 않는다.
• 힌트와 관련한 Oracle의 이런 정책은 프로그램 안정성 측면에 도움이 되는가 하면, 성능 측면에서 불안할 때도 있다.
• DBMS마다 차이가 있다는 사실을 미리 숙지하고, 애플리케이션 특성(안정성 우선, 성능 우선 등)에 맞게 개발 표준과 DB 관리정책을 수립할 필요가 있다.

[예시] 힌트에 사용된 인덱스를 어느 날 DBA가 삭제하거나 이름을 바꾸었다고 하자.

• SQL Server에선 에러가 발생하므로 해당 프로그램을 수정하고 다시 컴파일해야 한다.
  • 프로그램을 수정하다 보면 인덱스 변경이 발생했다는 사실을 발견하게 된다.
  • 성능에 문제가 생기지 않도록 적절한 조치를 취할 수 있다.
• Oracle에선 프로그램을 수정할 필요가 없어 좋지만 내부적으로 Full Table Scan하거나 다른 인덱스가 사용되면서 성능이 갑자기 나빠질 수 있다.
  • 애플리케이션 운영자는 사용자가 불평하기 전까지 그런 사실을 알지 못한다.
  • 사용 빈도가 높은 프로그램에서 그런 현상이 발생해 시스템이 멎기도 한다.