01 옵티마이저
(1) 옵티마이저란?
사용자가 요청한 SQL 을 가장 효율적이고 빠르게 수행할 수 있는 최적의 처리경로를 선택해 주는 DBMS 의 핵심엔진
(2) 규칙기반 옵티마이저 (Rule-Based Optimizer, RBO) 10g 부터는 지원 중단
Heuristic 옵티마이저라 불리며, 미리 정해 놓은 우선순위에 따라 액세스 경로를 평가하고 실행계획을 선택한다.
* 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. Clusterd 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 Indexed Columns
12. Sort Merge Join
13. MAX or MIN of Indexed Column
14. ORDER BY on Indexed Column
15. Full Table Scan
RBO는 대용량 데이터를 처리하는데 합리적이지 못할 때가 많다.
항상 인덱스를 신뢰하며, Full Table Scan 과의 손익을 따지지 않는다.
(3) 비용기반 옵티마이저 (Cost-Based Optimizer, CBO)
비용이란, 쿼리를 수행하는데 소요되는 일량 또는 시간을 뜻한다.
CBO 가 실행계획을 수립할 때 판단 기준이 되는 비용은 어디까지나 예상치다.
오브젝트 통계항목 - 레코드 개수, 블록 개수, 평균 행 길이, 컬럼 값의 수, 컬럼 값 분포, 인덱스 높이, 클러스터링 팩터
시스템 통계정보 - CPU 속도, 디스크 I/O 속도
* 옵티마이저의 최적화 수행단계 요약
1. 사용자가 던진 쿼리수행을 위해, 후보군이 될만한 실행계획을 찾는다.
2. Data Dictionary 에 미리 수집해 놓은 오브젝트 통계 및 시스템 통계정보를 이용해 각 실행계획의 예상비용을 산정한다.
3. 각 실행계획의 비용을 비교해서 최저 비용을 갖는 하나를 선택한다.
동적샘플링 (Dynamic Sampling)
쿼리를 최적화할 때 테이블과 인덱스에 대한 통계정보가 없거나 너무 오래돼 신뢰할 수 없을 때
옵티마이저가 동적으로 샘플링을 수행할 수 있도록 할 수 있다.
optimizer_dynamic_sampling 파라미터로 레벨 조정
아래의 조건 모두 만족할 때 동적 샘플링이 일어난다.
1. 통계정보가 수집되지 않은 테이블이 적어도 하나 이상 있고,
2. 그 테이블이 다른 테이블과 조인되거나 서브쿼리 또는 Non-mergeable View 에 포함되고,
3. 그 테이블에 인덱스가 하나도 없고
4. 그 테이블에 할당된 블록수가 32개 보다 많을 때
옵티마이저를 구성하는 서브 엔진
- Query Transformer: 사용자가 던진 SQL 을 최적화 하기 쉬운 형태로 변환을 시도한다.
- Estimator: 쿼리 오퍼레이션 각 단계의 선택도 (selectivity), 카디널리티 (cardinality), 비용 (cost) 를 계산하고,
궁극적으로 실행계획 전체에 대한 총 비용을 계산해낸다.
- Plan Generator: 하나의 쿼리를 수행하는데 있어 후보군이 될만한 실행계획들을 생성해내는 역할을 한다.
스스로 학습하는 옵티마이저 (Self-Learning Opimizer)
(4) 옵티마이저 모드
옵티마이저 모드로 선택할 수 있는 값은 아래 5가지가 있고, 시스템레벨, 세션레벨, 쿼리레벨에서 바꿀 수 있다.
- rule
- all_rows
- first_rows
- first_rows_n
- choose
RULE: RBO 모드 선택시 사용
ALL_ROWS: 쿼리 최종 결과 집합을 끝까지 Fetch, 시스템 리소스를 가장 적게 사용하는 실행계획 선택
FIRST_ROWS: 전체 결과 집합 중 일부 로우만 Fetch 하다가 멈추는 것, 가장 빠른 응답속도를 낼 수 있는 실행계획 선택
FIRST_ROWS_N: 사용자가 처음 n개 로우만 Fetch 하는 것을 전제로, 가장 빠른 응답 속도를 낼 수 있는 실행계획을 선택
n으로 지정할 수 있는 값은 1, 10, 100, 1000 네 가지
CHOOSE: 액세스되는 테이블 중 적어도 하나에 통계정보가 있다면 CBO, 그 중에서도 all_rows 모드를 선택한다.
어느 테이블에도 통계정보가 없으면 RBO 를 선택한다.
* 옵티마이저 모드 선택
all_rows 모드는 SQL 결과 집합을 모두 Fetch 하기에 가장 효율적인 실행계획을 옵티마이저에게 요구하는 것이고,
first_rows는 그 중 일부만 Fetch 하고 멈추는 것을 전제로 가장 효율적인 실행계획을 요구하는 옵티마이저 모드다.
애플리케이션 특성상 확실히 first_rows 가 적합하다는 판단이 서지 않는다면 all_rows 를 기본 모드로 선택하고,
필요한 쿼리 또는 세션 레벨에서 first_rows 모드로 전환할 것을 권고한다.
02 옵티마이저 행동에 영향을 미치는 요소
(1) SQL 과 연산자 형태
(2) 인덱스, IOT, 클러스터링, 파티셔닝, MV 등 옵티마이징 팩터
(3) 제약설정: PK, FK, Not Null, Check
- 개체 무결성
- 참조 무결성
- 도메인 무결성
- 사용자 정의 무결성
(4) 옵티마이저 힌트
* 아래 경우가 아니면 힌트를 사용
1. 문법적으로 맞지 않게 힌트를 기술
2. 잘못된 참조 사용
3. 의미적으로 맞지 않게 힌트 기술
4. 논리적으로 불가능한 액세스 경로
5. 버그
(5) 통계정보
(6) 옵티마이저 관련 파라미터
(7) DBMS 버전과 종류
03 옵티마이저의 한계
옵티마이저는 절대 완벽할 수 없다.
자동 튜닝 옵티마이저 (오프라인 옵티마이저): 통계를 분석하고, SQL 프로파일링을 실시하며, 액세스 경로 및 SQL 구조 분석을 통해 SQL 튜닝을 실시한다.
옵티마이저는 이미 높은 수준의 테크닉을 가지고 있지만 현실적인 제약 때문에 실 운영환경에 적용하지 못하고 있다.
(1) 부족한 옵티마이징 팩터
옵티마이저는 주어진 환경에서 최선을 다할 뿐 적절한 옵티마이징 팩터를 제공하는 것은 결국 사람의 몫이다.
(2) 부정확한 통계
정보는 곧 비용이다. 많은 정보를 수집, 보관한다면 그만큼 좋은 결가를 낼 수 있겠지만 현실적으로 100% 정확한 통계를 유지하기는 어렵다.
(3) 히스토그램의 한계
히스토그램 버킷 개수로 254개 까지만 허용된다
(4) 바인드 변수 사용 시 균등 분포 가정
조건절에 바인드 변수를 사용하면 옵티마이저가 균등분포를 가정하고 비용을 계산하기 때문에
아무리 정확한 컬럼 히스토그램을 보유하더라도 바인드 변수를 사용한 SQL 에는 무용지물이 된다.
(5) 결합 선택도 산정의 어려움
조건절 컬럼이 서로 상관관계에 있으면 정확한 데이터 분포와 카디널리티를 산정하기 어렵다.
(6) 비현실적인 가정
(7) 규칙에 의존하는 CBO
* 알파벳순 인덱스 선택 규칙
두 대안 인덱스의 예상 비용이 같을 때 알파벳 순에서 앞선 것을 선택한다.
(8) 하드웨어 성능 특성
04 통계정보 1
* 옵티마이저가 참조하는 통계정보
- 테이블 통계
- 인덱스 통계
- 컬럼 통계
- 시스템 통계
(1) 테이블 통계
compute 는 전수 검사, estimate 는 표본 조사
analyze table emp compute statistics for TABLE ;
analyze table emp estimate statistics sample 5000 rows for TABLE ;
analyze table emp estimate statistics sample 50 percent for TABLE ;
통계정보를 수집할 때 이제는 analyze 명령어를 사용하지 말라는 것이 오라클의 공식적인 입장이다.
dbms_stats 를 권장한다.
dmbs_stat 패키지이 용 예시
begin
dbms_stat.gather_table_stats ('scott', 'emp', cascade=>false, method_opt=>'for columns') ;
end ;
/
인덱스 통계가 수집되지 않도록 cascade false,
컬럼 통계가 수집되지 않도록 for columns 옵션에 컬럼명 지정하지 않음
(2) 인덱스 통계
인덱스 통계 수집
analyze INDEX emp_pk compute statistics;
테이블에 속한 모든 인덱스 통계 수집
analyze table emp compute statistics for ALL INDEXES;
테이블과 인덱스 통계 수집
analyze table emp compute statistics for TABLE for ALL INDEXES;
dbms_stats 패키지 이용 방법
-- 특정 인덱스 통계만 수집
begin
dbms_stats.gather_index_stats
(ownname => 'scott'
indname => 'emp_pk');
end;
-- 테이블에 속한 모든 인덱스 통계도 같이 수집
begin
dbms_stats.gather_table_stats ('scott', 'emp', cascade=>true);
end;
인덱스 통계 조회 결과
select blevel, leaf_blocks, clustering_factor, num_rows, distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key, sample_size, last_analyzed
from dba_indexes
where owner = 'SCOTT'
and table_name = 'EMP'
and index_name = 'EMP_PK';
인덱스 최초 생성, 재생성 할때 옵션을 주면 자동으로 인덱스 통계 수집된다.
인덱스는 이미 정렬돼 있으므로 통계정보 수집에 오랜시간이 소요되지 않는다.
create index emp_ename_idx on emp (ename) COMPUTE STATISTICS;
alter index emp_ename_idx rebuild COMPUTE STATISTICS;
(3) 컬럼 통계
테이블, 인덱스 통계는 제외하고 컬럼 통계만 수집하는 방법
analyze table emp compute statistics for ALL COLUMNS SIZE 254;
일부 컬럼에 대한 통계만 수집할 때
analyze table emp compute statistics for COLUMNS ENAME SIZE 10, SAL SIZE 20;
히스토그램 버킷 개수를 컬럼별로 지정하지 않고 똑같이 20으로 지정
analyze table emp compute statistics for COLUMNS SIZE 20 ENAME, SAL, HIREDATE;
테이블, 인덱스, 컬럼 통계를 동시 수집
analyze table emp compute statistics
for table
for all indexes
for all indexed columns size 254;
수집된 컬럼 통계 조회
--컬럼 통계 조회
select num_distinct, low_value, high_value, density, num_nulls, num_buckets, last_analyzed, sample_size, avg_col_len, histogram
from dba_tab_columns
where owner = 'SCOTT'
and table_name = 'EMP'
and column_name = 'DEPTNO'
--컬럼 히스토그램 조회
select endpoint_value, endpoint_number
from dba_histograms
where owner = 'SCOTT'
and table_name = 'EMP'
and column_name = 'DEPTNO'
order by endpoint_value;
(4) 시스템 통계
시스템 통계는 I/O, CPU 성능과 같은 하드웨어적 특성을 측정한 것으로서, 아래와 같은 항목을 포함
- CPU 속도
- 평균적인 Single Block I/O 속도
- 평균적인 Multiblock I/O 속도
- 평균적인 Multiblock I/O 개수
- I/O 서브시스템의 최대 처리량 (Throughput)
- 병렬 Slave 의 평균적인 처리량 (Throughput)
* Workload 시스템 통계
Workload 시스템 통계는, 애플리케이션으로부터 일정 시간 동안 발생한 시스템 부하를 측정, 보관함으로써
그 특성을 최적화 과정에 반영할 수 있게 한 기능
통계항목
cpuspeed 단일 CPU가 초당 수행할 수 있는 표준 오퍼레이션 개수 (단위 : 백만/초)
speadtim 평균적인 Single Block I/O 속도 (단위 : ms = 1/1000초)
mreadtim 평균적인 Multi Block I/O 속도 (단위 : ms = 1/1000초)
mbrc Multi Block I/O 방식을 사용할 때 평균적으로 읽은 블록 수
maxthr I/O 서브시스템의 최대 처리량 (단위 : 바이트/초)
slavethr 병렬 Slave의 평균적인 처리량 (단위 : 바이트/초)
* NoWorkload 시스템 통계
관리자가 명시적으로 선택하지 않더라도 CPU 비용 모델이 기본 비용 모델로 사용되게 하려고
오라클 10g 에서 NoWorkload 시스템 통계를 도입하였다.
통계항목
cpuspeednw NoWorkload 상태에서 측정된 CPU 속도
ioseektim I/O seek Time
iotfrspeed I/O Transfer 속도
Workload 는 실제 애플리케이션에서 발생하는 부하를 기준으로 각 항목의 통계치를 측정하는 반면
NoWorkload 는 모든 데이터파일 중에서 오라클이 무작위로 I/O 를 발생시켜 통계를 수집한다.
NoWorkload 도 시스템 부하가 어느정도 있는 상태에서 수집되는 것이 바람직하다.
05 카디널리티
인덱스, 클러스터 등 옵티마이징 팩터가 동일한 상황에서 CBO 행동에 결정적 영향을 미치는 요소는 무엇보다 통계정보다.
(1) 선택도 (Selectivity)
전체 대상 레코드 중에서 특정 조건에 의해 선택될 것으로 예상되는 레코드 비율
선택도를 가지고 카디널리티를 구하고, 다시 비용을 구함으로써 인덱스 사용 여부, 조인 순서와 방법 등을 결정하므로
선택도는 최적의 실행계획을 수립하는데 있어서 가장 중요한 요인이라고 할 수 있다.
선택도 → 카디널리티 → 비용 → 액세스 방식, 조인 순서, 조인 방법 등 결정
(2) 카디널리티 (Cardinality)
특정 액세스 단계를 거치고 나서 출력될 것으로 예상되는 결과 건수
카디널리티 = 총 로우수 x 선택도
컬럼 히스토그램이 없을 때 '=' 조건에 대한 선택도가 1/num_distinct 이므로 카디널리티는 아래와 같이 구해진다.
카디널리티 = 총 로우 수 x 선택도 = num_rows / num_distinct
(3) Null 값을 포함할 때
(4) 조건절이 두 개 이상일 때
(5) 범위검색 조건일 때
(6) cardinality 힌트를 이용한 실행계획 제어
06 히스토그램
(1) 히스토그램 유형
히스토그램이 없으면 더 정확한 카디널리티를 구할 수 있다. 특히, 분포가 균일하지 않은 컬럼으로 조회할 때 효과를 발휘한다.
- 높이 균형 (Height-Balanced) 히스토그램
- 도수분포 (Frequency) 히스토그램
FREQUENCY: 값별로 빈도수를 저장하는 도수분포 히스토그램
HEIGHT-BALANCED: 각 버킷의 높이가 동일한 높이균형 히스토그램
NONE: 히스토그램을 생성하지 않은 경우
(2) 도수분포 히스토그램
valued-based 히스토그램 으로도 불리며, 값별로 빈도수를 저장하는 히스토그램
endpoint_value: 버킷에 할당된 컬럼 값
endpoint_number: endpoint_value 로 정렬했을 때, 최소 값부터 현재 값까지의 누적수량
(3) 높이균형 히스토그램
equi-depth 히스토그램 으로도 불린다. 컬럼이 가진 값의 수보다 적은 버킷을 요청할 때 만들어진다.
버킷 개수보다 값의 수가 많기 때문에 하나의 버킷이 여러 개 값을 담당한다.
endpoint_number: 버킷 번호
endpoint_value: 버킷이 담당하는 가장 큰 값
* popular value 에 대한 선택도/카디널리티 계산
선택도 = (조건절 값의 버킷 개수) / (총 버킷 개수)
* non-popular value 에 대한 선택도/카디널리티 계산
카디널리티 = 총 로우 수 x 선택도 = 총 로우수 x density
(4) 바인드 변수 사용 시 카디널리티 계산
* '=' 조건일 때
- 히스토그램이 없을 때: 1/num_distinct 사용
- 도수분포 히스토그램일 때: 1/num_distinct 사용
- 높이균형 히스토그램일 때: density 사용
* 범위검색 조건일 때
(5) 결합 선택도
* 동적 샘플링 (Dynamic Sampling)
* 다중 컬럼 통계 (Multi-column Statistics)
07 비용
I/O 비용모델, CPU 비용 모델
파라미터 _optimizer_cost_model
- IO: I/O 비용 모델
- CPU: CPU 비용 모델
- CHOOSE: 시스템 통계가 있으면 CPU 비용 모델, 없으면 I/O 비용 모델
(1) I/O 비용 모델
디스크 I/O Call 횟수
* 인덱스를 경유한 테이블 액세스 비용
* Full Scan 에 의한 테이블 액세스 비용
* I/O 비용 모델의 비현실적인 가정
- Single Block I/O 와 Multiblock I/O 는 비용이 같다.
- 캐싱 효과를 전혀 고려하지 않는다.
optimizer_index_cost_adj: 인덱스 탐색 비용을 조정하고자 할 때 사용
optimizer_index_caching: NL 조인에서 inner 쪽 인덱스 블록이 캐싱돼 있을 가능성을 옵티마이저에게 알려주는 파라미터
(2) CPU 비용 모델
I/O 비용
CPU 비용
08 통계정보 2
(1) 전략적인 통계수집 정책의 중요성
* CBO 능력을 최대한 끌어올리는 핵심 요소
* DB 관리자의 핵심 역할은 통계정보 관리
* 통계정보 수집 시 고려사항
- 시간: 부하가 없는 시간대에 가능한 빠르게 수집을 완료해야 함
- 샘플 크기: 가능한 적은 양의 데이터를 읽어야 함
- 정확성: 전수 검사할 때의 통계치에 근접해야 함
- 안정성: 데이터에 큰 변화가 없는데 매번 통계치가 바뀌지 않아야 함
가장 짧은 시간 내에 꼭 필요한 만큼만 데이터를 읽어 충분한 신뢰수준을 갖춘 안정적인 통계정보를 옵티마이저에게 제공하려면 치밀한 전략이 있어야 한다.
* 주기적으로 통계 수집하면서 안정적이어야 최적
절대적인 최적의 성능을 구현하기보다 안정적인 운영을 바라는 OLTP 시스템 관리자 입장에서는
통계정보의 안정성이 더 중요할 수 있다. 그런 연유로, 통계 정보 수집을 꺼리는 DB 관리자들도 상당수 있다.
안정성이 중요하더라도 CBO를 사용하는 한 통계정보를 수집하지 않을 수 없다.
통계정보를 주기적으로 수집하면서도 안정적으로 운영되는 시스템이야말로 최적이라고 할 수 있으며,
이를 위해서 시스템 환경에 맞는 전략적인 통계정보 수집 정책이 반드시 필요하다.
* 통계 수집 정책 수립은 필수
(2) DBMS_STATS
Analyze 명령어를 버리고 이제는 dbms_stats 패키지를 사용하는 것이 바람직하다.
(3) 컬럼 히스토그램 수집
히스토그램을 가지면 더 나은 실행계획을 수립하는 데 도움이 되지만 이를 수집하고 관리하는 비용이 만만치 않기 때문에
필요한 컬럼에만 히스토그램을 수집해야 하며, 조건절에 자주 사용되면서 편중된 데이터 분포를 갖는 걸럼이 주 대상이다.
아래와 같은 컬럼에는 히스토그램이 불필요하다.
- 컬럼 데이터 분포가 균일
- Unique 하고 항상 등치조건으로만 검색되는 컬럼
- 항상 바인드 변수로 검색되는 컬럼
(4) 데이터 샘플링
샘플링 비율을 높일수록 통계정보의 정확도는 높아지지만 통계정보를 수집하는 데 더 많은 시간이 소요된다.
* 샘플링 비율 estimate_percent
* 블록 단위 샘플링 block_sample
* 안정적인 통계정보의 필요성
* 해시 기반 알고리즘으로 NDV 계산 - 11g
(5) 파티션 테이블 통계수집
파티션 테이블일 때 오라클은 테이블 레벨 통계와 파티션 레벨 통계를 따로 관리한다.
* NDV 를 제외한 Incremental Global 통계 - 10.2.0.4
* NDV 를 포함한 완벽한 Incremental Global 통계 - 11g
(6) 인덱스 통계 수집
(7) 캐싱된 커서 Invalidation
(8) 자동 통계 수집
오라클 10g부터 기본적으로 매일 밤 10시부터 다음날 아침 6시까지 모든 사용자 오브젝트에 대한 통계를 자동수집하도록 Job이 등록돼있다.
이 기능은 gather_stats_job 에 의해 자동 수행되며, 통계정보가 없거나 통계정보 수집 후 DML이 많이 발생한 모든 오브젝트를 대상으로 한다.
* GATHER_STATS_JOB
* 통계정보 갱신 대상 식별
* 자동 통계 수집 기능 활용 가이드
(9) Statistics Preference