[친절한 SQL 튜닝 스터디] SQL 옵티마이저

https://velog.io/@wooncloud/series/%EC%B9%9C%EC%A0%88%ED%95%9C-SQL-%ED%8A%9C%EB%8B%9D-%EC%8A%A4%ED%84%B0%EB%94%94

시리즈 | 친절한 SQL 튜닝 스터디 - wooncloud.log

통계정보와 비용 계산 원리

선택도와 카디널리티

선택도

선택도란, 전체 레코드 중에서 조건절에 의해 선택되는 레코드 비율을 말함.

가장 단순한 조건으로 검색할 때의 선택도만 살펴보면, 컬럼 값 종류 갯수를 이용해 아래와 같이 구한다.

💡 NDV : Number of Distinct Values

선택도 = 1 / NDV

카디널리티

카디널리티란 전체 레코드중에서 조건절에 의해 선택되는 레코드 갯수임.

카디널리티 = 총 로우 수 * 선택도 = 총 로우 수 / NDV

예시

상품컬럼에 가전, 의류, 식음료, 생활용품 4개의 값일 있을 때, 아래 조건절 선택도는 25%다.

만약 전체 레코드가 10만건이면, 카디널리티는 2만 5천건이다.

where 상품분류 = '가전'

• 옵티마이저는 카디널리티 구함 ⇒ 데이터 액세스 비용 계산 ⇒ 테이블 액세스방식, 조인순서, 조인 방식 결정
• 비용 계산 출발점은 선택도
• 선택도 계산에 NDV를 사용하므로 통계정보 수집과정에서 이 값을 정확히 구하는게 중요.

통계정보

• 오브젝트 통계
  • 테이블 통계
  • 인덱스 통계
  • 컬럼 통계
• 시스템 통계

테이블 통계

-- 테이블 수집 명령어
begin
    dbms_stats.gather_table_stats('scott', 'emp');
end;

수집된 테이블 통계정보, ALL_TAB_STATISTICS 뷰에서도 같은 정보를 확인할 수 있다.

select num_rows, blocks, avg_row_len, sample_size, last_analyzed
fro, all_tables
where owner = 'SCOTT'
and table_name = 'EMP';

통계항목	설명
NUM_ROWS	테이블에 저장된 총 레코드 갯수
BLOCKS (= 사용된 익스텐트에 속한 총 블록수)	테이블 블록 수
AVG_ROW_LEN	레코드당 평균 길이 (Bytes)
SAMPLE_SIZE	샘플링한 레코드 수
LAST_ANALYZED	통계정보 수집일시

인덱스 통계

-- 인덱스 통계 수집 명령어

-- 인덱스 통계만 수집
begin
    dbms_stats.gether_index_stats( ownname => 'scott', indname => 'emp_x01');
end;

-- 테이블 통계를 수집하면서 인덱스 통계도 같이 수집
begin
    dbms_stats.gether_table_stats ('scott', 'emp', cascade => true);
end;

수집된 인덱스 통계정보는 아래와 같이 조회할 수 있으면, ALL_IND_STATISTICS 뷰에서도 같은 정보를 확인할 수 있다.

select *
from all_indexes
where owner = 'scott'
and table_name = 'emp'
and index_name = 'emp_x01';

