제 1절 옵티마이저

1. 옵티마이저 소개

가. 옵티마이저란?

• 옵티마이저 (Optimizer) : SQL 을 가장 빠르고 효율적으로 수행할 최적의 처리 경로를 생성해 주는 DBMS 내부 핵심엔진.
• SQL 로 결과집합 요구하면, 옵티마이저가 자동으로 처리경로 (실행계획 : Execution Plan) 를 생성해준다.
  {info:title=옵티마이저의 SQL 최적화 과정}
• 사용자 쿼리수행을 위해, 후보군이 될만한 실행계획을 찾는다.
• 데이터 딕셔너리에 미리 수집해 놓은 오브젝트 통계 및 시스템 통계정보를 이용하여 각 실행계획의 예상비용을 산정.
• 각 실행계획을 비교하여 최저비용을 갖는 하나를 선택.
• (위 과정은 비용기반 옵티마이저의 SQL 최적화 과정이다)
  {info}

나. 옵티마이저 종류

• 1) 규칙기반 옵티마이저
  • Rule-Based Optimizer (RBO), 휴리스틱 옵티마이저라 불린다.
  • 미리 정해놓은 규칙 (액세스 경로별 우선순위) 으로 액세스 경로 평가하여 실행계획 선택한다.
  • 인덱스 구조, 연산자, 조건절 형태가 순위 결정짓는 주 요인이다.

