[DB] 7장. SQL 옵티마이저

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

선택도와 카디널리티에 이어 통계정보에 구체적으로 어떤 항목들이 있는지 살펴보고, 옵티마이저가 통계정보를 이용해 비용을 계산하는 원리를 간략히 살펴보자.

🔎 선택도와 카디널리티

⚙️ 선택도(Selectivity)

• 전체 레코드 중에서 조건절에 의해 선택되는 레코드 비율
• 단순 '='조건으로 검색할 때의 선택도만 살펴보면, 컬럼 값 종류 개수( Number of Distinct Values NDV)를 이용해 구한다.

선택도 = 1 / NDV

⚙️ 카티널리티 (Cardinality)

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

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

✨예시로 상품분류 컬럼에 가전, 의류, 식음료, 생활용품의 값이 있을 때, 조건절에 대한 선택도는 25%(=1/4)이며, 전체 레코드가 10만건이면, 카티널리티는 2만5천이다. 

> WHERE 상품분류 = '가전'

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

비용을 계산하는 시작점은 선택도이며, 선택도를 잘못 계산하면 카티널리티, 비용도 잘못 계산하게되고, 결과적으로 비효율적인 액세스방식과 조인 방식을 선택하게 된다! 

선택도를 계산할 때 NDV를 사용하므로 통계정보 수집과정에서 값을 정확히 구하는 것이 매우 중요하며, 통계정보 수집주기, 샘플링 비율등을 잘 결정해야 한다.

🔎 통계정보

• 통계정보에는 오브젝트 통계와 시스템통계가 있으며, 오브젝트 통계에는 테이블 통계, 인덱스 통계, 컬럼 통계(히스토그램 포함)로 나뉜다.

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';

 

🚩 주요 테이블 통계항목에 대한 설명

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

2. 인덱스 통계

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

-- 테이블 통계를 수집하면서 인덱스 통계도 같이 수집
begin
	dbms_stats.gather_index_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';

🚩 주요 인덱스 통계항목에 대한 설명

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

3. 컬럼통계

• 테이블 통계 수집할 때 함께 수집된다.

--all_tab_col_statistics 뷰에서 조회가능
select num_distinct, density, 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';

🚩 주요 컬럼 통계항목에 대한 설명

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

⚙️컬럼 히스토그램

• '='조건에 대한 선택도는 "1/NUM_DISTINCT" 공식을 구하거나 미리 구해놓은 DENSITY값을 이용하면 된다.
• 선택도를 잘못 구하면, 액세스 비용을 잘못 산정하게 되고, 결국 최적이 아닌 실행계획으로 이어지므로, 옵티마이저는 일반적인 컬럼통계외에 히스토그램을 추가적으로 활용한다.
• 히스토그램은 컬럼 값별로 데이터 비중 또는 빈도를 미리 계산해 놓은 통계정보이며, 실제 데이터를 읽어서 계산해둔 값이므로 데이터 분포가 많이 변하지 않는 한 거의 정확하다.

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

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

히스토그램을 수집하려면, 테이블 통계 수집할 때 method_opt 파리미터를 지정하면 된다.

4. 시스템통계

• 애플리케이션 및 하드웨어 성능 특성을 측정한 것

⚙️ 시스템통계 항목

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

🔎 비용 계산 원리

옵티마이저가 통계정보를 어떤 식으로 활용하는지, 단일 테이블을 인덱스로 액세스할 때의 비용 계산 원리를 간단히 살펴보면,

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

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

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

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

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

✨ 유효인덱스 선택도
전체 인덱스 레코드 중 액세스 조건(스캔범위를 결정하는 조건절)에 의해 선택될 것으로 예상되는 레코드 비중(%)을 의미한다.
✨ 유효테이블 선택도
전체 인덱스 레코드 중 인덱스 컬럼에 대한 모든 조건절에 의해 선택될 것으로 예상되는 레코드 비중(%)을 의미한다.

---

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

🔎 옵티마이저 종류

1. 비용 기반(Cost-Based) 옵티마이저