통계항목	설명	용도
BLEVEL	브랜치 레벨의 약자, 인덱스 루트에서 리프 블록에 도달하기 직전까지 읽게 되는 블록 수	인덱스 수직적 탐색 비용 계산
LEAF_BLOCKS	인덱스 리프 블록 총 개수	인덱스 수평적 탐색 비용 계산
NUM_ROWS	인덱스에 저장된 레코드 개수	인덱스 수평적 탐색 비용 계산
DISTINCT_KEYS	인덱스 키값의 조합으로 만들어지는 값의 종류 개수, 예를 들어, C1 + C2로 구성한 인덱스에서 C1 컬럼에 3개, C2 컬럼에 4개 값이 있으면 최대 12개 값의 종류가 만들어질텐데, 인덱스에 저장된 데이터 기준으로 실제 입력된 값의 종류 개수를 구해 놓은 수치, 인덱스 키값을 모두 '=' 조건으로 조회할 때의 선택도(Selectivity)를 계산하는 데 사용	인덱스 수평적 탐색 비용 계산
AVG_LEAF_BLOCKS_PER_KEY	인덱스 키값을 모두 '=' 조건으로 조회할 때 읽게 될 리프 블록 개수	인덱스 수평적 탐색 비용 계산
AVG_DATA_BLOCKS_PER_KEY	인덱스 키값을 모두 '=' 조건으로 조회할 때 읽게 될 테이블 블록 개수	테이블 액세스 비용 계산
CLUSTERING_FACTOR	인덱스 키값 기준으로 테이블 데이터가 모여 있는 정도, 인덱스 전체 레코드를 스캔하면서 테이블 레코드를 찾아 갈 때 읽게 될 테이블 블록 개수를 미리 계산해 놓은 수치	테이블 액세스 비용 계산

컬럼 통계

컬럼 통계는 테이블 통계 수집할 때 함께 수집된다.

수집된 컬럼 통계정보 조회. ALL_TAB_COL_STATISTICS 뷰에서도 같은 정보를 확인할 수 있다.

select *
from all_tab_columns
where owner = 'scott'
and table_name = 'emp'
and column_name = 'deptno'

통계항목	설명
NUM_DISTINCT (예를 들어, 성별 컬럼이면 2)	컬럼 값의 종류 개수(NDV, Number of Distinct Values)
DENSITY (히스토그램이 없거나, 있더라도 100% 균일한 분포를 갖는다면, 1 / NUM_DISTINCT 값과 일치)	‘=’ 조건으로 검색할 때의 선택도를 미리 구해 놓은 값.
AVG_COL_LEN	컬럼 평균 길이(Bytes)
LOW_VALUE	최소 값
HIGH_VALUE	최대 값
NUM_NULLS	값이 NULL인 레코드 수

컬럼 히스토그램

• ‘=’ 조건에 대한 선택도는 1/NUM_DISTINCT 공식으로 구하거나 미리 구해 놓은 DENSITY 값을 이용하면 된다.
• 일반적인 컬럼에는 이 공식이 비교적 잘 들어맞지만, 데이터 분포가 균일하지 않은 컬럼에는 그렇지 못하다.
• 선택도를 잘못 구하면 데이터 액세스 비용을 잘못 산정하게 되고, 결국 최적이 아닌 실행계획으로 이어진다.
• 그래서 옵티마이저는 일반적인 컬럼 통계 외에 히스토그램을 추가로 활용.

오라클 12c에서 사용하는 히스토그램 유형

히스토그램 유형	설명
도수분포	값별로 빈도수 저장
높이균형	각 버킷의 높이가 동일하도록 데이터 분포 관리
상위도수분포	많은 레코드를 가진 상위 n개 값에 대한 빈도수 저장 (12c)
하이브리드	도수분포 높이 균형 히스토그램의 특성결합 (12c)

시스템 통계

시스템 통계는 애플리케이션 및 하드웨어 성능 특성을 측정한 것이며, 아래 항목들을 포함한다.

• CPU 속도
• 평균적인 Single Block I/O 속도
• 평균적인 Multiblock I/O 속도
• 평균적인 Multiblock I/O 개수
• I/O 서브시스템의 최대 처리량(Throughput)
• 병렬 Slave의 평균적인 처리량(Throughput)

비용 계산 원리

단일 테이블을 인덱스로 액세스할 때의 비용 계산 방법

• 인덱스 키값을 모두 ‘=’ 조건으로 검색할 때, 인덱스 통계만으로도 쉽게 비용을 계산 가능.

비용 = BLEVEL -- 인덱스 수직적 탐색 비용
    + AVG_LEAF_BLOCKS_PER_KEY -- 인덱스 수평적 탐색 비용
    + AVG_DATA_BLOCKS_PER_KEY -- 테이블 랜덤 액세스 비용

• 인덱스 키값이 모두 ‘=’ 조건이 아닐 때는 컬럼 통계까지 활용

