옵티마이저와 비용

#핵심 요약

옵티마이저는 통계정보로 조건의 선택도, 예상 결과 건수(카디널리티), 비용을 차례로 계산하고 비용이 가장 낮은 실행계획을 고름

선택도는 "얼마나 걸러지는가", 카디널리티는 "몇 건이 나오는가"(전체 행 수 × 선택도), 비용은 "어떤 경로가 더 효율적인가"임

Cost는 초 단위 시간이 아니라 같은 시스템 안에서 계획들을 비교하기 위한 상대적 지표임

전체를 끝까지 읽으면 ALL_ROWS, 앞쪽 N건만 빨리 보여 주면 FIRST_ROWS_N 모드가 맞음

통계정보와 최적화 시간에 한계가 있어 가장 빠른 실행계획을 고르지 못할 수 있음

#개념과 동작 원리

#RBO와 CBO

#옵티마이저 모드

#선택도, 카디널리티, 비용의 예

위 수치는 개념 예시임
느린 SQL의 진단 예에서는 응답 시간 12.45s, 예상 1건(E-Rows), 실제 100만 건(A-Rows)의 차이를 제시함
진단 순서는 느린 SQL 확인 → 예상, 실제 카디널리티 비교 → 비용 판단 근거 확인임
인덱스가 있어도 비용이 더 크면 Full Scan을 선택할 수 있으며 통계정보 미갱신과 카디널리티 오차를 확인함

#비용 계산의 입력과 비교 기준

후보 실행 경로 생성 → 각 경로의 예상 Cost 계산 → 가장 낮은 Cost의 계획 선택 순서임
과거의 I/O 중심 설명은 Cost를 예상 I/O Call 횟수로 표현하며 현재의 CPU + I/O 비용 모델 설명은 Single Block I/O를 기준으로 한 상대 시간으로 표현함
Cost 100의 예시는 해당 시스템에서 Single Block I/O 약 100번 정도의 시간이라는 의미임
초 단위의 절대 실행 시간이나 실제 I/O Call 100회가 수행됐다는 출력으로 읽지 않음

클러스터링 팩터 설명의 좋은 경우는 인덱스 순서와 비슷하게 관련 데이터가 가까운 테이블 블록에 모여 있어 적은 블록을 읽는 경우임
나쁜 경우는 인덱스 키 순서가 정렬되어 있어도 테이블 데이터가 여러 블록에 흩어져 한 건을 읽을 때마다 다른 블록을 찾아가는 경우임
설명의 표현은 좋은 경우를 군집성 계수 높음, 나쁜 경우를 군집성 계수 낮음으로 부르며 수치형 CLUSTERING_FACTOR의 수치 크기와는 다른 표현임

#인덱스 비용 참고식

'=' 조건: Cost = BLEVEL + AVG_LEAF_BLOCKS_PER_KEY + AVG_DATA_BLOCKS_PER_KEY
범위 검색: Cost = BLEVEL + (LEAF_BLOCKS × 유효 인덱스 선택도)
               + (CLUSTERING_FACTOR × 유효 테이블 선택도)

설명: BLEVEL은 인덱스 루트에서 리프까지 내려가는 깊이, 리프 블록 수는 키를 찾으려고 읽는 수평 탐색 비용, 마지막 항은 테이블 블록 접근 비용임
CLUSTERING_FACTOR가 좋다는 것은 인덱스 순서와 테이블 저장 순서가 비슷해 관련 데이터가 가까운 블록에 모여 있다는 뜻임
확인 사항: CLUSTERING_FACTOR가 테이블 행 수에 가까우면 군집도가 나쁘고, 테이블 블록 수에 가까우면 좋음

#옵티마이저 판단에 영향을 주는 요소

1. SQL과 연산자 형태: 옵티마이저는 작성된 SQL 안에서만 최적화함
2. 인덱스, IOT, 클러스터, 파티션, MV 같은 구조: 선택할 수 있는 경로를 늘리거나 제한함
3. 제약(PK, FK, CHECK, NOT NULL): 중복, NULL 가능 여부, 조인 관계를 더 정확히 판단하게 함
4. 통계정보: 비용 계산의 핵심 재료
5. 힌트: 특정 경로를 유도하거나 강제함
   문법 오류, 잘못된 별칭, 불가능한 경로면 적용되지 않음
6. 옵티마이저 파라미터와 DBMS 버전: 바뀌면 같은 SQL도 다른 계획이 나올 수 있어 업그레이드 뒤 회귀 테스트가 필요함
   환경은 V$SYS_OPTIMIZER_ENV, V$SES_OPTIMIZER_ENV에서 봄

#옵티마이저의 한계와 개발자의 역할

한계: 통계는 전체 데이터를 완벽히 반영하지 못하고, 바인드 변수를 쓰면 실제 값 분포를 반영하기 어렵고, 애매하면 내부 가정에 의존하며, 모든 후보를 검토할 시간이 없음