• 사용자 쿼리를 위해 후보군이 될만한 실행계획들을 도출하고, 데이터 딕셔너리에 미리 수집해 둔 통계정보를 이용해 각 실행계획의 예상비용을 산정하고, 그중 낮은 비용의 실행계획 하나를 선택하는 옵티마이저이다.
• CBO가 사용하는 통계정보로는 데이터량, 컬럼값의 수, 컬럼 값의 분포, 인덱스 높이, 클러스터링 팩터등이 있다.

2. 규칙위반(Rule-Based) 옵티마이저 RBO

• 데이터 특성을 나타내는 통계정보를 전혀 활용하지 않고, 단순 규칙에만 의존하기 때문에 대량 데이터를 처리하는데 부적합하다.

🚩RBO가 사용하는 규칙(인덱스 구조, 연산자, 조건절 형태가 순위를 결정하는 요소)

순위	액세스 경로
1	Single Row by Rowid
2	Single Row by Cluster Join
3	Single Row by Hash Cluster Kay with Urious 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 Range Search on Indied Columns
12	Sort Merge Join
13	MAX or MIN of Indexed Column
14	ORDER BY on Indexed Colum
15	Full Table Scan

RBO는 대용량 데이터베이스 환경에서는 힘들다. 예를 들어, 고객유형코드에 인덱스가 있으면 RBO는 Full Table Scan 순위가 15로 낮기 때문에 무조건 인덱스를 사용한다. 고객 유형코드 조건을 만족하는 고객이 전체 90%를 차지한다면 RBO는 좋은 선택을 했다고 보기 힘들다.

또한, 고객명 컬럼에 인덱스가 있다면 RBO는 인덱스 컬럼에 대한 ORBER BY순위가 Full Table Scan 순위보다 한단계 높기때문에 무조건 인덱스를 사용한다. 

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

그래서 CBO는 ALL_ROWS와 FIRST_ROWS 유형의 옵티마이저 모드를 두고있다.

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

🔎 옵티마이저 모드

비용기반 옵티마이저는 최적화 목표를 설정하는 기능으로서 옵티마이저 모드 중 하나를 선택하면 된다.

• ALL_ROWS : 전체 처리속도 최적화
• FIRST_ROWS : 최초 응답속도 최적화
• FIRST_ROWS_N :최초 N건 응답속도 최적화

⚙️ ALL_ROWS를 설정시

• 옵티마이저는 쿼리 결과집합 전체를 읽는 것을 전제로 시스템 리소스(I/O, CPU, 메모리등)를 가장 적게 사용하는 실행계획을 선택한다.
• 전체 처리속도 최적화가 목표다.

⚙️ FIRST_ROWS로 설정시

• 옵티마이저는 전체 결과집합 중 앞쪽 일부만 읽다가 멈추는 것을 전체로 응답속도가 가장 빠른 실행계획을 선택한다.
• 최초 응답속도 최적화가 목표다.
• ALL_ROWS와 비교하면, Table Full Scan 보다 인덱스를 더 많이 선택하고, 해시조인, 소트머지조인보다 NL조인을 더 많이 선택한다.
• 앞으로 사라지게 될 옵티마이저 모드이므로 'FIRST_ROWS_N' 사용

⚙️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은 읽을 데이터 건수를 지정하였으므로 더 정확한 비용 산정이 가능하다. FIRST_ROWS_N이 FIRST_ROWS보다 더 완벽한 CBO모드로 작동되는 이유다!

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

1. SQL과 연산자 형태

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

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

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

3. 제약설정

•  DBMS에 설정한 PK, FK, Check, Not Null같은 제약들은 데이터 무결성을 보장해줄뿐만 아니라 옵티마이저가 쿼리 성능을 최적화하는데 중요한 메타 정보로 활용된다.

4. 통계정보

• 옵티마이저에 매우 걍력한 영향을 주며, SQL 옵티마이저가 사용하는 통계정보에 문제가 생기면 애플리케이션 성능이 갑자기 느려지고, 심할땐 장애 상황으로 이어진다. 잘 작동하던 프로그램이 갑자기 느려졌다면 십중팔구는 통계정보가 원인이다.