• 2) 비용기반 옵티마이저
  • Cost-Based Optimizer (CBO), 비용을 기반으로 최적화 수행.
  • 비용(Cost) 이란 쿼리수행 시 소요되는 일량, 시간을 뜻하며, 이는 예상치 이다.
  • 미리 구해둔 테이블, 인덱스에 대한 통계정보를 기초로 오퍼레이션 단계별 예상비용 산정, 이를 합한 총 비용이 가장 낮은 실행계획을 선택.
  • 오브젝트 통계 항목 : 레코드 개수, 블록 개수, 평균 행 길이, 컬럼 값의 수, 컬럼 값 분포, 인덱스 높이 (Height), 클러스터링 팩터, 하드웨어 특성을 반영한 시스템 정보(CPU 속도, 디스크 I/O 속도 등)
  • ORACLE 은 RBO에서 출발하였으나, 타 DBMS는 CBO채택. Oracle 도 10g 부터 RBO 지원 중단함.
    {panel:title=스스로 학습하는 옵티마이저 (Self-Learning Optimizer)| borderColor=#ccc|titleBGColor=#B0C4DE|bgColor=#ffffff}
    전통적 옵티마이저는 오브젝트 통계, 시스템 통계로 부터 산정한 "예상" 비용만으로 실행계획을 수립했다.
    앞으로는 예상치와 런타임 수행 결과를 비교하고, 예상치가 빗나갔을 때 실행계획을 조정하는 옵티마이저로 발전할 것이다.
    최근에 발표된 각 DBMS 버전은 이미 이런 기능을 포함하고 있다.
    {panel}

다. SQL 최적화 과정

• Oracle 기준으로 SQL 최적화 및 수행과정을 표현하면 아래와 같다.
  
• 각 서브엔진별 역할
  
  • 쿼리를 내부 표현방식으로 변환
  • 표준적인(canonical) 형태로 변환
  • 후보군이 될만한 (낮은 레벨의) 프로시저를 선택
  • 실행계획을 생성하고, 가장 비용이 적은 것을 선택

라. 최적화 목표

1. 전체 처리속도 최적화

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

 
alter system set optimizer_mode = all_rows;   // 시스템 레벨 변경
alter session set optimizer_mode = all_rows;  // 세션 레벨 변경
select /*+ all_rows */ * from t where ... ;          // 쿼리 레벨 변경

1. 최초 응답속도 최척화

• 전체 결과집합 중 일부만 읽다가 멈추는 것을 전제로, 가장 빠른 응답 속도를 낼 수 있는 실행계획을 선택한다.
• 이 모드에서 생성한 실행계획으로 데이터를 끝까지 읽으면 전체 처리속도 최적화 실행계획보다 더 많은 리소스를 사용하고 전체 수행 속도도 느려질 수 있다.
• Oracle 에서 옵티마이저 모드를 first_rows_n 으로 지정 (시스템 또는 세션레벨에서) first_rows_10 으로 지정 시, 사용자가 전체 결과집합 중 처음 10개의 로우만 읽고 멈추는 것을 전제로 가장 빠른 응답속도를 내는 실행계획을 선택한다.

select /*+ first_rows(10) */ * from t where ;

• SQL 서버에서는 쿼리 힌트로 FAST 10 을 지정하면 된다.

select * from t where OPTION(fast 10); 

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

가. SQL과 연산자 형태

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

나. 옵티마이징 팩터

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

다. DBMS 제약 설정

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

라. 옵티마이저 힌트

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

마. 통계정보

• 통계정보는 옵티마이저에 미치는 영향이 절대적이다.
• CBO의 모든 판단기준은 통계정보에서 나온다.

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

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

사. DBMS 버전과 종류

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

3. 옵티마이저의 한계

• 사람이 만든 소프트웨어 엔진이 불과. 결코 완벽하지 않다.
• 현실적인 제약 (통계정보 수집량, 최적화를 위해 허락되는 시간) 때문에 아직 적용하지 못하는 것들도 있다.

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

• 적절한 옵티마이징 팩터 (효과적으로 구성된 인덱스, IOT, 클러스터링, 파티셔닝) 등을 제공해야 좋은 실행계획을 수립할 수 있다.

나. 통계정보의 부정확성

• 100% 정확한 통계정보를 유지하는 것은 현실적으로 어렵다.
• 컬럼 분포가 고르지 않을 때 컬럼 히스토램이 반드시 필요한데 이를 수집, 유지하는 비용이 만만치않다.
• 컬럼 결합 시 모든 결합 분포를 미리 구해두기 어려운 것도 제약 중 하나다.
  상관관계에 있는 두 컬럼이 조건절에 사용될 때 옵티마이저가 잘못된 실행계획을 수립하게 만드는 주요인이다.
• 예시

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

• 직급 = { 부장, 과장, 대리, 사원 } 각 25% 비중
• 전체 사원 1,000명
• 히스토그램 상 '연봉' >= 5000 조건에 부합하는 사원 비중이 10% 이면, 옴티마이저는 쿼리 조건에 해당하는 사원 수를 25 (=1000 X 0.25 X 0.1) 명으로 추정한다.
• 직급과 연봉은 상관관계가 높으므로 모든 부장의 연봉이 5000 만원 이상이라면, 쿼리 결과는 250 (=1000 X 0.25 X 1) 건이다.
• 이런 결합 분포를 모두 저장하면 좋겠으나, 테이블 컬럼이 많을 수록 조합수가 기하급수적으로 증가하므로 거의 불가능하다.

다. 바인드 변수 사용 시 균등분포 가정

• 조건절에 바인드 변수 사용 시, 옵티마이저가 균등분포를 가정하고 비용계산하므로
  정확한 히스토그램을 보유해도 바인드 변수를 사용한 SQL에는 무용지물이다.

비현실적인 가정

• 쿼리수행비용 평가 시 여러 가정을 하는데, 그 중 일부는 비현실적으로 이해할 수 없는 실행계획을 수립하기도 한다.
  ( 예전 Oracle 버전은 Single Block I/O , Multiblock I/O 의 비용을 같게하고, 데이터 블록 캐싱 효과도 고려하지 않았다. )

마. 규칙에 의존하는 CBO

• CBO라 해도 부분적으로는 규칙에 의존한다.
• 최적화 목표를 최초 응답속도에 맞추면 ( optimizer_mode = first_rows ), order by, 소트를 대체할 인덱스가 있을 경우 무조건 인덱스를 사용한다.
• 휴리스틱 쿼리 변환도 좋은 예 이다. (다음 절에서 설명)

바. 하드웨어 성능 특성

• 운영 시스템 하드웨어 사양이 다르면 옵티마이저가 잘못된 실행계획을 수립할 가능성이 높아진다.
• 어플리케이션 특성 (I/O패턴, 부하정도 등) 에 의해서도 하드웨어 성능은 달라진다.

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

• CBO 는 실행계획 생성 시 SQL에서 액세스할 데이터 특성을 고려하기 위해 통계정보를 사용한다.
• DBMS 버전이 올라갈수록 자동 통계관리 방식으로 바뀌고 있으나 수동으로 DB관리자가 수집해 주어야 할 때도 있다.
• 통계정보 종류는 아래 네 가지가 있다.

가. 선택도

• 선택도 (Selectivity) : 전체 대상 레코드 중 특정 조건에 의해 선택될 것으로 예상되는 레코드 비율
• 선택도를 가지고 카디널리티를 구하고, 다시 비용을 구해 인덱스 사용 여부, 조인 순서와 방법을 결정한다.
• 최적의 실행계획을 수립하는데 가장 중요한 요인.

나. 카디널리티

• 특정 액세스 단계를 거친 뒤 출력될 것으로 예상되는 결과 건수
  총 로우수 에 선택도 를 곱해서 구한다.

다. 히스토그램

• 저장된 히스토그램 정보가 있으면, 옵티마이저는 이를 사용해 더 정확하게 카디널리티를 구할 수 있다.
• 분포가 균일하지 않은 컬럼을 조회할 때 효과를 발휘한다.
• 도수분포 히스토그램
  • 빈도수(frequency number) 를 저장하는 히스토그램.
  • 칼럼이 가진 값의 수가 적을 때 사용한다. 각각 하나의 버킷을 할당(값의 수 = 버킷 개수) 하는 것이 가능하다.
    
• 높이균형 히스토그램
  • 칼럼이 가진 수가 아주 많아 각각 하나의 버킷을 할당하기 어려울 때 사용된다.
  • 하나의 버킷이 여러 개 값을 담당한다.
  • 값의 수가 1000 개 인데 히스토그램을 위해 할당된 버킷 수가 100 개이면, 하나의 버킷이 평균 10개의 값을 대표한다.
  • 높이균형 히스토그램은 버킷 높이가 같으며, 각 버킷은 {1/(버킷 개수) X 100} % 의 데이터 분포를 갖는다.
  • 각 버킷이 갖는 빈도수는 { (총 레코드 개수) / (버킷 개수)} 로 구할 수 있다.
  • 빈도 수 많은 값은 두 개 이상의 버킷이 할당된다.
    
  • x 축은 연령대로, age = 40 레코드 비중이 50% 여서 총 20개 버킷 중 10개 버킷을 차지해있다.

라. 비용

• 비용(Cost) : 쿼리를 수행하는데 소요되는 일량 또는 시간으로 예상치이다.
• 옵티마이저 비용모델에는 두 가지가 있다.
  • I/O 비용모델 : 요청 횟수만을 쿼리 수행 비용으로 간주해 실행계획 평가
  • CPU 비용모델 : 요청 횟수와 시간개념을 더해 비용을 산정한다.

• 인덱스를 경유한 테이블 액세스 비용
  • I/O 비용모델에서 비용은 디스크 I/O Call 횟수 (논리적,물리적으로 읽은 블록 개수가 아닌 I/O Call횟수) 를 의미한다.
  • 인덱스 경유한 테이블 액세스 시 Single Block I/O 방식 사용
  • 디스크에서 한 블록을 읽을 때 마다 한 번의 I/O Call 방식이므로 물리적 블록 개수가 I/O Call 횟수와 일치한다.
  • 인덱스를 이용한 테이블 액세스 비용은 아래 공식과 같다.
    
  • 인덱스를 경유한 테이블 액세스 비용 항목

• Full Scan에 의한 테이블 액세스 비용
• Full Scan 은 테이블 전체를 순차적으로 읽어들일 때 발생하는 I/O Call 횟수를 비용으로 계산한다.
• Full Scan 시 한 번의 I/O Call 로 여러 블록을 읽어들이는 Multiblock I/O 방식을 사용하므로,
  총 블록 수를 Multiblock I/O 단위로 나눈 만큼 I/O Call 이 발생한다.
• Multiblock I/O 단위가 증가할 수록 I/O Call 횟수가 줄고 예상비용도 줄게 된다.

옵티마이저 힌트

• 옵티마이저 힌트는 프로그램이나 데이터 특성 정보를 정확히 알고있는 개발자가,
  직접 인덱스를 지정하거나 조인 방식을 변경하여 더 좋은 실행계획을 유도할 수 있도록 한다.

가. Oracle 힌트

1. 힌트 기술 방법

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 ;

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

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

• • 버그
    • Oracle 의 경우 잘못된 힌트 기술이나 참조에도 에러가 발생하지 않는다. ( SQL Server 의 경우 없는 인덱스 참조 힌트 사용 시 에러 발생한다 )
      DBMS 마다 차이가 있음을 숙지하고, 애플리케이션 특성에 맞게 개발 표준과 DB관리 정책을 수립해야 한다.

1. 힌트 종류 (Oracle)

나. SQL Server 힌트

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

1. 테이블 힌트
   • 테이블명 다음에 WITH 절을 통해 지정. ( fastfirstrow, holdlock, nolock 등 )
2. 조인 힌트
   • FROM 절에 지정, 두 테이블 간 조인 전략에 영향. loop, hash, merge, remote 등
3. 쿼리 힌트
   • 쿼리당 맨 마지막에 한번만 지정할 수 있으며 OPTION 절을 이용한다.