비용 = BLEVEL -- 인덱스 수직적 탐색 비용
    + LEAF_BLOCKS * 유효 인덱스 선택도 -- 인덱스 수평적 탐색 비용
    + CLUSTERING_FACTOR * 유효 테이블 선택도 -- 테이블 랜덤 액세스 비용

• BLEVEL, LEAF_BLOCKS, CLUSTERING_FACTOR는 인덱스 통계에서 얻을 수 있고, 유효 인덱스 선택도와 유효 테이블 선택도는 컬럼 통계 및 히스토그램을 이용해 계산한다.
• 유효 인덱스 선택도 : 전체 인덱스 레코드 중, 액세스 조건에 의해 선택될 것으로 예상되는 레코드 비중을 의미.
• 유효 테이블 선택도 : 전체 인덱스 레코드 중 인덱스 컬럼에 대한 모든 조건절에 의해 선택될 것으로 예상되는 레코드 비중

옵티마이저에 대한 이해

옵티마이저 종류

• 비용기반(Cost-Based) 옵티마이저 (CBO)
  • 사용자 쿼리를 위해 후보군이 될만한 실행계획을 도출
    ⇒ 데이터 딕셔너리(Data Dictionary)에 미리 수집해 둔 통계정보를 이용해 각 실행계획의 예상비용을 산정
    ⇒ 그중 가장 낮은 비용의 실행계획 하나를 선택하는 옵티마이저.
  • CBO가 사용하는 통계정보로는 데이터량, 컬럼 값의 수, 컬럼 값 분포, 인덱스 높이, 클러스터링 팩터 등이 있다.
• 규칙기반(Rule-Based) 옵티마이저 (RBO)
  • 각 액세스 경로에 대한 우선순위 규칙에 따라 실행계획을 만드는 과거의 옵티마이저
  • 데이터 특성을 나타내는 통계정보를 전혀 활용하지 않고 단순한 규칙에만 의존하기 때문에 대량 데이터를 처리하는 데 부적합.

옵티마이저 모드

최적화 목표를 설정하는 기능으로서 아래 세가지 옵티마이저 모드 중 하나를 선택하면 된다.

• ALL_ROWS
  • 전체 처리속도 최적화
  • 옵티마이저는 쿼리 결과집합 ‘전체를 읽는 것을 전제로’ 시스템 리소스(I/O, CPU, 메모리 등)를 가장 적게 사용하는 실행계획을 선택
  • 즉, 전체 처리속도 최적화가 목표
• FIRST_ROWS
  • 최초 응답속도 최적화
  • 옵티마이저는 전체 결과집합 중 ‘앞쪽 일부만 읽다가 멈추는 것을 전제로’ 응답 속도가 가장 빠른 실행계획을 선택
  • 즉, 최초 응답속도 최적화가 목표
  • ALL_ROWS와 비교하면, Table Full Scan 보다 인덱스를 더 많이 선택하고,
    해시 조인, 소트 머지 조인보다 NL 조인을 더 많이 선택하는 경향을 보임
  • 사실 이 옵티마이저 모드는 앞으로 사라지게 될(deprecated) 옵티마이저 모드이기 때문에, FIRST_ROWS_N을 사용해야 한다.
• FIRST_ROWS_N
  • 최초 N건 응답속도 최적화
  • 옵티마이저는 사용자가 ‘앞쪽 N개 로우만 읽고 멈추는 것을 전제로’ 응답 속도가 가장 빠른 실행계획을 선택
  • ALTER SYSTEM 또는 ALTER SESSION 명령어로 옵티마이저 모드를 설정할 때 N으로 지정할 수 있는 값은 아래와 같이 1, 10, 100, 1000 네 가지다.

alter session set optimizer_mode = first_rows_1;
alter session set optimizer_mode = first_rows_10;
alter session set optimizer_mode = first_rows_100;
alter session set optimizer_mode = first_rows_1000;

FIRST_ROWS(n) 힌트로 설정할 때는 괄호 안에 ()보다 큰 어떤 정수 값이라도 입력할 수 있다.

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

