6. 히스토그램
히스토그램이 있으면 더 정확한 카디널리티를 구할 수 있다.
특히, 분포가 균일하지 않은 컬럼으로 조회할 때 효과를 발휘한다.
히스토그램을 생성하려면 버킷 개수를 2 이상(size 옵션) 지정하면 된다.
히스토그램 정보는 dba_histograms 또는 dba_tab_histograms 뷰를 통해 확인할 수 있다.
10g 부터는 dba_tab columns 뷰에 histogram 컬럼이 추가되면서 히스토그램 유형을 쉽게 파악할 수 있다.
histogram 컬럼의 표시 값
-
FREQUENCY
값 별로 빈도수를 저장하는 도수분포 히스토그램 (값의 수 <= 버킷 개수)
-
HEIGHT-BALANCED
각 버킷의 높이가 동일한 높이균형 히스토그램 (값의 수 > 버킷 개수)
-
NONE
히스토그램을 생성하지 않은 경우
1. 도수분포(Frequency) 히스토그램
도수분포 히스토그램은 컬럼 값마다 하나의 버킷을 할당한다.
사용자가 요청한 버킷 개수가 컬럼이 가진 값의 수보다 많거나 같을 때 사용되며, 최대 254개의 버킷만 허용하므오 값의 수가 254개를 넘는 컬럼에는 이 히스토그램을 사용할 수 없다.
도수분포 히스토그램은 값별로 빈도수를 미리 계산해 두는 방식이기 때문에 조건절을 만족하는 카디널리티를 쉽고 정확하게 구할 수 있다.
하지만 시스템 자원에 한계가 있으므로 컬럼 값의 빈도수를 모두 이런 식으로 저장할 수는 없다.
그래서 값의 수가 많을 때는 높이균형 히스토그램을 사용한다.
2. 높이균형 히스토그램
높이균형 히스토그램은 'equi-depth 히스토그램'으로도 불린다.
컬럼이 가진 값의 수보다 적은 버킷을 요청할 때 만들어 진다.
버킷 개수보다 값의 수가 많기 때문에 하나의 버킷이 여러 개의 값을 담당한다.
요청할 수 있는 버킷의 개수가 최대 254개이기 때문에 254개가 넘는 버킷을 요청하면 무조건 높이균형 히스토그램이 만들어진다.
높이균형 히스토그램에서는 말 그대로 각 버킷의 높이가 같다.
오라클은 popular value를 압축해서 저장한다.
- endpoint_number : 버킷 번호
- endpont_value : 버킷이 담당하는 가장 큰 값
오라클은 popular value에 대한 카디널리티를 구할 때만 버킷에 의한 계산식을 사용하고 나머지는 미리 구해놓은 density 값을 이용한다.
✅ popular value에 대한 선택도/카디널리티 계산
조건절 값이 두 개 이상 버킷을 가진 polular value이면 아래 공식을 따른다.
선택도 = (조건절 값의 버킷 개수) / (총 버킷 개수)
카디널리티 = 총 로우 수 * 선택도
✅ non-popular value에 대한 선택도/카디널리티 계산
선택도 = 미리 구해놓은 density
카디널리티 = 총 로우 수 * 선택도(density)
3. 바인드 변수 사용 시 카디널리티 계산
바인드 변수를 사용하면, 최초 수행할 때 최적화를 거친 실행계획을 캐시에 적재하고 실행시점에는 그것을 그대로 가져와 값만 다르게 바인딩하면서 반복 재사용하게 된다.
여기서, 변수를 바인딩하는 시점이 실행시점이라는 사실이 중요하다.
즉, SQL 최적화를 하는 시점에 조건절 컬럼의 데이터 분포를 활용하지 못하는 문제점을 갖는다.
따라서 바인드 변수를 사용할 때 옵티마이저는 평균 분포를 가정한 실행계획을 생성한다.
✅ '=' 조건일 때
선택도 계산하기
-
히스토그램이 없을 때 : 1/num_distinct
-
도수분포 히스토그램일 때 : 1/num_distinct
-
높이균형 히스토그램일 때 : density
✅ 범위 검색 조건일 때
-
부등호(> , < , <= , >= ) 일때 선택도를 5%로 놓고 계산
-
Between이나 > and < 같이 바인드 변수가 두 개일 때 5*5 해서 0.25%로 놓고 계산
=> 이처럼 바인드 변수를 사용하면 정확한 컬럼 히스토그램에 근거하지 않고 카디널리티를 구하는 정해진 계산식에 기초해 비용을 계산하므로 최적이 아닌 실행계획을 수립할 가능성이 커진다.
좋은 실행계획을 위해서라면 DW, OLAP, 배치 프로그램에서 수행되는 쿼리는 바인드 변수보다 상수를 사용하는 것이 좋다.
날짜 컬럼처럼 부등호, between 같은 범위 조건으로 자주검색될 때 특히 그렇다.
OLTP성 쿼리라도 값의 종류가 적고 분포가 균일하지 않을 때는 상수 조건을 쓰는 것이 유용할 수 있다.
파티션 테이블을 쿼리할 때 파티션 레벨 통계정보를 이용하지 못하는 것도 바인드 변수의 부작용 중 하나이다.
파티션 레벨 통계보다 다소 부정확한 테이블 레벨 통계를 이용함으로써 옵티마이저가 가끔 잘못된 실행계획을 수립한다.
4. 결합 선택도
✅ 동적 샘플링
동적 샘플링 레벨 4 이상일 때 오라클은 소량의 데이터 샘플링을 통해 where 조건절에 사용된 두 개 이상 컬럼의 결합 분포를 구하는 기능을 제공한다.
✅ 다중 컬럼 통계 (Mulit-column Statistics)
11g 부터는 '확장형 통계' 라고 불리는 기능을 통해 다중 컬럼에 대한 히스토그램도 생성할 수 있게 되었다.
이 기능을 사용하려면 컬럼 그룹을 생성하면 된다.
ver ret varchar2(20);
exec :ret := dbms_stats.create_extended_stats(user, '사원', '(급여, 상여)'); -- 생성
print ret;
exec dbms_stats.drop_extended_stats(user, '사원', '(급여, 상여)'); -- 삭제7. 비용
1. I/O 비용 모델
I/O 비용 모델에서의 비용은 디스크 I/O 횟수를 의미한다.
비용 = belvel + -- 인덱스 수직적 탐색 비용
(리프 블록 수 * 유효 인덱스 선택도) + -- 인덱스 수평적 탐색 비용
(CF * 유효 테이블 선택도) -- 테이블 Random 액세스 비용 ✅ 인덱스를 경유한 테이블 액세스 비용
인덱스를 경유한 테이블 액세스 시에는 Single Block I/O 방식이 사용된다.
이는 디스크에서 한 블록을 읽을 때마다 한 번의 I/O Call을 일으키는 방식이므로 읽게 될 물리적 블록 개수가 액세스 비용과 일치한다.
선택도는 각 조건절 컬럼의 선택도를 곱해서 구하므로, 유효 인덱스 선택도와 유효 테이블 선택도는 항상 같다. (좌변 컬럼 가공, 왼쪽 '%' Like 조건 제외)
✅ Full Scan에 의한 테이블 액세스 비용
테이블을 Full Scan할 때는 HWM 아래쪽 블록을 순차적으로 읽어 들이는 과정에서 발생하는 I/O Call 횟수로 비용을 계산한다.
✅ I/O 비용 모델의 비현실적인 가정
-
Single Block I/O 와 Multiblock I/O는 비용이 같다.
-
캐싱 효과를 전혀 고려하지 않는다.
이를 보정하기 위해 오라클 8 버전부터 파라미터가 제공된다.
1. optimizer_index_cost_adj
이 파라미터는 인덱스 탐색 비용을 조정하고자 할 때 사용하며, 범위는 1~10,000이다.
기본 값 100은 Single Block I/O와 Multiblock I/O의 비용을 같게 평가하는 것이다.
예를 들어 25로 두면 SingleBlock I/O는 Multiblock I/O 비용의 25%로 간주한다.
이 값을 낮게 설정할수록 옵티마이저는 테이블 스캔보다는 인덱스를 이용한 테이블 액세스를 선호하게 된다.
2. optimizer_index_caching
optimizer_index_caching은 NL조인에서 inner 쪽 인덱스 블록이 캐싱돼 있을 가능성을 옵티마이저에게 일려주는 파라미터이며, IN-List Iterator 방식으로 인덱스를 탐색할 때 읽게 되는 인덱스 블록 액세스 비용에도 영향을 미친다.
설정할 수 있는 값의 범위는 0~100이며, 이 값을 높게 설정할수록 옵티마이저는 인덱스를 이용한 NL조인을 선호하게 된다.
2. CPU 비용 모델
모든 데이터베이스 오퍼레이션은 CPU를 사용하며, 경우에 따라서는 I/O보다 성능에 더 큰 영향을 끼치기도 한다.
블록 I/O가 소량인데도 쿼리 수행 시간이 오래 걸리는 경우
-
해시 조인할 때, 해시 체인에 달린 레코드가 많아 해시 체인을 스캔하는 부하가 심할 때
-
캐싱된 블록을 반복적으로 읽는데, 한 블록 내에서 매번 비효율적으로 많은 레코드를 스캔할 때
-
버퍼 Pin을 한 상태에서 같은 블록을 반복 액세스할 때
-
다량의 레코드를 읽으면서 건건이 여러 개의 사용자 정의 함수를 반복 호출할 때
-
메모리 소트를 반복할 때
아래와 같은 경우에도 CPU 사용량이 다소 증가한다.
-
조건절 개수가 아주 많을 때
-
조건절이나 select-list에 연산 집약적인 작업을 많이 포함할 때
이에 오라클은 쿼리 수행에 필요한 I/O 뿐만 아니라 예상되는 CPU 리소스 사용량까지 비용 계산식에 포함하는 새로운 비용 모델인 'CPU 비용 모델'을 9i에서 선보였다.
CPU 비용 모델의 비용계산식
비용 = (#SRds * sreadtim + -- Single Block I/O 요철 횟수 * Single Block I/O 시간 // I/O 비용 #MRds * mreadtim + -- Multiblock I/O 요청 횟수 * Multiblock I/O 시간 // I/O 비용 #CPUCycles / cpuspeed -- 예상 CPU 사이클 수 / 초당 처리할 수 있는 CPU 사이클 수 // CPU 비용 ) / sreadtim -- Single Block I/O 시간
CPUCycles (예상 CPU 사이클 수) 에는 순수한 CPU 연산과버퍼 캐시에서 데이터를 읽는 CPU 비용까지를 포함한다.
I/O 시간과 CPU 연산 시간을 더한 시간 개념을 빌어 쿼리 수행 비용을 평가한다는 것이 CPU 비용 모델의 핵심이다.
CPU 비용 모델에서 말하는 비용은, 쿼리의 예상 총 수행 시간을 Single Block I/O 시간 단위로 표현한 것이다.
8. 통계정보 Ⅱ
통계정보 수집 시 고려사항
-
시간 : 부하가 없는 시간대에 가능한 빠르게 수입을 완료해야 함
-
샘플 크기 : 가능한 적은 양의 데이터를 읽어야 함
-
정확성 : 전수 검사할 때의 통계치에 근사해야 함
-
안정성 : 데이터에 큰 변화가 없는데 매번 통계치가 바뀌지 않아야 함
가장 짧은 시간 내에 꼭 필요한 만큼만 데이터를 읽어 충분한 신뢰수준을 갖춘 안정적인 통계정보를 옵티마이저에게 제공해야 한다.
통계정보를 주기적으로 수집하면서도 안정적으로 운영되는 시스템이야말로 최적이다.
1. DBMS_STATS
통계정보 수집을 위해 오랫동안 사용해온 Analyze 명령어는 쓰지 않고 이제는 dbms_stats 패키지를 사용하는 것이 바람직하다.
dbms_stats가 더 정교하게 통계를 계산해 내기 때문이며, 특히 파티션 테이블/인덱스일 때는 반드시 dbms_stats를 사용해야 한다.
단, freelist 블록 정보 수집, 체인이 발생한 로우 개수 확인, list chained rows, validate 시에는 Anlayze 명령어를 사용한다.
2. 컬럼 히스토그램 수집
필요한 컬럼에만 히스토그램을 수집해야 하며, 조건절에 자주 사용되면서 편중된 데이터 분포를 갖는 컬럼이 주 대상이다.
히스토그램이 불필요한 컬럼 대상
-
컬럼 데이터 분포가 균일
-
Unique하고 항상 등치조건으로만 검색되는 컬럼
-
항상 바인드 변수로 검색되는 컬럼
10g부터는 모든컬럼에 대해 skew 여부를 조사해서 버킷 개수까지 조사한다.
따라서, 대용량 테이블일 때는 관리자가 직접 히스토그램 수집 컬럼을 지정해 주는 것이 좋다.
3. 데이터 샘플링
샘플링 비율을 높일수록 통계정보의 정확도는 높아지지만 통계정보를 수집하는 데 더 많은 시간이 소요된다.
반대로 샘플링 비율을 낮추면 통계정보의 정확도는 낮아지지만 더 효율적이고 빠르게 통계를 수집할 수 있다.
✅ 샘플링 비율
dbms_stats 패키지에서 샘플링 비율을 조정하기 위해 estimate_percent 인자를 사용한다.
이 값을 크게 한다고 선형적으로 증가하지 않고 충분한 신뢰수준에 도달한다.
따라서 각 테이블별로 적정 샘플링 비율을 조사할 필요가 있늗네, 5%에서 시작해 값을 늘려가며 두세번만 통계를 수집해 보면 적정 크기를 결정할 수 있다.
✅ 블록 단위 샘플링
block_sample 인자를 통해 블록 단위 샘플링을 할지 로우 단위 샘플링을 할지 결정한다.
블록 단위 샘플링이 더 빠르고 효율적이지만 데이터 분포가 고르지 않을 때 정확도가 많이 떨어진다.
기본 값은 로우 단위 샘플링이다.
✅ 안정적인 통계정보의 필요성
컬럼에 Null 값이 많거나 데이터 분포가 고르지 않을 때 샘플링 비율에 의해 영향을 크게 받는다.
✅ 해시 알고리즘으로 NDV 계산 -11g
오라클은 대용량 파티션이나 테이블 전체를 스캔하더라도 기존에 샘플링 방식을 사용할 때보다 빠른 속도를 내는 해시 기반의 새로운 알고리즘을 고안했다.
소트를 수행하지 않기 때문이며, 전체를 대상으로 NDV를 구하므로 정확도는 100%에 가깝다.
빠르고 정확하면서도 안정적인 통계정보를 구현할 수 있게 된 것이다.
4. 파티션 테이블 통계 수집
파티션 테이블일 때 오라클은 테이블 레벨 통계(global 통계)와 파티션 레벨 통계를 따로 관리한다.
-
파티션 레벨 통계 : Static Partition Pruning이 작동할 때 사용된다.
결합 파티션일 때는 서브파티션 레벨로 통계를 관리할 수도 있다. -
테이블 레벨 통계 : Dynamic Parition Pruning이 작동할 때 사용된다.
쿼리에 바인드 변수를 사용했거나, 파티션 테이블이 NL 조인에서 Inner 쪽 테이블이면 액세스해야 할 대상 파티션 목록을 쿼리 최적화 시점에 정할 수 없기 때문이다.
또한 파티션 키에 대한 조건절이 없을 때도 테이블 레벨 통계가 사용된다.
10g - NDV를 제외한 Incremental Global 통계
11g - NDV를 포함한 완벽한 Incremental Global 통계
5. 인덱스 통계 수집
테이블 통계를 수집하면서 cascade 옵션을 true로 설정하면 테이블에 속한 모든 인덱스 통계도 같이 수집된다.
문제는, 대용량 테이블이어서 샘플링 비율을 지정하면 인덱스 통계까지도 같은 비율이 적용된다는 것이다.
이럴 때는 테이블 통계만 샘플링 방식을 이용하고, 인덱스는 전수 검사하도록 각기 통계를 수집해야 한다.
10g 부터는 인덱스를 처음 생성하거나 rebuild할 때 인덱스 통계가 자동 수집되며,
_optimizer_compute_idex_stats 파라미터를 통해 설정을 변경할 수 있다.
6. 캐싱된 커서 invalidation
no_invalidate 옵션을 어떻게 지정하느냐에 따라 통계를 수집한 테이블과 관련된 SQL 커서의 무효화 시점이 달라진다.
no_invalidate 옵션들
-
false (9i dafault) : 통계정보 변경 시 관련된 SQL 커서들이 즉시 무효화된다.
그 다음 수행하는 세션에 의해, 새로 갱신된 통계정보를 이용한 실행계획이 하드파싱된다. -
true : 통계정보 변경 시 관련된 SQL 커서들을 무효화하지 않는다.
-
dbms_stats.auto_invalidate (10g default) : 통계정보 변경 시 관련된 SQL 커서들을 한번에 무효화하지 않고 정해진 시간 동안 조금씩 무효화 한다.
7. 자동 통계 수집
오라클은 10g부터 밤 10시 ~ 오전 6시까지 모든 사용자 오브젝트에 대한 통계를 자동 수집하도록 Job이 등록돼 있다.
이 기능은 gather_stats_job에 의해 자동 수행되며, 통계정보가 없거나 통계정보 수집 후 DML이 많이 발생한 모든 오브젝트를 대상으로 한다.
✅ GATHER_STATS_JOB
gather_stats_job은 데이터베이스 생성 시 자동으로 생성되며, Maintenance 윈도우 그룹에 등록된 윈도우가 열릴 때 마다 스케쥴러에 의해 수행된다.
✅ Statistics Preference
gather_stats_job을 그대로 활성화한 상태에서 테이블 또는 스키마별로 통계 수집 방식을 따로 설정할 수 있게 한 것이다.
그러면 자동 통계 수집 기능이 작동할 때 해당 테이블 또는 스키마에 대해서는 기본 설정 값을 무시하고 사용자 지시사항에 따라 통계정보를 수집한다.
