[SQLP] 옵티마이징 원리

1. 옵티마이저 소개

옵티마이저 종류

1. 규칙기반 옵티마이저: 미리 정해 놓은 규칙에 따라 엑세스 경로를 평가하고 실행계획을 선택, 여기 말하는 규칙이란 인덱스 구조, 연산자 조건절 등을 말한다.
2. 비용기반 옵티마이저: 비용을 기반으로 최적화를 수행한다. 이 때 비용은 레코드 개수, 블록 개수, 평균 행 길이, 컬럼 값의 수, 컬럼 값 분포, 인덱스 높이, 클러스터링 팩터 등이 있다.

최적화 목표

1. 전체 처리속도 최적화: 쿼리 최종 결과 집합을 끝까지 읽는 것을 전체로, 시스템 리소스를 가장 적게 사용하는 실행계획을 선택한다.
   • 아래와 같이 옵티마이저 모드를 바꿀 수 있다.

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

2. 최초 응답속도 최적화: 전체 결과 집합 중 일부만 읽다가 멈추는 것을 전제로, 가장 빠른 응답 속도를 낼 수 있는 실행계획을 선택한다.
   • 아래와 같이 전체 집합 중 처음 10개만 읽고 멈추게 할 수도 있다.
   • 오해할까봐 내용을 추가하자면 전체 집합에 대해서 일부만 보여준다는거지, 절대 10개만 가지고 연산한다는게 아니다. SQL-Server의 TOP N 쿼리를 생각하면 된다.

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

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

• SQL 연산자 형태
• 옵티마이징 팩터
  • 인덱스, IOT, 클러스터링, 파티셔닝 등의 구성
• DBMS 제약 설정
  • PK, FK, Not Null 등
• 옵티마이저 힌트
• 통계정보
• 옵티마이저 관련 파라미터
• DBMS 버전과 종류

3. 옵티마이저의 한계

옵티마이저 팩터의 부족

• 아무리 옵티마이저가 발전해도 적절한 옵티마이징 팩터를 제공하지 않는다면 좋은 실행계획을 수립할 수 없다.

통계 정보의 부정확성

• 최적화에 필요한 모든 정보를 수집해서 보관할 수 있다면 옵티마이저도 그만큼 고성능 실행계획을 수립하겠지만, 100% 정확한 통계정보를 유지하기는 현실적으로 어렵다.
• 특히 컬럼 분포가 고르지 않을 때 컬럼 히스토그램이 필요한데 이 비용도 만만치 않고 컬럼이 결합 했을 때의 모든 분포를 파악하기도 어렵다.
• 아래의 쿼리를 예로 들어보자
  • 부장, 과장, 대리, 사원 집합이 각각 25%의 비중을 갖고 전체 1000명의 직원이 있다고 가정하고 5000 이상의 연봉을 가진 직원이 10%이면 옵티마이저는 25(=1000 * 0.25 * 0.1)명으로 추정한다.
  • 근데 실제로 5000 이상인 연봉이 다 부장이라고 치면 실제 쿼리는 100건이다.
  • 이렇게 옵타마이저가 성능 상으로 분포의 함정에 빠질 수 있다.

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

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

• 조건절에 바인드 변수를 사용하면 옵티마이저 균등분포를 가정하고 비용을 계산한다.
  • 대용량 데이터 처리할 때는 바인드보다는 상수로 처리하는 것이 좋다고 한다. - 생각해보면 잘못된 통계 안 좋은 성능을 낼 수 있기 때문이라 생각한다.

비현실적인 가정

• 이전 Oracle 버전에서는 Siggle Block I/O, MultiBlock I/O 의 비용, 데이터 블록 캐싱 등도 고려하지 않았다. 이런 것들이 비현실적인 가정이고 버전이 올라갈 수록 보완하고 있지만, 모두 해결된다고 기대하지는 말자.

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

옵티마이저 통계 유형

통계유형	세부 통계 항목
테이블 통계	전체 레코드 수, 총 블록 수, 빈 블록 수, 한 행당 평균 크기 등
인덱스 통계	인덱스 높이, 리프 블록 수, 클러스터링 팩터, 인덱스 레코드 수 등
컬럼 통계	값의 수, 최저 값, 최고 값, 밀도, null 값 개수, 컬럼 히스토그램 등
시스템 통계	CPU 속도, 평균 I/O 속도, 초당 I/O 처리량 등

가. 선택도

• 보통 일반적으로 선택도 -> 카디널리티 -> 비용 -> 엑세스 방식, 조인 순서, 조인 방법 등으로 결정한다고 한다,.
• 히스토그램이 없거나, 바인드 변수 사용시 = 조건에 관한 선택도를 구하는 공식은 1 / Distinct Value 개수 이다.
  • 균일 분포로 가정하기 때문이다.

나. 카디널리티

• 카디널리티는 특정 엑세스 단계를 거치고 난 후 출력될 것으로 예상되는 결과 건수를 말한다.
  • 카디널리티 = 총 로우 수 x 선택도
• 쉽게 암기를 위해 말하자면 카디널리티가 높을 수록 데이터 중복이 낮다.
  • 카디널리티가 높다는 건 그만큼 데이터들 간의 값 차이가 뚜렷하다고 볼 수도 있겠다.

다. 비용

• 여기서의 비용이란 쿼리를 수행하는 데 소요되는 일량 또는 시간을 뜻하며 어디까지나 예상치이다.
• 옵티마이저 비용 모델에는 I/O 비용 모델과 CPU 비용 모델 2 가지가 잇다.
  • 책에서는 I/O 비용 모델만 다룬다.

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

• I/O 비용 모델에서의 비용은 디스크 I/O Call 횟수(논리적, 물리적으로 읽은 블록 개수가 아닌 I/O Call 횟수)를 의미한다.
• 그리고 인덱스를 경유한 테이블 엑세스 시에는 SIngle Block I/O 방식이 사용된다.
  • 그래서 읽게 될 물리적 블록 개수가 I/O Call 횟수가 일치한다.
• 비용 산정을 그래서 아래와 같이 할 수 있다.
  • 비용 = blevel + (리프 블록 수 x 유효 인덱스 선택도) + (클러스터링 팩터 x 유효 테이블 선택도)
  • 위의 공식을 비용으로 말하자면 인덱스 수직적 탐색 비용 + 인덱스 수평적 탐색 비용 + 테이블 랜덤 엑세스 비용이다.

비용 항목 설명

항목	설명
blevel	브랜치 레벨을 의미하며, 리프 블록에 도달하기 전에 읽게 될 브랜치 블록 개수
클러스터링 팩터	특정 칼럼을 기준으로 같은 값을 갖는 데이터가 서로 모여 있는 정도, 논리적인 읽기 블록를 계수화함
유효 인덱스 선택도	전체 인덱스 레코드 중에서 조건절을 만족하는 레코드를 찾기 위해 스캔할 것으로 예상되는 비율
유효 테이블 선택도	전체 레코드 중에서 인덱스 스캔을 완료하고서 최종적으로 테이블을 방문할 것으로 예상되는 비율

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

• 테이블 전체를 순차적으로 읽어 들이는 과정에서 발생하는 I/O Call 횟수로 비용을 계산한다.
• Full Scan은 Multi Block I/O 이기 때문에 총 블록 수 / MultiBlock I/O 단위 로 I/O Call 횟수로 본다.
  • 예를 들어 100개의 블록을 10개씩 있으면 10번의 I/O call이 발생할 것이다.