8회차 SQL 옵티마이저

1. 통계정보와 비용 계산 원리

1.1 선택도와 카디널리티

 

🥸 선택도(Selectivity)

   = 전체 레코드 중에서 조건절에 의해 선택되는 레코드 비율

   = 1 / NDV(컬럼 값 종류 개수: Number of Distinct Values)

🥸카디널리티(Cardinality)

   = 전체 레코드 중에서 조건절에 의해 선택되는 레코드 개수

   = 총 로우 수 x 선택도 = 총 로우 수 / NDV

 

예제)
상품분류 컬럼에 '가전', '의류', '식재료', '생활용품' 네 개의 값이 있을 때 아래 조건절에 의한 선택도는 25%(1/4)이며, 전체 레코드가 10만 건이라고 가정하면 카디널리티는 2만 5천이다.

WHERE 상품분류 = '가전'
1 / 4 (NDV = '가전', '의류', '식재료', '생활용품') = 25%
100,000 / 4 = 25,000 건

 

옵티마이저는 카디널리티를 구하고, 데이터를 액세스하는 데 드는 비용을 계산해서 테이블 액세스 방식, 조인 순서, 조인 방식 등을 계산한다.

이 비용을 계산하는 출발점은 선택도이며 선택도를 잘못 계산하면 카디널리티와 비용도 잘못 계산하게 되고 비효율적인 액세스방식과 조인방식을 선택하게 된다. 선택도 계산 시에 NDV를 사용하므로 통계정보 수집 과정에서 이 값을 정확하게 구하는 것이 매우 중요해 통계정보 수집 주기, 샘플링 비율 등을 잘 결정해야 한다.

---

1.2 통계정보

💡통계정보 종류

   1️⃣ 오브젝트 통계

 

 - 테이블 통계

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

-- 수집된 테이블 통계정보 조회 ==> all_tab_statistics 뷰에서도 같은 정보 확인
select num_rows, blocks, avg_row_len, sample_size, last_analyzed
from all_tables
where owner = 'SCOTT'
and table_name = 'EMP';

/**
1. NUM_ROWS      : 테이블에 저장된 총 레코드 개수
2. BLOCKS        : 테이블 블록 수 = '사용된'(데이터가 한 건이라도 입력된 적이 있는 모든 익스텐트)
                   익스텐트에 속한 총 블록 수
                  💡 테이블에 '할당된' 총 블록 수는 dba_segments 또는 user_segments 뷰에서 
                     확인 가능
3. AVG_ROWS_LEN  : 레코드당 평균 길이(Bytes)
4. SAMPLE_SIZE   : 샘플링한 레코드 수 
5. LAST_ANALYZED : 통계정보 수집일시
**/

 

 - 인덱스 통계

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

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

-- 수집된 인덱스 통계정보 조회, all_ind_statistics 뷰에서 동일하게 확인 가능
select blevel, leaf_blocks, num_rows, distinct_keys
     , avg_leaf_blocks_per_key, avg_data_blocks_per_key, clustering_factor
     , sample_size, last_analyzed
from all_indexes
where owner      = 'SCOTT'
and   table_name = 'EMP'
and   index_name = 'EMP_X01';

/*
  -- 인덱스 수직적 탐색 비용 계산
  1. BLEVEL                  : 브랜치 레벨, 인덱스 루트에서 리프 블록에 도달하기 직전까지 읽게되는 블록 수
  -- 인덱스 수평적 탐색 비용 계산
  2. LEAF_BLOCKS             : 인덱스 리프 블록 총 개수
  3. NUM_ROWS                : 인덱스에 저장된 레코드 개수
  4. DISTINCT_KEYS           : 인덱스 키 값의 조합으로 만들어지는 값의 종류 개수
                               (C1 + C2 컬럼 구성의 인덱스이고 C1에 3개 값, 
                               C2에 4개 값이 있을 때 최대 12개의 값이 만들어진다.)
  5. AVG_LEAF_BLOCKS_PER_KEY : 인덱스 키값을 모두 '=' 조건으로 조회할 때 읽게될 리프 블록 개수
  6. AVG_DATA_BLOCKS_PER_KEY : 인덱스 키값을 모두 '=' 조건으로 조회할 때 읽게 될 테이블 블록 개수
  -- 테이블 액세스 비용 계산
  7. CLUSTERING_FACTOR       : 인덱스 키 값 기준으로 테이블 데이터가 모여있는 정도
                               인덱스 전체 코드를 스캔하면서 테이블 레코드를 찾아갈 때 테이블 블록 개수를 
                               미리 계산해 놓은 수치
*/

 

- 컬럼 통계(히스토그램 포함)

