옵티마이저와 통계 - 선택도·카디널리티·히스토그램
이 장에서 배우는 것
앞 장에서는 NL·소트 머지·해시 조인이 각각 어떤 방식으로 두 로우 집합을 묶는지 살펴봤다. 그런데 옵티마이저가 왜 그 조인 방식을 골랐는지, 왜 특정 테이블을 먼저 읽기로 했는지를 이해하려면 한 단계 더 들어가야 한다. 그 판단의 근거가 통계 정보다. 통계가 실제 데이터 분포와 맞지 않으면 조인 순서도, 스캔 방식도 어긋난다. 이 장에서는 비용 기반 옵티마이저(Cost-Based Optimizer, CBO)가 통계를 어떻게 활용해 선택도와 카디널리티를 계산하는지, 그리고 히스토그램(histogram)이 언제 필요한지를 다룬다.
- 옵티마이저가 실행계획을 세울 때 참고하는 통계 정보 항목을 구분할 수 있다
- 선택도(selectivity)와 카디널리티(cardinality)를 직접 계산할 수 있다
- 값의 분포가 치우친 컬럼에 히스토그램이 왜 필요한지 설명할 수 있다
- 통계가 실제와 다를 때 실행계획이 어떻게 어긋나는지 사례로 파악한다
문제 상황
온라인 서점의 정산 담당자가 매일 아침 반품 주문 목록을 조회하는 리포트 쿼리를 돌린다. 주문 테이블은 천만 건 규모이고 주문 상태(ORDER_STATUS) 컬럼에는 인덱스가 걸려 있다. 평소에는 이 인덱스를 타고 몇 초 안에 끝나던 쿼리가 어느 날부터 갑자기 테이블 전체를 훑는 실행계획으로 바뀌어 몇 분씩 걸리기 시작했다. DBA가 통계를 다시 수집했는데도 결과는 그대로다. 원인은 간단하다. 주문 상태는 '배송완료'가 90%를 차지하고 '반품'은 2%밖에 안 되는데, 옵티마이저는 통계에 히스토그램이 없어서 네 가지 상태값이 균등하게 25%씩 분포한다고 가정해 버린 것이다. 이번 장에서 쓰는 예제 테이블과 인덱스는 다음과 같다.
CREATE TABLE orders_stat_demo (
order_id NUMBER NOT NULL,
member_id NUMBER NOT NULL,
order_date DATE NOT NULL,
order_status VARCHAR2(10) NOT NULL,
total_amount NUMBER(10,0) NOT NULL,
CONSTRAINT pk_orders_stat_demo PRIMARY KEY (order_id)
);
CREATE INDEX ix_orders_stat_status
ON orders_stat_demo (order_status);비용 기반 옵티마이저와 통계 정보 항목
Oracle 19c의 옵티마이저는 여러 실행 경로 중 하나를 고르기 위해 각 경로의 예상 비용을 계산한다. 비용은 예상 I/O 횟수와 CPU 사용량을 합쳐 추정한 값이며, 이 추정치가 정확하려면 테이블에 몇 건의 행이 있는지, 컬럼 값이 몇 가지나 되는지, 값들이 어떻게 분포하는지를 알아야 한다. 이 정보를 모아 둔 것이 통계 정보이고, DBMS_STATS 패키지로 수집한다. 예전 버전에서 쓰던 규칙 기반 옵티마이저(Rule-Based Optimizer)는 이미 오래전에 폐지됐으므로 지금은 통계 없이는 실행계획을 제대로 세울 수 없다고 봐도 된다.
통계는 크게 테이블 통계, 컬럼 통계, 인덱스 통계로 나뉜다.
| 구분 | 대표 항목 | 의미 |
|---|---|---|
| 테이블 통계 | NUM_ROWS, BLOCKS, AVG_ROW_LEN | 테이블 전체 규모 |
| 컬럼 통계 | NDV, NUM_NULLS, DENSITY, HISTOGRAM | 선택도 계산의 기초 |
| 인덱스 통계 | BLEVEL, LEAF_BLOCKS, CLUSTERING_FACTOR | 인덱스 스캔 비용 산정 |
이 중 컬럼 통계의 NDV(Number of Distinct Values, 컬럼이 가진 서로 다른 값의 개수)가 이번 장의 핵심이다. 히스토그램이 없을 때 옵티마이저는 값이 NDV개만큼 균등하게 분포한다고 가정하기 때문이다.
MySQL 8도 비용 기반 옵티마이저를 쓰지만 통계를 관리하는 방식과 명령어가 다르다.
| 항목 | Oracle 19c | MySQL 8 | 비고 |
|---|---|---|---|
| 통계 수집 명령 | DBMS_STATS.GATHER_TABLE_STATS | ANALYZE TABLE | 수집 범위 지정 방식이 다름 |
| 히스토그램 종류 | FREQUENCY, TOP-FREQUENCY, HYBRID | 등폭(equi-height) 단일 방식 | Oracle 쪽이 분포 표현이 세밀함 |
| 히스토그램 생성 문법 | METHOD_OPT => 'FOR COLUMNS 컬럼 SIZE n' | ANALYZE TABLE t UPDATE HISTOGRAM ON 컬럼 | 문법 자체가 다름 |
| 자동 통계 수집 | 야간 자동 잡(GATHER_STATS_JOB) | innodb_stats_auto_recalc | MySQL은 변경 비율 기준으로 재수집 |
선택도와 카디널리티 계산
선택도(selectivity)는 조건을 만족하는 행이 전체 행 중 차지하는 비율이고, 카디널리티(cardinality)는 그 비율을 실제 행 수로 환산한 값이다. 계산식은 다음과 같다.
선택도 = 조건을 만족하는 행 수 / 전체 행 수
카디널리티 = 선택도 × 전체 행 수문제는 옵티마이저가 히스토그램 없이 등치 조건(=)의 선택도를 구할 때 1 / NDV 공식을 쓴다는 점이다. 주문 상태 컬럼의 NDV가 4('배송완료', '배송중', '주문취소', '반품')이므로, 전체 행이 10만 건이라면 옵티마이저는 어떤 상태값을 조건으로 걸어도 선택도 25%, 카디널리티 25,000건으로 추정한다. 그러나 실제 분포는 그렇지 않다.
그림에서 보듯 실제로는 '배송완료'가 90%를 차지하고 '반품'은 2%에 불과하다. 그런데도 히스토그램이 없으면 옵티마이저는 네 값 모두 25%라고 가정한다. '반품' 조건의 카디널리티가 실제 2,000건인데 25,000건으로 12배 이상 부풀려 추정되는 셈이다. 이렇게 부풀려진 추정치는 인덱스 범위 스캔 대신 테이블 전체 스캔을 고르게 만드는 원인이 된다.
히스토그램이 필요한 분포와 통계가 틀렸을 때 생기는 일
히스토그램은 컬럼 값의 분포를 구간(버킷)별로 저장해 두는 통계다. 값의 종류가 적고 특정 값에 쏠려 있는 컬럼, 즉 카디널리티가 낮으면서 분포가 균등하지 않은 컬럼에 필요하다. 반대로 회원 번호처럼 값이 거의 유일하고 고르게 흩어져 있는 컬럼에는 히스토그램이 크게 도움이 되지 않는다. 오히려 불필요한 히스토그램은 통계 수집 시간만 늘리고 옵티마이저가 참고할 정보만 늘려 파싱 비용을 더할 수 있다.
Oracle 19c는 값의 종류 수에 따라 히스토그램 종류를 자동으로 고른다. 서로 다른 값이 254개 이하면 값마다 정확한 비율을 저장하는 FREQUENCY 히스토그램을, 그보다 많으면 상위 빈도 값만 따로 저장하는 TOP-FREQUENCY나 구간을 나눠 저장하는 HYBRID 히스토그램을 만든다. 주문 상태처럼 NDV가 4인 컬럼은 FREQUENCY 히스토그램이 만들어지고, 값별 실제 비율(90%, 5%, 3%, 2%)이 그대로 저장된다.
통계가 실제와 어긋나면 다음과 같은 문제가 이어진다. 카디널리티를 과대평가하면 인덱스 스캔이 유리한 상황에서도 테이블 전체 스캔을 고르고, 반대로 과소평가하면 실제로는 많은 행을 걸러야 하는데도 인덱스를 반복 접근해 오히려 느려진다. 조인이 섞인 쿼리라면 문제가 더 커진다. 한 테이블의 카디널리티 추정이 틀리면 그 뒤에 이어지는 조인 순서와 조인 방식 선택까지 연쇄적으로 어긋나기 때문이다.
완성 코드
-- 1) 예제 데이터 적재: 주문 상태를 의도적으로 치우치게 구성
BEGIN
FOR i IN 1 .. 100000 LOOP
INSERT INTO orders_stat_demo
(order_id, member_id, order_date, order_status, total_amount)
VALUES (
i,
TRUNC(DBMS_RANDOM.VALUE(1, 50000)),
SYSDATE - DBMS_RANDOM.VALUE(0, 365),
CASE
WHEN DBMS_RANDOM.VALUE(0, 100) < 90 THEN '배송완료'
WHEN DBMS_RANDOM.VALUE(0, 100) < 95 THEN '배송중'
WHEN DBMS_RANDOM.VALUE(0, 100) < 98 THEN '주문취소'
ELSE '반품'
END,
TRUNC(DBMS_RANDOM.VALUE(9000, 89000))
);
END LOOP;
COMMIT;
END;
/
-- 2) 히스토그램 없이 통계 수집
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => USER,
tabname => 'ORDERS_STAT_DEMO',
method_opt => 'FOR ALL COLUMNS SIZE 1',
cascade => TRUE);
END;
/
SET LINESIZE 160
SET PAGESIZE 100
EXPLAIN PLAN FOR
SELECT order_id, order_date, total_amount
FROM orders_stat_demo
WHERE order_status = '반품';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'BASIC ROWS COST'));
-- 3) 반품처럼 치우친 컬럼에 히스토그램을 포함해 통계 재수집
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => USER,
tabname => 'ORDERS_STAT_DEMO',
method_opt => 'FOR COLUMNS ORDER_STATUS SIZE 254',
cascade => TRUE);
END;
/
EXPLAIN PLAN FOR
SELECT order_id, order_date, total_amount
FROM orders_stat_demo
WHERE order_status = '반품';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'BASIC ROWS COST'));줄별 해설
1번 블록은 10만 건의 주문을 만들면서 DBMS_RANDOM.VALUE로 상태값 비율을 90/5/3/2로 고정한다. 실무 데이터에서 흔히 보이는 쏠림을 재현하기 위한 장치다. 2번 블록의 method_opt => 'FOR ALL COLUMNS SIZE 1'은 모든 컬럼의 통계는 수집하되 히스토그램은 만들지 말라는 뜻이다. SIZE 1이 곧 히스토그램을 생성하지 않는다는 의미다. 이 상태에서 실행계획을 뽑으면 옵티마이저는 앞서 설명한 대로 '반품' 조건의 카디널리티를 25,000건으로 잘못 추정한다.
3번 블록의 method_opt => 'FOR COLUMNS ORDER_STATUS SIZE 254'는 ORDER_STATUS 컬럼 하나에만 최대 254개 버킷까지 쓸 수 있는 히스토그램을 만들라는 지정이다. NDV가 4이므로 실제로는 값별 정확한 비율을 담은 FREQUENCY 히스토그램이 만들어진다. 이후 같은 쿼리를 다시 EXPLAIN PLAN하면 카디널리티 추정이 2,000건 근처로 바뀌고 실행계획도 달라진다.
실행 결과
-- 히스토그램 없이 통계 수집한 뒤의 실행계획
Plan hash value: 1234567890
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 25000 | 210 (2)|
|* 1 | TABLE ACCESS FULL| ORDERS_STAT_DEMO | 25000 | 210 (2)|
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ORDER_STATUS"='반품')-- 반품 컬럼에 히스토그램을 추가한 뒤의 실행계획
Plan hash value: 987654321
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2000 | 45 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| ORDERS_STAT_DEMO | 2000 | 45 (0)|
|* 2 | INDEX RANGE SCAN | IX_ORDERS_STAT_STATUS | 2000 | 6 (0)|
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ORDER_STATUS"='반품')Rows 열의 추정치가 25,000에서 2,000으로 바뀌면서 Operation 자체가 TABLE ACCESS FULL에서 INDEX RANGE SCAN으로 바뀐 것을 볼 수 있다. 통계가 실제 분포를 담게 되면서 비용 계산이 뒤집힌 것이다.
실무에서 자주 틀리는 것
모든 컬럼에 히스토그램을 습관적으로 만든다
쏠림이 없는 컬럼까지 히스토그램을 만들면 통계 수집 시간과 파싱 비용만 늘어난다.
-- 틀린 방식: 전체 컬럼에 무조건 254 버킷
method_opt => 'FOR ALL COLUMNS SIZE 254'
-- 고친 방식: 옵티마이저가 쏠림이 있는 컬럼만 골라 히스토그램을 만들게 함
method_opt => 'FOR ALL COLUMNS SIZE AUTO'대량 적재 후 통계를 그대로 방치한다
야간 자동 통계 수집 잡을 기다리는 동안 부정확한 통계로 하루 종일 잘못된 실행계획이 돌 수 있다.
-- 틀린 방식: 배치 적재만 하고 끝
INSERT INTO orders_stat_demo SELECT * FROM staging_orders;
COMMIT;
-- 고친 방식: 적재 직후 직접 통계 재수집
INSERT INTO orders_stat_demo SELECT * FROM staging_orders;
COMMIT;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDERS_STAT_DEMO', cascade => TRUE);
END;
/바인드 변수를 쓰면 히스토그램이 항상 잘 맞는다고 생각한다
바인드 변수를 쓰는 쿼리는 최초 실행 시 넘어온 값을 기준으로 실행계획이 고정될 수 있다. '배송완료'로 처음 파싱된 커서가 그대로 '반품' 조회에도 재사용되면 히스토그램이 있어도 최적 계획이 나오지 않는다.
-- 문제가 될 수 있는 형태: 값에 따라 최적 계획이 달라지는데 커서를 공유
SELECT order_id FROM orders_stat_demo WHERE order_status = :status;
-- 대안: 쏠림이 큰 조건은 배치 리포트성 쿼리에서 리터럴로 분리해 작성
SELECT order_id FROM orders_stat_demo WHERE order_status = '반품';NULL이 많은 컬럼도 NDV 기준으로만 선택도를 계산한다
NUM_NULLS를 무시하고 1/NDV만 적용하면 NULL이 아닌 값의 실제 비율보다 선택도를 낮게 잡아 카디널리티를 과소평가한다.
-- 틀린 계산: NULL 비율을 고려하지 않음
선택도 = 1 / NDV
-- 고친 계산: NULL이 아닌 행 비율을 반영
선택도 = (1 - NUM_NULLS / NUM_ROWS) / NDV한눈에 보기
| 개념 | 핵심 정의 | 확인 방법 |
|---|---|---|
| 선택도 | 조건을 만족하는 행의 비율 | USER_TAB_COL_STATISTICS |
| 카디널리티 | 옵티마이저가 추정한 결과 행 수 | 실행계획의 Rows 열 |
| 히스토그램 | 컬럼 값 분포를 구간별로 저장한 통계 | USER_TAB_HISTOGRAMS |
| 통계 수집 | DBMS_STATS로 데이터 분포를 옵티마이저에 제공 | USER_TAB_STATISTICS.LAST_ANALYZED |
연습 문제
- 어떤 테이블의 NUM_ROWS가 200,000이고, 특정 컬럼의 NDV가 5, 히스토그램이 없다고 할 때 이 컬럼에 대한 등치 조건의 선택도와 카디널리티를 구하라.
- 한 컬럼의 실제 값 분포가 A 78%, B 15%, C 5%, D 2%로 나타났다. 이 컬럼에 히스토그램이 필요한지 판단하고 그 이유를 설명하라.
- 통계를 재수집한 직후인데도 실행계획의 Rows 값이 실제 결과 행 수와 크게 차이 난다. 어떤 부분을 먼저 점검해야 하는지 두 가지를 제시하라.
method_opt => 'FOR ALL COLUMNS SIZE 254'를 모든 테이블에 일괄 적용하는 배치 스크립트가 있다. 이 방식의 문제점과 대안을 제시하라.
정답과 해설
1번 히스토그램이 없으므로 선택도는 1/NDV = 1/5 = 20%다. 카디널리티는 선택도 × NUM_ROWS = 200,000 × 0.2 = 40,000건이다.
2번 히스토그램이 필요하다. 값이 균등하지 않고 A에 78%가 몰려 있어, 히스토그램 없이 1/NDV(25%)로 가정하면 A 조회는 과소평가되고 D 조회는 과대평가된다. NDV가 4로 254 이하이므로 FREQUENCY 히스토그램이 적합하다.
3번 먼저 조건절에 사용된 컬럼에 히스토그램이 실제로 생성됐는지 USER_TAB_HISTOGRAMS로 확인한다. 다음으로 바인드 변수를 쓰는 쿼리라면 최초 파싱 시 넘어온 값이 무엇이었는지, 커서가 그 값 기준으로 고정돼 있지 않은지 확인한다.
4번 모든 테이블, 모든 컬럼에 254 버킷 히스토그램을 강제로 만들면 통계 수집 시간이 크게 늘고 쏠림이 없는 컬럼에는 아무 이득이 없다. SIZE AUTO로 바꿔 옵티마이저가 실제로 쏠림이 있는 컬럼만 골라 히스토그램을 만들게 하는 편이 낫다.