• FIRST_ROWS는 사용자가 데이터를 어느 정도 읽다가 멈출지를 지정하지 않았으므로 정확한 비용 산정이 어려움
• FIRST_ROWS_N은 읽을 데이터 건수를 지정하였으므로 더 정확한 비용 산정이 가능

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

SQL과 연산자 형태

결과가 같더라도 SQL을 어떤 형태로 작성했는지 또는 어떤 연산자 (=, IN, LIKE, BETWEEN, 부등호 등)를 사용했는지에 따라 옵티마이저가 다른 선택을 할 수 있거, 궁극적으로 쿼리 성능에 영향을 미침.

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

쿼리를 똑같이 작성해도 인덱스, IOT, 클러스터, 파티션, MV 등을 구성했는지, 그리고 어떤 식으로 구성했는지에 따라 실행계획과 성능이 달라짐

제약 설정

DBMS에 설정한 PK, FK, CHECK, NOT NULL 같은 제약들은 데이터 무결설 보장해주고 옵티마이저가 쿼리 성능을 최적화하는데 중요한 메타정보로 활용됨.

통계정보

통계정보는 옵티마이저에 매우 강력한 영향을 미침.

SQL 옵티마이저가 사용하는 통계정보에 문제가 생기면 애플리케이션 성능이 갑자기 느려지고 심할땐 장애 상황으로 이어진다.

시스템 장애가 종종 발생하는 다양한 원인

• 특정 테이블 통계정보를 갑자기 삭제한다
• 대량 데이터를 지웠다 다시 입력하기 직전, 데이터가 없는 상태에서 자동으로 통계정보가 수집한다
• 3년간 갱신하지 않던 특정 테이블 통계정보를 갑자기 재수집한다
• 통계정보 없이 관리하던 테이블에 인덱스를 재생성한다
• 테이블이나 인덱스를 재생성하면 파티션 단위로만 통계정보를 수집한다.

옵티마이저 힌트

옵티마이저에게 가장 절대적인 영향을 미치는 요소.

옵티마이저는 힌트를 명령어로 인식하고 그대로 따른다.

옵티마이저 힌트가 동작하지 않는 이유

• 문법이 맞지 않음
• 잘못된 참조
• 의미적으로 맞지 않은 힌트 기술
• 논리적으로 불가능한 액세스 경로
• 버그

옵티마이저 관련 파라미터

옵티마이저 행동에 영향을 미치는 파라미터 목록을 살펴볼 수 있는 쿼리

select name, value, isdefault, default_value
from v$sys_optimizer_env

옵티마이저의 한계

• 네비게이션도 실수하듯 옵티마이저도 완벽하지 않고 한계가 있다.
• 옵티마이저 핸동에 가장 큰 영향을 미치는 통계정보를 ‘필요한 만큼 충분히’ 확보하는 것은 불가능한 일이다.
• 통계정보를 완벽히 수집해도 바인드 변수를 사용한 SQL에 컬럼 히스토그램을 활용할 수 없다는 치명적인 단점도 있음
• 기본적으로 비용기반으로 작동하나, 내부적으로 여러 가정과 정해진 규칙을 이용해 기계적인 선택을 하는것도 한계다.

개발자의 역할

DB 세계에서도 불완전한 옵티마이저에만 의존하는게 아니라 개발자 스스로 옵티마이저가 되어야 한다.

고성능, 고효율 DB 애플리케이션을 구축하려면, 소수 DBA나 튜너보다 다수 개발자의 역할이 중요하다. 따라서 새로운 개발 언어를 익히는 노력 이상으로 SQL 수행원리와 튜닝 방법을 익히는데도 많은 노력과 시간을 투자해야 한다.

장인정신을 가져라

기본적으로 옵티마이저에게 많은 일을 맡기는 RDBMS 환경에서 SQL 성능을 높이기 위해 개발자가 할 일은 다음과 같다.

• 필요한 최소 블록만 읽도록 쿼리 작성
• 최적의 옵티마이징 팩터를 제공한다
• 필요하다면 옵티마이저 힌트를 이용해 최적의 액세스 경로로 유도한다.

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