-- 컬럼 통계는 테이블 통계 수집할 때 함께 수집된다.
-- 수집된 컬럼 통계정보 조회 방법, all_tab_col_statistics 뷰에서 동일 정보 조회 가능
select num_distinct ,desity, avg_col_len, low_value, high_value, num_nulls
     , last_analyzed, sample_size
from all_tab_columns
where owner = 'SCOTT'
and table_name = 'EMP'
and column_name = 'DEPTNO';

/*

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

*/

 

☺️ 컬럼 히스토그램

'=' 조건에 대한 선택도는 1/NDV 공식으로 구하거나 미리 계산된 DENSITY 값을 이용하면 되는데 일반적인 컬럼이 아닌 데이터 분포가 균일하지 않은 컬럼에서는 공식이 들어맞지 않아 최적이 아닌 실행계획으로 이어질 수 있다. 이 때 옵티마이저는 일반적인 컬럼 통계 외에 히스토그램을 추가로 활용한다.

 

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

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

 

-- 히스토그램 수집 방법
begin
  dbms_stats.gather_table_stats('scott', 'emp'
     , cascade=>false, method_opt=>'for columns ename size 10, deptno size 4');
end;

begin
  dbms_stats.gather_table_stats('scott', 'emp'
     , cascade=>false, method_opt=>'for all columns size 75');
end;

begin
  dbms_stats.gather_table_stats('scott', 'emp'
     , cascade=>false, method_opt=>'for all columns size auto');
end;

-- 수집된 컬럼 히스토그램 조회, all_tab_histograms 뷰에서 동일 정보 조회 가능
select endpoint_value, endpoint_number
from all_histograms
where owner = 'SCOTT'
and table_name = 'EMP'
and column_name = 'DEPTNO'
order by endpoint_value;

ENDPOINT_VALUE ENDPOINT_NUMBER
-------------- ----------------
          10                3
          20                8
          30               14

   

2️⃣ 시스템 통계 

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

 

➡️ CPU 속도

➡️ 평균적인 Single Block I/O 속도

➡️ 평균적인 Multiblock I/O 속도

➡️ 평균적인 Multiblock I/O 개수

➡️ I/O 서브시스템의 최대 처리량(Throughput)
➡️ 병렬 Slave의 평균적인 처리량(Throughput)

 

과거에는 옵티마이저가 위의 항목을 고려하지 않고, 옵티마이저 개발팀이 사용한 하드웨어 사양에 맞춰진 고정 상수값으로 처리를 했는데 그러다보니 실제 오라클이 설치된 운영 시스템 사양이 그보다 좋거나 나쁠 때 옵티마이저가 최적이 아닌 실행계획을 수립할 가능성이 생겼었다. 또 애플리케이션 특성(OLTP:온라인트랜션처리, DW: 데이터 웨어하우스(?)) 및 동시 트랜잭션 발생량에 따라서도 이들 성능 특성이 달라진다. 

이에 오라클은 제품이 설치된 하드웨어 및 애플리케이션 특성을 반영해 옵티마이저가 보다 합리적으로 작동할 수 있게 하려고 9i부터 시스템 통계수집 기능을 도입하였다.

-- 시스템 통계 조회 방법
select sname, pname, pval1, pval2 from sys.aux_stats$;

 

---

1.3 비용 계산 원리

인덱스 키 값을 모두 '=' 조건으로 검색할 때 아래와 같이 인덱스 통계만으로 쉽계 비용을 계산할 수 있다.

 

비용 = BLEVEL                                                -- 인덱스 수직적 탐색 비용

         + AVG_LEAF_BLOCKS_PER_KEY       -- 인덱스 수평적 탐색 비용 

         + AVG_DATA_BLOCKS_PER_KEY       -- 테이블 랜덤 액세스 비용

 

인덱스 키 값이 모두 '=' 조건이 아닐 때 아래와 같이 컬럼 통계까지 활용한다.

 

비용 = BLEVEL                                                                  -- 인덱스 수직적 탐색 비용

         + LEAF_BLOCKS     X 유효 인덱스 선택도                -- 인덱스 수평적 탐색 비용 

         + CLUSTERING_FACTOR X 유효 테이블 선택도      -- 테이블 랜덤 액세스 비용

 

BLEVEL, LEAF_BLOCKS, CLUSTERING_FACTOR는 인덱스 통계에서 얻고, 유효 인덱스 선택도와 유효 테이블 선택도는 컬럼 통계 및 히스토그램을 이용해 계산한다.

 

😊 유효 인덱스 선택도 : 전체 인덱스 레코드 중 액세스 조건(스캔 범위를 결정하는 조건절)에 의해 선택될 것으로 예상되는 레코드 비중(%)

    => 아래 그림에서 1️⃣번 인덱스 액세스 조건에 해당

😊 유효 테이블 선택도 : 전체 인덱스 레코드 중 인덱스 컬럼에 대한 모든 조건절에 의해 선택될 것으로 예상되는 레코드 비중(%)

     => 아래 그림에서 1️⃣, 2️⃣번 조건에 의한 선택도에 해당

 