개발자의 역할: 필요한 최소 블록만 읽는 SQL 작성, 조건과 업무 패턴에 맞는 인덱스, 파티션, 통계 제공, 필요하면 힌트로 경로 유도, 실행계획을 직접 확인하고 검증

#주요 용어와 구성 요소

#필수 명령어 또는 SQL

ALTER SESSION SET optimizer_mode = all_rows;
ALTER SESSION SET optimizer_mode = first_rows_100;

SELECT /*+ FIRST_ROWS(20) */ col1, col2
  FROM t;

SELECT index_name, blevel, leaf_blocks, avg_leaf_blocks_per_key,
       avg_data_blocks_per_key, clustering_factor
  FROM user_indexes
 WHERE table_name = '<TABLE_NAME>';

SELECT table_name, num_rows, blocks
  FROM user_tables
 WHERE table_name = '<TABLE_NAME>';

결과 해석: 두 번째 조회의 NUM_ROWS, BLOCKS와 첫 조회의 CLUSTERING_FACTOR를 비교해 군집도를 가늠함
바꿀 입력값: <TABLE_NAME>

#옵티마이저 모드 실습: 같은 SQL, 다른 계획

상담이력 50만 건, 상담이력_X01 (접수일시) 인덱스에서 최근 접수 순 첫 20건만 보여 주는 화면을 가정함

ALTER SESSION SET optimizer_mode = all_rows;

SELECT /*+ GATHER_PLAN_STATISTICS */ *
  FROM (SELECT 상담번호, 고객번호, 접수일시, 상담유형코드
          FROM 상담이력
         ORDER BY 접수일시 DESC)
 WHERE ROWNUM <= 20;

SELECT /*+ GATHER_PLAN_STATISTICS FIRST_ROWS(20) */ *
  FROM (SELECT 상담번호, 고객번호, 접수일시, 상담유형코드
          FROM 상담이력
         ORDER BY 접수일시 DESC)
 WHERE ROWNUM <= 20;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

결과 해석:

수업 자료의 예시 계획: ALL_ROWS는 TABLE ACCESS FULL(A-Rows 500K) 뒤 SORT ORDER BY STOPKEY, FIRST_ROWS(20)은 INDEX FULL SCAN DESCENDING으로 정렬 없이 20건에서 멈춤(COUNT STOPKEY)
예시의 Buffers는 "15000+"와 25처럼 어림값으로 적혀 있어 실측값으로 보지 않음

optimizer_mode 파라미터의 N은 1, 10, 100, 1000만 되고, FIRST_ROWS(n) 힌트는 0보다 큰 정수면 됨

반대로 끝까지 읽는 작업에 FIRST_ROWS_N 계획(인덱스로 전량 랜덤 액세스)을 쓰면 Full Scan과 정렬보다 훨씬 느려짐

연습 문제: 온라인 화면을 위해 로그온 트리거가 모든 세션에 optimizer_mode = first_rows_10을 걸자, 300만 건을 ORDER BY 가맹점번호로 끝까지 추출하는 야간 배치가 INDEX FULL SCAN을 타며 20분에서 3시간으로 늘어남
트리거는 그대로 두고 배치 SQL에만 /*+ ALL_ROWS */를 넣어 문장 단위로 목표를 되돌리는 것이 수업 자료의 답이며, 힌트로 준 모드가 세션 설정보다 우선함

#클러스터링 팩터 실습

-- 대출기록 100만 건: MOD(ROWNUM, 10000)으로 회원 데이터가 전체 블록에 흩어짐
-- 인덱스 대출기록_X1 (회원번호, 대출일자)
CREATE TABLE 대출기록_정렬 AS
SELECT * FROM 대출기록 ORDER BY 회원번호, 대출일자;

CREATE INDEX 대출기록_정렬_X1 ON 대출기록_정렬 (회원번호, 대출일자);
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, '대출기록_정렬', CASCADE => TRUE);

SELECT index_name, clustering_factor
  FROM user_indexes
 WHERE table_name IN ('대출기록', '대출기록_정렬');

결과 해석:

실습 기록: 회원번호 = 777 AND 대출일자 >= SYSDATE - 356로 50건을 읽을 때 INDEX RANGE SCAN Buffers는 둘 다 3이었고, 테이블까지 합친 Buffers는 흩어진 대출기록 53, 인덱스 순서로 다시 만든 대출기록_정렬 5였음
SQL과 인덱스를 못 바꿀 때 테이블 액세스 단계의 Buffers만 높다면 군집도를 의심하고, 인덱스 순서로 데이터를 다시 적재하는 방법을 검토함(연습 문제: 오픈 초기에는 매장별로 뭉쳐 있다가 이후 시간순으로 섞여 쌓인 포인트적립이력)

#제약과 파라미터가 계획을 바꾸는 실습

