SQL 옵티마이저
통계정보와 비용 계산 원리
선택도, 카디널리티, 통계정보에 대해 살펴보고,
옵티마이저가 이를 이용해 비용을 계산하는 원리를 살펴본다.
선택도와 카디널리티
선택도 : 전체 레코드 중 조건절에 의해 선택되는 레코드 비율
= 1 / NDV(Number of Distinct Values)
카니덜리티 : 전체 레코드 중 조건절에 의해 선택되는 레코드 개수
= 전체 로우 수 * 선택도
옵티마이저는 카디널리티를 구한 후 데이터 액세스 비용을 계산해
테이블 액세스 방식, 조인 순서, 조인 방식 등을 결정한다.
이때 비용 계산의 출발점은 선택도기에 이를 잘못 계산하면, 이후의 계산들이 잘못되어
비효율적인 액세스 방식과 조인 방식을 선택할 수 밖에 없다.
선택도를 계산할 때 NDV를 사용하므로 통계정보 수집주기, 샘플링 비율 등을 잘 결정해
정확한 통계정보를 사용해서 계산할 수 있게 처리해줘야 한다.
통계정보
통계정보 = 오브젝트 통계 + 시스템 통계
오브젝트 통계 = 테이블 통계 + 인덱스 통계 + 컬럼 통계
테이블 통계
NUM_ROWS : 테이블에 저장된 총 레코드 개수
BLOCKS : 테이블 블록 수 = 사용된 익스텐트에 속한 총 블록 수
AVG_ROW_LEN : 레코드당 평균 길이(Bytes)
SAMPLE_SIZE : 샘플링한 레코드 수
LAST_ANALYZED : 통계정보 수집일시
인덱스 통계
BLEVEL : 브랜치 레벨, 루트에서 리프 블록에 도달 직전까지 읽게 되는 블록 수
LEAF_BLOCKS : 인덱스 리프 블록 총 개수
NUM_ROWS : 인덱스에 저장된 레코드 총 개수
DISTINCT_KEYS :인덱스 키값의 조합으로 만들어지는 값의 종류 개수
AVG_LEAF_BLOCKS_PER_KEY : 키값을 = 조건으로 조회 시 읽게 될 리프 블록 개수
AVG_DATA_BLOCKS_PER_KEY : 키값을 = 조건으로 조회 시 읽게 될 테이블 블록 개수
CLUSTERING_FACTOR : 키값 기준으로 테이블 데이터가 모여 있는 정도
컬럼 통계
테이블 통계 수집시 같이 수집된다.
NUM_DISTINCT : 컬럼 값의 종류 개수(NDV)
DENSITYY : = 조건 검색 시 선택도를 미리 구해 놓은 값
AVG_COL_LEN : 컬럼 평균 길이
LOW_VALUE : 최소 값
HIGH_VALUE : 최대 값
NUM_NULLS : 값이 NULL인 레코드 수
컬럼 히스토그램
= 조건에 대한 선택도는 선택도 공식으로 구하거나 미리 구해 놓은 DENSITY 값을 이용하면 되지만,
데이터 분포가 균일하지 않은 컬럼에서는 해당 공식이 잘 들어맞지 않을 수도 있다.
옵티마이저는 이런 문제를 위해 컬럼 통계 외에도 히스토그램을 사용해 컬럼 값별로
실제 데이터를 읽어서 데이터 비중이나 빈도를 미리 계산해둔다.
히스토그램 유형
도수분포(FREQUENCY) : 값별로 빈도수 저장
높이균형(HEIGHT-BALANCED) : 각 버킷의 높이가 동일하도록 데이터 분포 관리
상위도수분포(TOP-FREQUENCY) : 많은 레코드를 가진 상위 N개 값에 대한 빈도수 저장
하이브리드 : 도수분포와 높인균형 히스토그램의 특성 결합
시스템 통계
애플리케이션 및 하드웨어 성능 특성을 측정한 값
CPU 속도, 평균적인 Single Block I/O 속도, 평균적인 Multi Block I/O 속도 및 개수
I/O 서브시스템의 최대 처리량, 병렬 Slave의 평균적인 처리량
비용 계산 원리
단일 테이블을 인덱스로 액세스할 때의 비용 계산 원리를 살펴보자
인덱스 키값을 모두 = 조건으로 검색 시 비용
= BLEVEL (수직적 탐색 비용)
+ AVG_LEAF_BLOCKS_PER_KEY (수평적 탐색 비용)
+ AVG_DATA_BLOCKS_PER_KEY (랜덤 액세스 비용)
인덱스 키값이 모두 = 조건이 아닐 때 검색 시 비용
= BLEVEL (수직적 탐색 비용)
+ (LEAF_BLOCKS * 유효 인덱스 선택도) (수평적 탐색 비용)
+ (CLUSTERING_FACTOR * 유효 테이블 선택도) (랜덤 액세스 비용)
BLEVEL, LEAF_BLOCKS, CLUSTERING_FACTOR은 인덱스 통계에서 얻고,
유효 인덱스 선택도와 테이블 선택도는 컬럼 통계 및 히스토그램을 통해 계산한다.
유효 인덱스 선택도
전체 인덱스 레코드 중 조건절에 의해 선택될 것으로 예상되는 레코드 비중
유효 테이블 선택도
전체 인덱스 레코드 중 인덱스 컬럼에 대한 모든 조건절에 의해 선택될 것으로 예상되는 레코드 비중
이 두 조건절에 의해 테이블 액세스 여부가 결정된다.
비용의 정확한 의미
I/O 비용 모델에서의 비용은 예상 I/O Call 횟수를 의미하고,
CPU 비용 모델에서의 비용은 Single Block I/O를 기준으로 한 상대적 시간이다.
옵티마이저에 대한 이해
옵티마이저 종류
비용 기반 옵티마이저(CBO, Cost-Based)
쿼리를 위해 후보군이 될만한 실행계획들을 도출하고,
데이터 딕셔너리에 미리 수집해 둔 통계정보를 이용해 각 실행계획의 예상비용을 산정하고,
그중 가장 낮은 비용의 실행계획 하나를 선택하는 옵티마이저다.
규칙 기반 옵티마이저(RBO, Rule-Based)
과거에 사용한 액세스 경로에 대한 우선순위 규칙에 따라 실행 계획을 만드는 옵티마이저다.
통계정보를 활용하지 않고 단순 규칙에만 의존해 대량 데이터 처리에 부적합하다.
옵티마이저 모드
최적화 목표를 설정하는 기능으로 세 가지 모드를 지원한다.
ALL_ROWS
쿼리 결과집합 전체를 읽는 것을 전제로 시스템 리소스를 가장 적게 사용하는 실행계획을 선택한다.
= 전체 처리속도 최적화가 목표
FIRST_ROWS
전체 결과집합 중 앞쪽 일부만 읽다가 멈추는 것을 전제로 응답 속도가 가장 빠른 실행계획을 선택한다.
ALL_ROWS와 비교해 인덱스와 NL 조인을 더 선호한다.
= 최초 응답속도 최적화가 목표
몇 개의 데이터를 읽다가 멈출지를 지정하지 않기 때문에 정확한 비용 산정이 어렵고
DEPRECATED 상태의 옵티마이저 모드이기 때문에 가급적이면 사용하지 않고 FIRST_ROWS_N 모드를 사용하자
FIRST_ROWS_N
앞쪽 N개 로우만 읽고 멈추는 것을 전제로 응답 속도가 가장 빠른 실행계획을 선택한다.
몇 개의 데이를 읽고 멈출지를 지정하기 때문에 더 정확한 비용 산정이 가능하다.
옵티마이저에 영향을 미치는 요소
SQL과 연산자 형태
결과가 같더라도 SQL을 어떻게 작성했는지 혹은 어떤 연산자를 사용한지에 따라
옵티마이저가 다른 선택을 할 수 있기에 쿼리 성능에 영향을 미친다.
인덱스, IOT, 클러스터, 파티션, MV 등 옵티마이징 팩터
쿼리가 같아도 각 요소들을 구성했는지, 어떤 식으로 구성했는지에 따라 달라진다.
제약 설정
데이터 무결성을 보장해 줄뿐만 아니라 옵티마이저가 쿼리 성능을 최적화하는데
매우 중요한 메타 정보로 활용된다.
통계정보
옵티마이저에 가장 큰 영향을 미치는 요소 중 하나로 잘못된 통계정보로 성능 저하 및 장애가 발생할 수 있다.
옵티마이저 힌트
옵티마이저는 힌트를 명령어로 인식하고 그대로 따르기 때문에 절대적인 영향을 미치는 요소다.
옵티마이저 관련 파라미터
모든 환경이 같아도 오라클 버전 업그레이드 시 옵티마이저 동작이 다른 경우가 있는데
이는 파라미터 추가 또는 기본값 변경이 원인이다.
옵티마이저의 한계
옵티마이저는 항상 최적의 선택을 할 수 있는 완벽한 존재는 아니다.
- 통계정보를 필요한 만큼 충분히 확보하는 것이 힘들다.
- 바인드 변수를 사용한 SQL에 컬럼 히스토그램 활용이 불가하다.
- 비용기반으로 작동해도, 내부적으로 여러 가정과 정해진 규칙을 이용해 기계적인 선택을 한다.
- 최적화에 허용되는 시간이 매우 짧다.
개발자의 역할
바빠서 옵티마이저에게 맡기더라도, 결과물이 올바른지 실행계획을 통해 늘 점검하고 개선하자!
필요한 최소 블록만 읽도로 쿼리 작성
결과집합을 논리적으로 잘 정의하고, 결과집합을 만들기 위해 DB 프로세스가 최소한의 일만 하도록
쿼리를 효율적으로 작성하는 것이 근분적으로 가장 중요하다.
통계정보에 담기 힘든 업무적 특성까지 고려해서 효율적인 SQL을 작성하는 것은 개발자의 역할이 맞다.
최적의 옵티마이징 팩터 제공
옵티마이저가 최적화를 잘 수행할 수 있게 적절한 수단을 제공해줘야 한다.
전략적인 인덱스 구성
어떤 테이블을 어떤 조건으로 자주 액세스하는지는 개발자가 더 잘알기 때문에
해당 부분도 DBA가 아닌 개발자가 구성해주면 좋다.
DBMS가 제공하는 다양한 기능 활용
파티션, 클러스터, IOT, MV, Result Cache 등에 대해 학습하고 활용하자
옵티마이저 모드 설정
상황에 맞는 전략(모드)을 잘 설정해줘야 한다.
정확하고 안정적인 통계정보
옵티마이저가 계획을 잘 수립하고 계산할 수 있게 필요한 정보를 잘 제공해줘야 한다.
필요하다면, 옵티마이저 힌트를 사용해 최적의 액세스 경로로 유도
옵티마이저는 완벽하지 않기 때문에 최적의 실행계획만 세울 수는 없고,
최적으로 실행되고 있더라도 절대 다른 방식으로 바뀌면 안되는 경우도 있기 때문에
해당 경우들에 대해 힌트를 사용해 액세스 경로를 적절하게 유도할줄 알아야 한다.
튜닝 전문가 되는 공부방법
데이터베이스 튜닝이란
SQL 튜닝 : I/O 효율화, DB Call 최소화, SQL 파싱 최소화 등
DB 설계 : 논리적 데이터 구조 설계, 물리적 저장 구조 설계 등
인스턴스 튜닝 : Lock/Latch 모니터링 및 해소, 메모리 설정, 프로세스 설정 등
오디오 튜닝 관점에서의 데이터베이스 튜닝
좋은 소스 = 옵티마이저가 효율적으로 처리할 수 있게 작성한 SQL
좋은 공간 = 효과적인 데이터 구조
이 두 가지를 잘 갖춘 뒤에나 좋은 하드웨어가 필요하다.
무엇을 어떻게 공부해야 하는가
DBA
- 데이터베이스 설치, 백업/복구, 오브젝트 생성/변경, 보안 등
- 데이터베이스 아키텍처 완벽 숙지
- 운영 과정에서 생기는 장애 상황을 모니터링하고 해결하는 기술력 및 자신만의 스크립트 개발
SQL 튜닝
- 옵티마이저가 SQL을 파싱하고 통계정보를 활용해 실행계획을 생성하는 원리
- 옵티마이저가 쿼리변환 원리를 바탕으로 실행계획을 분석하는 방법
- 옵티마이저 힌트를 이용해 실행계획을 제어하는 방법
- 옵티마이저가 좋은 실행계획을 생성하도록 유도하기 위한 효과적인 SQL 작성법
- 애플리케이션에서 SQL을 실행할 때 사용하는 프로그래밍 인터페이스
- SQL을 빠르게 처리할 수 있는 좋은 데이터 구조와 파티션/인덱스 설계
- 정확성과 안정성을 확보할 수 있는 통계정보 수집 정책