😎 비용(Cost)의 정확한 의미

위에서 설명한 비용 계산식은 I/O 비용 모델 기준이며, I/O 비용 모델 사용 시 실행계획에 나타나는 Cost는 예상 I/O Call 횟수를 의미한다.

반면, 최신 'CPU 비용 모델'에서 Cost는 Single Block I/O를 기준으로 한 상대적 시간 개념을 표현한다.

 CPU 비용 모델을 개발한 이유는 같은 실행계획으로 같은 양의 데이터를 읽어도 애플리케이션 및 하드웨어 성능 특성에 따라 절대 소요시간이 다를 수 있어서이다.

 

---

2. 옵티마이저에 대한 이해

2.1  옵티마이저 종류

비용기반(Cost-Based) 옵티마이저는 사용자 쿼리를 위해 후보군이 될만한 실행계획을 도출하고, 데이터 딕셔너리에 미리 수집해 둔 통계정보를 이용해 각 실행계획의 예상비용을 산정, 그 중 가장 낮은 비용의 실행계획 하나를 선택하는 옵티마이저이다. 

데이터량, 컬럼 값의 수, 컬럼 값 분포, 인덱스 높이, 클러스터링 팩터 등의 통계정보를 사용한다.

 

과거에는 각 액세스 경로에 대한 우선순위 규칙에 따라 실행계획을 만드는 규칙기반 옵티마이저(Rule-Based Optimizer - RBO)를 사용했다. 이는 데이터 특성을 나타내는 통계정보를 전혀 활용하지 않고 단순한 규칙에만 의존하기 때문에 대용량 데이터 처리에 부적합했다.

 

😎RBO가 사용하는 우선순위 규칙

순위	액세스 경로
1	Single Row by Rowid
2	Single Row by Cluster Join
3	Single Row by Hash Cluster Key with Unique or Primary Key
4	Single Row by Unique or Primary Key
5	Clustered Join
6	Hash Cluster Key
7	Indexed Cluster Key
8	Composite Index
9	Single-Column Indexes
10	Bounded Range Search on Indexed Columns
11	Unbounded Rang Search on Indexed Columns
12	Sort Merge Join
13	MAX or MIN of Indexed Column
14	ORDER BY on Indexed Column
15	Full Table Scan

 

RBO 규칙은 대용량 데이터베이스 환경에서는 전혀 대안이 될 수 없는데 아래 예시로 확인해보자.

 

예시1)

select *
from 고객
where 고객유형코드 = 'CC0123';

 

만약 고객 테이블의 고객유형코드에 인덱스가 있다면 RBO는 무조건 인덱스를 사용한다. Full Table Scan 순위가 15위로 가장 낮기 때문이다. 만약 고객유형코드 조건을 만족하는 고객이 전체에서 90%라면 결코 좋은선택이라 할 수 없다.

 

예시2)

select *
from 고객
order by 고객명;

 

고객명 컬럼에 인덱스가 있다면 RBO는 무조건 또 인덱스를 사용하는데 이는 인덱스 컬럼에 대한 ORDER BY 순위(14)가 Full Table Scan 순위(15)보다 한단계 높기 때문이다.

부분범위처리가 가능한 상황에서 인덱스를 이용해 소트 연산을 생략한다면 성능을 높이는데 도움이 되지만 그렇지 못한 상황에서 인덱스로 전체 레코드를 액세스하는 것은 결코 좋지 않다.

 

예시3)

select *
from 사원
where 연령 >= 60
and 연봉 between 3000 and 6000;

 

RBO 규칙에 의해 인덱스 컬럼에 대한 BETWEEN 조건(10)이 부등호 조건(11)보다 우선순위가 높기 때문에 연령과 연봉 컬럼에 각각 인덱스가 있다면 RBO는 무조건 연봉 컬럼 인덱스를 사용한다.

 

이런 RBO의 한계점 때문에 CBO(비용 기반 옵티마이저)가 탄생했다. 이후 오라클을 포함한 RDBMS 들은 CBM만 지원하고 있다.

---

2.2 옵티마이저 모드

비용기반 옵티마이저에서 최적화 목표를 설정하는 기능으로 아래 세가지 모드가 존재한다.

➡️ ALL_ROWS : 전체 처리속도 최적화

   - 쿼리 결과집합 전체를 읽는 것을 전제로 시스템 리소스(I/O, CPU, 메모리 등)을 가장 적게 사용하는 실행계획을 선택한다.