🚩 원인

- 특정 테이블 통계정보를 갑자기 삭제한다.

- 대량 데이터를 지웠다가 다시 입력하기 직전, 데이터가 없는 상태에서 자동으로(스케줄링 된 통계수집 패키지에 의해) 통계정보가 수집된다.

- 3년간 갱신하지 않던 특정 테이블 통계정보를 어느 날 갑자기 재수집한다.

- 통계정보 없이 관리하던 테이블에 인덱스를 재생성한다.

- 테이블이나 인덱스를 재생성하면서 파티션 단위로만 통계정보를 수집한다.

5. 옵티마이저 힌트 

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

✨ 힌트가 잘 작동하지 않으면 확인해볼 사항
1. 문법적으로 맞지 않게 힌트 기술
2. 잘못된 참조 사용
3. 의미적으로 맞지 않게 힌트를 기술
4. 논리적으로 불가능한 액세스 경로
5. 버그

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

• SQL, 데이터, 통계정보등 모든 환경이 동일할 때 오라클 버전을 업그레이드하면 옵티마이저가 다르게 작동하는 경험을 겪을 수 있다.
• 옵티마이저의 그런 변화는 파라미터 추가나 기본값 변경에 기인한다.

--옵티마이저 행동에 영향을 미치는 파라미터 목록은 아래 쿼리를 통해 얻을 수 있다.
select name, value, isdefault, defulat_value
from v$sys_optimizer_env

✨ 옵티마이저의 한계

기본적으로 비용기반으로 작동하지만, 내부적으로 여러 가정과 정해진 규칙을 이용해 기계적인 선택을 한다는 사실은 옵티마이저가 한계를 보이는 원인 중 하나다.

🔎 개발자의 역할

결과물이 올바른지 실행계획을 통해 늘 점검하고, 개선할 여지는 없는지 찾으려고 노력해야 한다.
🚩 옵티마이저에게 많은 일을 맡기는 RDBMS환경에서 SQL성능을 높이기 위해 개발자의 역할

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

• DB성능은 I/O효율에 달려있으므로 동일한 레코드를 반복적으로 읽지 않고 필요한 최소 블록만 읽도록 한다.

2. 최적의 옵티마이징 팩터 제공한다.

🚩옵티마이징 팩터

• 전략적인 인덱스 구성 (옵티마이저를 돕는 가장 기본적인 옵티마이징 팩터)
• DMBS가 제공하는 다양한 기능 활용(파티션, 클러스터, IOT, MV....등등)
• 옵티마이저 모드 설정 
• 정확하고 안정적인 통계정보

정리하자면,

①옵티마이저 모드를 포함해 각종 파라미터를 적절한 값으로 설정하고, 통계정보를 잘 수집해주는 것이 무엇보다 중요하다.

②이것이 기본이 된 상태에서 전략적인 인덱스 구성이 필수적으로 뒷받침이 되어야 한다.

③ DBMS가 제공하는 기능을 적극적으로 활용해 옵티마이저가 최적의 선택을 할 수 있도록 다양한 수단을 제공해 주어야 한다.

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

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

⚙️데이터베이스 튜닝이란?

• 일반적으로 성능 튜닝이라고 말하며, DBMS성능 튜닝이다.
• 데이터 베이스는 데이터를 읽고 쓰는 소프트웨어 엔진이며, 데이터를 읽고 쓸때 SQL을 이용한다.
• 즉, SQL이 병목이나 지연없이 빠르고 안정적으로 수행되도록 조치하는 모든 활동을 데이터베이스 튜닝이라고 정의한다.

1. SQL 튜닝 : I/O 효율화, DB Call 최소화, SQL 파싱 최소화 등

2. DB 설계 : 논리적 데이터 구조 설계, 물리적 저장 구조 설계등

3. 인스턴스 튜닝 : Lock/Latch 모니터링 및 해소, 메모리 설정, 프로세스 설정 등

 

⚙️튜닝 전문가가 되려면 필요한 지식과 기술력 

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