ALTER TABLE dept ADD CONSTRAINT dept_deptno_pk PRIMARY KEY (deptno);
ALTER TABLE emp  ADD CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno) REFERENCES dept (deptno);

SELECT e.empno, e.ename, e.sal
  FROM emp e, dept d
 WHERE e.deptno = d.deptno;

결과 해석:

실습 기록: 제약이 없을 때는 HASH JOIN(DEPT, EMP 각각 Full Scan, Buffers 13)이었고, PK와 FK를 건 뒤에는 DEPT 조인이 사라지고 EMP Full Scan만 남았음(Buffers 6)
FK가 "조인해도 건수가 변하지 않음"을 보장해 결과에 영향 없는 테이블을 조인에서 뺄 수 있었음

-- emp_big 10만 건, emp_big_sal_idx (sal)
ALTER SESSION SET optimizer_features_enable = '8.1.7';
ALTER SESSION SET optimizer_index_cost_adj = 100;   -- 실습: TABLE ACCESS FULL
ALTER SESSION SET optimizer_index_cost_adj = 1;     -- 실습: INDEX RANGE SCAN

EXPLAIN PLAN FOR
SELECT * FROM emp_big WHERE sal BETWEEN 0 AND 10500;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

결과 해석:

optimizer_index_cost_adj는 인덱스 경로 비용을 백분율로 깎아 인덱스를 더 고르게 만듦
실습에서는 거의 모든 행을 읽는 범위인데도 값을 1로 낮추자 인덱스 범위 스캔으로 바뀌었음

시스템 전체에 ALTER SYSTEM SET optimizer_mode = first_rows_1을 거는 연습도 있었음
시스템 수준 변경은 모든 SQL의 계획에 영향을 주므로 운영에서는 영향 범위를 먼저 확인함

#바인드 변수와 히스토그램 실습

-- emp500: ename 값이 한쪽으로 쏠림, emp500_ename 인덱스
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'emp500', method_opt => 'for all columns size 255');

EXPLAIN PLAN FOR SELECT * FROM emp500 WHERE ename = 'scott1';
EXPLAIN PLAN FOR SELECT * FROM emp500 WHERE ename = 'scott99';
EXPLAIN PLAN FOR SELECT * FROM emp500 WHERE ename = :v_ename;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

결과 해석:

실습 기록: 통계를 지운 상태에서는 두 값 모두 INDEX RANGE SCAN이었고, 히스토그램을 수집한 뒤에는 scott1은 인덱스, scott99는 TABLE ACCESS FULL로 갈렸음
바인드 변수로 바꾼 마지막 문장은 파싱 시점에 값을 몰라 히스토그램을 값별로 쓰지 못하는 경우를 보는 실습이며, 그 결과는 기재 없음

수업 자료의 다른 예: 주문 상태 코드 한 값이 90%를 차지하는 T_ORDER에서 Full Scan과 인덱스 강제 계획을 비교하는 실습이 있음(결과 수치 기재 없음)

최적화 시간의 한계는 SQL 튜닝 어드바이저처럼 시간을 더 들여 분석하는 도구로 보완함(통계정보와 실행계획 변화)

#구문과 옵션 설명

OPTIMIZER_MODE의 기본값은 ALL_ROWS임
FIRST_ROWS_1, FIRST_ROWS_10, FIRST_ROWS_100, FIRST_ROWS_1000은 초기 행 응답을 목표로 함
FIRST_ROWS는 이전 버전 호환성과 계획 안정성을 위해 유지되는 값이며 초기 행 최적화에는 FIRST_ROWS_n을 검토함
FIRST_ROWS의 폐지 일정은 기재 없음

FIRST_ROWS(n)은 최적화 목표이며 결과 행 수를 제한하지 않음
실제 행 수를 제한하려면 ROWNUM 같은 조건이 필요함

기존 인덱스 비용 식은 구성 요소를 이해하기 위한 참고식임
CPU, I/O 등 자원과 통계가 반영되는 실제 내부 비용을 그 식만으로 정확히 재현한다고 보지 않음

#사용 조건과 주의사항

서로 다른 SQL이나 다른 옵티마이저 모드의 Cost 값을 실행 시간 순위로 직접 비교하지 않음
바인드를 사용한다고 항상 값을 전혀 참고하지 못하는 것은 아니며 바인드 피킹과 ACS가 적용될 수 있음
EXPLAIN PLAN의 바인드 추정과 실제 커서 계획은 구분함

조인 제거는 제거할 테이블이 결과를 거르거나 늘리지 않는다는 보장이 전제임
조인 제거 여부를 판단할 때는 PK와 FK 외에 NULL 허용 여부와 제약 상태도 확인해야 함

이전 버전 옵티마이저 기능과 전역 파라미터를 바꾸는 기존 실습은 현재 버전의 일반 권장 설정으로 쓰지 않음