➡️ FIRST_ROWS : 최초 응답속도 최적화

   - 전체 결과집합 중 앞쪽 일부만 읽다가 멈추는 것을 전제로 응답속도가 가장 빠른 실행계획을 선택한다.

   - ALL_ROWS와 비교하면 Table Full Scan 보다 인덱스를 더 많이 선택하고, 해시 조인, 소트 머지 조인보다 NL 조인을 더 많이 선택하는 경향을 보인다.

   - 인덱스를 이용해 소트연산을 생략하는 실행계획으로 선택한다.

   - 사용자가 중간에 멈추지 않고 결과집합을 끝까지 읽는다면 테이블 스캔에 비해 전체 수행 속도가 더 느려지는 결과를 초래한다.

➡️ FIRST_ROWS_N : 최초 N 건 응답속도 최적화

   - 현재는 FIRST_ROWS 모드는 앞으로 사라지게 될(deprecated) 모드이므로 FIRST_ROWS_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) 힌트로 설정할 때는 괄호 안에 0보다 큰 어떤 정수값도 입력할 수 있다.

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

 

- FIRST_ROWS는 사용자가 데이터를 어느정도 읽다가 멈출지를 지정하지 않아 정확한 비용산정이 어렵지만 FIRST_ROWS_N은 읽을 데이터를 정확히 지정해 더 정확한 비용 산정이 가능하다.

---

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

1️⃣ SQL과 연산자 형태

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

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

   - 쿼리가 똑같아도 인덱스, IOT, 클러스터, 파티션, MV 구성여부에 따라 실행계획과 성능이 크게 달라진다.

3️⃣ 제약 설정

   - PK, FK, Check, Not Null 같은 제약들은 데이터 무결성 보장뿐 아니라 옵티마이저가 쿼리 성능을 최적화 하는데 매우 중요한 메타 정보이다.

4️⃣ 통계정보

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

5️⃣ 옵티마이저 힌트

   - 옵티마이저에게 가장 절대적인 영향을 미치는 요소로 옵티마이저는 힌트를 명령어(directives)로 인식하고 그대로 따른다.

   - 힌트가 작동하지 않는다면 아래 경우에 해당할 가능성이 높다.

     1. 문법적으로 맞지 않게 힌트를 기술

     2. 잘못된 참조 사용

     3. 의미적으로 맞지 않게 힌트를 기술

     4. 논리적으로 불가능한 액세스 경로

     5. 버그

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

   - SQL, 데이터, 통계정보, 하드웨어 등 모든 환경이 같은데 오라클 버전 업그레이드 시 옵티마이저가 다르게 작동을 한다면 대게 파라미터 추가 또는 기본값 변경이 이유일 수 있다.

-- 옵티마이저에 영향을 미치는 파라미터 목록 확인
-- 이 뷰에서 확인 가능한 목록은 극히 일부로 변경이 가능한 공식 파라미터 위주만 보여준다.
select name, value, isdefault, default_value
from v$sys_optimizer_env

 

---

2.4 옵티마이저의 한계

SQL 옵티마이저는 DBA가 통계정보를 아무리 잘 수집하고 개발자가 SQL을 아무리 잘 작성해도 실수가 있기 마련이다.

같은 SQL이어도 DBMS, 버전에 따라 옵티마이저가 다른 실행계획을 생성할 수 있기 때문에 그 선택이 항상 최선이 아닐 수 있다.

또한 옵티마이저 행동에 가장 큰 영향을 미치는 통계정보를 필요한 수만큼 충분히 확보하는 것도 불가능한 일이며 통계정보를 아무리 완벽하게 수집해도 바인드 변수를 사용한 SQL에 컬럼 히스토그램을 활용할 수 없다는 단점도 있다.

이와같은 여러가지 이유로 SQL 최적화 기법이 과거와 비교해 아주 많이 좋아졌지만 앞으로도 옵티마이저는 불완전할 수 밖에 없다.

---

2.5 개발자의 역할

2.4 의 이유로 불완전한 옵티마이저에만 의존할 것이 아닌 개발자 스스로 옵티마이저가 되어야 한다.

최적화를 옵티마이저에게 맡기지만 그 결과물이 올바른지 실행계획을 통해 늘점검하고 더 개선할 여지는 없는지 찾으려고 노력해야 한다.

 

😎 RDBMS 환경에서 SQL 성능을 높이기 위해 개발자가 할 일

   ➡️ 필요한 최소 블록만 읽도록 쿼리를 작성한다.

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

      - 서브쿼리의 경우 가능하다면 출력 대상 집합을 확정 짓고 나서 조인하도록 한다.

   ➡️ 최적의 옵티마이징 팩터를 제공한다.

      - 전략적인 인덱스를 구성한다.

      - DBMS가 제공하는 다양한 기능을 활용한다. ( 파티션, 클러스터, IOT, MV, Result, Cache 등)

      - 옵티마이저 모드를 포함한 각종 파라미터를 적절한 값으로 설정한다.

      - 정확하고 안정적인 통계정보를 잘 수집하게끔 한다.

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