SQL 작성자 스스로 결과집합을 논리적으로 잘 정의하고, 그 결과집합을 만들기 위해 DB 프로세스가 최소한의 일만 하도록 쿼리를 효율적으로 작성하는 것이 중요하다.

데이터베이스 성능은 I/O 효율에 달려있으므로 동일한 레코드를 반복적으로 읽지 않고, 필요한 최소 블록만 읽도록 해야한다.

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

• 전략적 인덱스 구성 ⇒ 옵티마이저를 돕는 가장 기본적인 옵티마이저 팩터
• DBMS가 제공하는 다양한 기능 활용 ⇒ 파티션, 클러스터, IOT, MV, Result Cache
• 옵티마이저 모드 설정 ⇒ 옵티마이저 모드를 포함해 각종 파라미터를 적절한 값으로 설정해야함
• 정확하고 안정적인 통계정보 ⇒ 통계정보를 잘 수집하자

필요하다면 옵티마이저 힌트를 이용해 최적의 액세스 경로로 유도

옵티마이저가 최적의 실행계획을 수립하지 못하면, 개발자가 힌트를 이용해 직접 데이터 액세스 경로를 선택해 줄 수 있다.

절대 다른 방식으로 바뀌지 않게 실행계획을 고정해야 하는 시스템들도 있음.

DB 애플리케이션 개발자라면 오티마이저가 생각못한 최적의 액세스 경로를 찾고, 그 방식으로 실행계획을 유도할 수 있어야 한다.

튜닝 전문가가 되는 공부 방법

데이터베이스 튜닝이란?

• (= 성능 튜닝) DBMS 성능 튜닝이라는 말이 정확하다.
• SQL이 병목이나 지연 없이 빠르고 안정적으로 수행되도록 조치하는 모든 활동

데이터베이스 튜닝 종류

• SQL 튜닝 : I/O 효율화, DB Call 최소화, SQL 파싱 최소화
• DB 설계 : 논리적 데이터 구조 설계, 물리적 저장 구조 설계 등
• 인스턴스 튜닝 : Lock/Latch 모니터링 및 해소, 메모리 설정, 프로세스 설정 등

데이터베이스에서 좋은 소스란, 옵티마이저가 효율적으로 처리할 수 있게 작성한 SQL을 말한다.

그리고 효과적인 데이터 구조가 중요하다.

DBA가 되고 싶다면?

• 데이터베이스 자체에 대한 연구가 중요하다.
• 데이터베이스 설치, 백업/복구, 오브젝트 생성/변경, 보안 등은 기본소양
• 데이터베이스 아키텍처를 완벽 숙지
• 데이터베이스를 운영하면서 생기는 여러 장애, 상황을 모니터링하고 해결하는 기술력과 자신만의 스크립트를 꾸준히 개발해야 한다.

SQL 튜너가 되고 싶다면?

• SQL 중심으로 공부해야 한다.
• 옵티마이저가 SQL을 파싱하고 통꼐정보를 활용해 실행계획을 생성하는 원리
• 옵티마이저 쿼리 변환 원리를 바탕으로 실행계획을 분석하는 방법
• 옵티마이저 힌트를 이용해 실행계획을 제어하는 방법
• 옵티마이저가 좋은 실행계획을 생성하도록 유도하기 위한 효과적인 SQL 작성법
• 애플리케이션에서 SQL을 실행할 때 사용하는 프로그래밍 인터페이스
• SQL을 빠르게 처리할 수 있는 좋은 데이터 구조와 파티션/인덱스 설계
• 정확성과 안정성을 확보할 수 있는 통계정보 수립 정책

SQL 튜닝이 데이터베이스 튜닝

데이터베이스 튜닝은 SQL이 병목이나 지연 없이 빠르고 안정적으로 수행되도록 조치하는 모든 활동이다.

• SQL과 친숙해져야 한다.
• 수많은 SQL과 실행계획을 분석하라!
• 좋은 데이터 구조를 설계하는 방법에 관심을 가져라!
• 수많은 시간과 노력을 기울여라!