7장. 옵티마이저 통계
학습 목표
이번 장을 완료하면 다음과 같은 작업을 수행 할 수 있다.
① 옵티마이저 통계 수집
② 시스템 통계 수집
③ 정적 선호도 설정
④ 동적 샘플링 사용
⑤ 옵티마이저 통계 조작
옵티마이저 통계
옵티마이저 통계는 데이터베이스와 데이터베이스 내부의 객체들에 대한 구체적인 정보를 표현한다.
이 통계는 각 SQL 문장에 대한 최상의 실행 계획을 선택하기 위해 옵티마이저에 의해서 사용 된다.
데이터베이스 내의 객체들은 지속적으로 변경되기 때문에 통계는 정기적으로 갱신되어야만 이러한
데이터베이스 객체들을 정확하게 표현 할 수 있다. 통계는 오라클 데이터베이스에 의해 자동적으로
유지되거나 DBMS_STATS 패키지를 사용하여 직접 관리 할 수 있다.
옵티마이저 통계의 유형
대부분의 옵티마이저 통계는 다음과 같다.
① 테이블 통계
- 행의 개수
- 블록의 개수
- 행의 평균 길이
② 인덱스 통계
- B*-트리 높이
- 고유값의 개수
- 리프 블록의 개수
- 클러스터링 팩터
③ 시스템 통계
- I/O 성능 및 이용률
- CPU 성능 및 이용률
④ 컬럼 통계
- 기본 : 고유값의 개수, Null의 개수, 평균 길이, 최소값, 최대값
- 히스토그램(컬럼의 데이터가 균등하지 않은 경우에 데이터 분포도)
- 확장된 통계
Oracle Database 10g부터 인덱스가 생성되거나 재구성되면 인덱스 통계가 자동으로 수집된다.
참고 : 위에서 언급한 통계는 옵티마이저 통계로서 쿼리 최적화를 위해 생성되고 데이터 딕셔너리에 저장된다. 이러한
통계를 V$ 뷰를 통해서 볼 수 있는 성능 통계와 혼동하면 않된다.
테이블 통계(DBA_TAB_STATSTICS)
NUM_ROWS
카디널리티 계산의 기본이 된다. 행의 개수는 테이블이 중첩 루프 조인을 위해 먼저 드라이빙 되는 경우, 매우 중요하며
내부 테이블을 검증하는 회수를 결정하게 된다.
BLOCKS
사용된 데이터 블록의 개수다. 블록 개수는 DB_FILE_MULTIBLOCK_READ_COUNT와 함께 기본적인 테이블 액세스
비용을 제공한다.
EMPTY_BLOCKS
테이블 내에서 한 번도 사용되지 않은 빈 블록의 개수. 사용된 데이터 블록과 HWM 사이의 블록 들이다.
AVG_SPACE
테이블에 할당된 데이터 블록 중에서 비어 있는 공간의 평균 크기를 바이트로 표시한다.
CHAIN_CNT
하나의 데이터 블록에서 다른 데이터 블록으로 연결되었거나, 새로운 블록으로 이주 한 행의 개 수. 행이 이주하면 과거
ROWID를 보존하기 위한 연결이 필요하다.
AVG_ROW_LEN
테이블 내에서 행의 평균 길이이며, 바이트로 표시한다.
STALE_STATS
해당 테이블의 통계 자료가 너무 오래되지 않았는지를 나타낸다.
인덱스 통계(DBA_IND_STATISTICS)
인덱스 컬럼의 선두 컬럼이 조건절에 등장하면 옵티마이저는 인덱스 경로를 요구한다. 그러나, 조건절이 없거나 쿼리 내에서
참조되는 모든 컬럼들이 인덱스에 존재하면 옵티마이저는 전체 인덱스 스캔과 전체 테이블 스캔을 고려하게 된다.
BLEVEL
리프 블록을 검색하는 비용을 계산하기 위해 사용된다. 루트 블록부터 리프 블록까지의 인덱스 깊이를 나타낸다. 깊이가 0이라는
것은 루트 블록과 리프 블록이 동일하다는 것을 의미한다.
LEAF_BLOCKS
전체 인덱스 스캔의 비용을 계산할 때 사용한다.
CLUSTERING_FACTOR
인덱스의 값을 기준으로 테이블 내의 행들의 순서를 측정한다. 만약, 이 값이 블록의 개수에 근접 하다면, 이 테이블은 정렬이 잘
되어 있는 상태이다. 이 경우에 동일한 리프 블록 내 인덱스 엔트 리들은 동일한 데이터 블록 내의 행들을 가리키는 경향이 있다.
만약, 이 값이 행의 개수에 근접 하다면 이 테이블의 순서는 랜덤하다는 것을 의미한다. 이 경우에 동일한 리프 블록 내의 인덱스
엔트리들이 동일한 데이터 블록을 거의 가리키지 않는다.
STALE_STATS
해당 인덱스의 통계 자료가 오래되지 않았는지를 나타낸다.
DISTINCT_KEYS
고유한 인덱스 값의 개수다. UNIQUE 및 PRIMARY KEY 제약조건이 정의된 인덱스의 경우, 이 값은 테이블 내의 행의 개수와 동일하다.
AVG_LEAF_BLOCKS_PER_KEY
인덱스 내의 각 고유 값들이 차지하는 리프 블록의 평균 개수이며 가장 근접한 정수로 반올림된다. UNIQUE와 PRIMARY
KEY 제약조건이 정의된 인덱스의 경우에 이 값은 언제나 1이다.
AVG_DATA_BLOCKS_PER_KEY
인덱스 내의 각 고유 값들이 가리키는 테이블 내 데이터 블록의 평균 개수이며 가장 근접한 정수로 반올림된다. 이 통계
자료는 인덱스 컬럼 값을 포함하는 행들이 저장된 데이터 블록의 평균 개수다.
NUM_ROWS
인덱스 내의 행의 개수이다.
인덱스 클러스터링 팩터
그림 7.1
시스템은 블록 단위의 입력/출력(I/O)을 수행한다. 그러므로, 전체 테이블 스캔을 선택하려는 옵티마이저의 결정은 액세스
할 행의 개수가 아닌 블록의 개수에 영향을 받는다. 이것을 인덱스 클러스터링 팩터라고 부른다. 만약, 해당 블록들이
하나의 행만을 가지고 있다면, 액세스 할 행과 액세스 할 블록은 동일 할 것이다.
그러나, 대부분의 테이블들은 각 블록에 여러 개의 행을 가지고 있다. 즉, 요청하는 행들이 일부 블록에 모여 있거나,
많은 블록들에 넓게 퍼져 있을 수 있다. B*-트리의 높이, 리프 블록의 개수, 인덱스 선택도와 같은 정보와 함께 인덱스
범위 스캔의 비용 산정 공식에는 클러스터링 팩터가 포함된다. 낮은 클러스터링 팩터는 개별 행들이 테이블 내의 소수 블록에 집중 되어 있음을 의미
하기 때문이다. 높은 클러스터링 팩터는 개별 행들이 랜덤하게 테이블 내의 블록들에 넓게 퍼져 있음을 의미한다. 그러므로, 높은 클러스터링
팩터는 인덱스 범위 스캔을 사용하여 ROWID에 의 해 행들을 인출 할 때 비용이 높아짐을 의미하는데, 그 이유는 해당 데이터를 인어 오기
위해 테 이블 내의 많은 블록들을 방문해야 하기 때문이다. 현실에서는 인덱스 범위 스캔의 비용을 결정 하는데 클러스터링 팩터가 중요한
역할을 하게 되며, 그 이유는 리프 블록의 갯수와 B*-트리의 높이가 클러스터링 팩터와 테이블의 선택도와 비교 할 때 매우 작은 값이기 때문이다.
참고 : 만약, 하나의 테이블에 하나 이상의 인덱스가 존재한다면, 하나의 인덱스에 대한 클러스터링 팩터는 매우 낮은 반면에 다른 인덱스의
클러스터링 팩터는 매우 높을 수 있다. 어느 하나의 인덱스에 대한 클러스터링 팩터를 개선시키기 위해 테이블을 재구성하는 것은 다른 인덱스의
클러스터링 팩터를 낮출 수도 있다.
클러스터링 팩터는 인덱스에 대한 통계 자료를 수집하면 DBA_INDEXES의 CLUSTERING_FACTOR 컬럼에 저장된다. 이 값을 계산하는 것은
매우 간단하다. 인덱스를 왼쪽부터 오른쪽으로 진행하면 서 각 인덱스 엔트리를 인고, 해당 행들이 이전 행이 저장되어 있었던 블록이 아닌
다른 블록으로 이동하면 클러스터링 팩터에 1을 더하면 된다. 이 알고리즘을 기반으로 클러스터링 팩터의 가장 작은 값은 블록의 개수가 되며,
가장 큰 값은 행의 개수가 된다.
위 그림의 예제에서는 클러스터링 팩터가 비용에 어떻게 영향을 주는지 보여준다. 다음과 같은 상황을 가정하자. 9개의 행을 가진 테이블이
존재하며, c1에 비 고유 인덱스가 있고, c1 컬럼에 현 재 A, B, C의 값이 저장되어 있다. 해당 테이블은 오직 3개의 블록으로 구성되어 있다.
① 첫 번째 경우 : 같은 행들이 테이블에 각각 나열되어, 인덱스 값이 테이블 블록들에 모여 있지 않고 넓게 퍼져 있다면, 인덱스 클러스터링 팩터는 높아진다.
② 두 번째 경우 : 동일한 블록에 같은 값을 갖는 행들이 모여 있으면 인덱스 클러스터링 팩터는 낮아진다.
참고 : 비트맵 인덱스의 경우, 클러스터링 팩터를 적용 할 수 없으며, 사용 할 수도 없다.
컬럼 통계(DBA_TAB_COL_STATISTICS)
NUM_DISTINCT는 선택도 계산에 사용된다. 예를 들어, 1/NDV
LOW_VALUE와 HIGH_VALUE : CBO는 모든 데이터 타입에 대하여 최소값과 최대값 사이의 값들 이 균등하게 분포되어 있다고 가정한다.
이 값들은 범위 선택도를 결정하는데 사용된다.
NUM_NULLS는 Null 허용 컬럼과 조건절에 IS NULL과 ISNOT NULL이 있는 경우, 선택도를 계산하는데 도움을 준다.
DENSITY는 히스토그램의 한 종류이다. 비인기(nonpopular) 값에 대하여 선택도를 예측 할 때 사용한다. 이 컬럼에서 특정한 하나의
값을 찾기 위한 확률로 생각 할 수 있다. 해당 컬럼에 히스토그램이 존재하는지 여부에 따라 계산된다.
NUM_BUCKETS는 해당 컬럼에 대한 히스토그램에서 버킷의 개수다.
HISTOGRAM은 히스토그램이 존재 하는 지와 히스토그램의 종류, NONE, FREQUENCY, HEIGHT를 나타낸다.
히스토그램
히스토그램은 컬럼에 저장된 서로 다른 값들의 분포를 수집하여, 더 나은 선택도 예측을 수행 할 수 있도록 해준다. 분포가 균등하지
않은 데이터 또는 많은 중복값을 포함하는 컬럼에 히스토그 램을 생성하는 것은 쿼리 옵티마이저가 우수한 선택도 예측을 수행하고
인덱스 이용률, 조인 순 서, 조인 방식 등을 고려한 더 나은 결정을 만들어 내는데 도움을 준다.
히스토그램이 없다면, 균등 분포를 가정한다. 만약, 컬럼에 대하여 히스토그램이 사용 가능하다면,
예측기는 고유값의 개수 대신에 히스토그램을 사용 할 것이다.
히스토그램을 생성하면, 오라클 데이터베이스는 해당 컬럼에서 발견된 고유값의 개수에 따라 2가 지 방식의 히스토그램을 표현법을
사용한다. 현재 보유한 데이터의 고유값이 254개 이내이고, 히스토그램 버킷의 수를 지정하지 않으면, 시스템은 도수분포 히스토그램을
생성한다. 만약, 고유값 의 개수가 필요한 히스토그램 버킷의 개수를 초과하면, 높이 균형 히스토그램을 생성한다.
히스토그램에 관련된 정보는 DBA_TAB_HISTOGRAMS, DBA_PART_HISTOGRAMS, DBA_SUBPART_HISTOGRAMS에서 찾을 수 있다.
참고 : 히스토그램 통계를 수집하는 것은 통계 수집 작업 중에 자원을 가장 많이 소비하는 작업이다.
도수 분포 히스토그램
그림 7.2
그림의 예제는 컬럼에 40,001개의 값을 가진 컬럼을 가정한 것이다. 여기에는 10개의 고유 값, 1, 3,
5, 7, 10, 16, 27, 32, 39, 49가 있으며, 이 중에 10이 가장 많은 16,293번 저장되어 있다.
요청된 버킷의 수가 고유 값의 개수와 같거나 크다면, 각각의 서로 다른 값을 저장하고, 정확한 카디널리티 통계를 기록 할 수 있다.
이 경우에 DBA_TAB_HISTOGRAMS의 ENDPOINT_VALUE 컬럼 에는 컬럼 값이 저장되고 ENDPOINT_NUMBER 컬럼에는
해당 컬럼 값에 대한 행의 개수가 저장 된다.
행의 개수는 누적 형태로 저장되어 범위 스캔을 위해 필요한 계산을 생략 할 수 있다. 행의 개수에 대한 실제 값은 그림의
곡선으로 명확히 보여진다. 오직 ENDPOINT_VALUE와 ENDPOINT_NUMBER 컬럼은 데이터 딕셔너리에 저장된다.
도수 분포 히스토그램 확인
그림 7.3
위 그림의 예제는 도수 분포 히스토그램을 보는 방법을 보여준다. INVENTORIES 테이블의 WAREHOUSE_ID 컬럼의 고유
값 개수는 9이고, 요청 버킷의 수는 20이므로 시스템은 9개의 버킷을 가진 도수 분포 히스토그램을 자동으로 생성한다.
이 정보는 USER_TAB_COL_STATISTICS 뷰에 표시된다.
히스토그램 자체 데이터를 보려면 USER_HISTOGRAMS 뷰를 검색하면 된다. 해당 값의 누적 발생 횟수와 일치하는
ENDPOINT_NUMBER와 컬럼의 실제 값인 ENDPOINT_VALUE를 확인 할 수 있다.
참고 : DBMS_STATS 패키지는 이 장의 뒷 부분에서 다룬다.
높이 균형 히스토그램
그림 7.4
높이 균형 히스토그램에서 컬럼 값은 밴드로 분할되고, 각 밴드는 동일한 개수의 행들을 포함한다. 히스토그램은 endpoint가 어떤 값의
범위에서 종료되는지를 알려 준다. 위 예제에서는 40,001 개의 값을 가진 컬럼이 있으며, 1, 3, 5, 7, 10, 16, 27, 32, 39, 49의
10개의 고유 값을 갖고, 10의 값이 16,293번 발생한다고 가정한다. 버킷의 개수가 고유값의 개수보다
작다면, ENDPOINT_NUMBER는 버킷 번호를 기록하고 ENDPOINT_VALUE는 해당 endpoint에 해당하는 컬럼 값을
기록한다. 이 예제에서는 버킷 당 행의 개수가 전체 행의 1/5이므로 8000개이다.
이러한 가정을 기반으로 10은 8000과 24000사이에 나타나므로, 10은 인기(popular) 값이라고 가 정한다.
이러한 유형의 히스토그램은 인기 값이 등가(=) 조건으로 비교되거나 범위 조건에서 유리하다.
버킷 당 행의 개수는 기록되지 않는데, 그 이유는 전체 값의 개수로부터 계산될 수 있고, 모든 버킷들은 동일한 개수의
값을 가지고 있기 때문이다. 예제에서는 10이 여러 개의 endpoint 값을 포함하므로 인기 값이다. 히스토그램은 저장 공간
을 절약하기 위해 인기 값을 실제로 저장하지 않는다.
예제에서 2번 버킷(인기 값 10)은 DBA_TAB_HISTOGRAMS에 기록되지 않는다.
참고 : 예제에서 밀도 통계 값은 1/9 * 4/5 = 0.088 또는 8.8% (9=#NPV, 4=#NPB). 이 값은 비인기값에 대한 선택도 계산시에 사용된다.
역자 주 : NPV(Non-popular value), NPB(Non-popular band)
높이 균형 히스토그램 확인
그림 7.5
위 그림의 예제는 높이 균형 히스토그램을 확인하는 방법을 보여준다. INVENTORIES 테이블의 QUANTITY_ON_HAND
컬럼의 고유 값의 개수는 237이고, 요청된 버킷의 수는 10이므로 시스템은 10개의 버킷을 가진 높이 균형 히스토그램을
생성한다. USER_TAB_COL_STATISTICS 뷰에서 이런 정보를 확인 할 수 있다.
히스토그램 자체 데이터를 확인하려면 USER_HISTOGRAMS 뷰를 쿼리하면 된다. 버킷 번호와 일치하는
ENDPOINT_NUMBER와 endpoint 값에 해당하는 ENDPOINT_VALUE를 확인 할 수 있다.
참고 : DBMS_STATS 패키지는 이 장의 뒷부분에서 다룬다.
히스토그램 작성시 고려사항
히스토그램은 주어진 컬럼의 현 데이터 분포를 나타내는 경우에만 유용하다. 컬럼 내의 데이터는 값의 분포가 고정적으로
유지되는한, 변경 될 수 있다. 만약, 컬럼의 데이터 분포가 자주 변경된다면, 자주 히스토그램을 재계산하여야 한다.
히스토그램은 히스토그램을 생성하고자 하는 컬럼의 값이 매우 불균등한 분포를 갖는 경우에만 유용하다.
그러나, SQL 문장의 WHERE 절에 나타나지 않는 컬럼에 대하여 히스토그램을 생성 할 필요는 없다. 이와 유사하게 균등
분포를 갖는 컬럼에 대하여 히스토그램을 생성 할 필요도 없다.
또한, UNIQUE로 선언된 컬럼에 대해서도 선택도가 명확하기 때문에 히스토그램은 유용하지 못하다. 버킷의 최대 개수는
254이고, 이 값이 실제 고유 값의 개수보다 작을 수 있다. 히스토그램은 성능에 영향을 줄 수 있고, 히스토그램이 쿼리
실행 계획을 꾸준하게 향상시킬 수 있는 경우에만 사용하여야 한다. 균등 분포된 데이터의 경우, 옵티마이저는
히스토그램을 사용하지 않고도 특정 문장의 실행 비용을 상당히 정확하게 예측해 낼 수 있다.
참고 : 문자 컬럼은 몇 가지 예외적인 동작 특성을 갖는데, 히스토그램 데이터에 문자열의 선두 32바이트만 저장하기 때문이다.
다중 컬럼 통계 : 개요
그림 7.6
Oracle Database 10g부터 쿼리 옵티마이저는 다음과 같은 제한된 경우에서만 다중 조건의 선택도를 계산 할 때, 컬럼 간의 상관 관계를 고려한다.
① 결합된 조건의 모든 컬럼들이 결합 인덱스 키의 모든 컬럼과 일치하고, 조건 절이 등가 조인(equijoin) 이라면, 옵티마이저는
선택도 예측을 위해 인덱스 내 고유 값의 개수를 사용한다. 즉, 1/NDK.
② DYNAMIC_SAMPLING이 4로 설정되면, 쿼리 최적화기는 동적 샘플링을 사용하여 같은 테이블의 여러 컬럼들을 포함하는
복잡한 조건절의 선택도를 예측한다. 그러나, 샘플의 크기가 매우 작고 파싱 시간이 증가하게 된다. 그 결과, 샘플은 통계적으로
부정확 할 수 있고 좋지 않은 결과를 발생 시킬 수도 있다.
그 외의 모든 경우에서 옵티마이저는 복잡한 조건 절에 사용된 컬럼의 값들이 서로 독립적이라고 가정하고, 개별 조건의
선택도를 곱하여 결합된 조건의 선택도를 예측한다. 이러한 방식은 컬럼 간의 상관 관계가 존재하는 경우에 선택도를
제대로 예측하지 못하는 결과를 발생시킨다.
이러한 문제를 보완하기 위해, Oracle Database 11g는 두 개 이상의 컬럼들(컬럼 그룹이라고 부르기도 함)에 대하여 함수적
의존성을 수집하기 위해 다음과 같은 정보, 즉, 고유 값의 개수, Null의 개수, 도수분포 히스토그램, 밀도를 수집, 저장 및 사용한다.
예를 들어, 자동차에 관련된 정보를 저장하기 위한 VEHICLE 테이블을 가정하자. MAKE와 MODEL 컬럼은 밀접한 상관 관계가 있어서,
MODEL이 MAKE를 결정한다. 이것은 강력한 의존성이며, 옵티마이저에 의해 두 개의 컬럼이 높은 상관 관계를 갖는다고 가정한다.
이러한 상관 관계를 옵티마이저에게 전달하려면 그림의 예제와 같이 CREATE_EXTENDED_STATS 함수를 사용하면 된다. 그러면, 모든
컬럼들에 대한 통계가 계산된다.
옵티마이저는 등가 조건을 사용하는 경우에만 다중 컬럼 통계를 사용한다.
참고
① CREATE_EXTENDED_STATS 함수는 SYS_STUW_5RHLX443AN1ZCLPE_GLE4와 같은 가상의 숨겨진 컬럼명을 리턴한다.
② 그림의 예제를 기반으로 해당 이름은 다음 SQL을 사용하여 결정 될 수 있다.
select dbms_stats.show_extended_stats_name(‘jfv’,’vehicle’,’(make,model)’) from dual;
③ 확장 통계를 생성한 후, ALL | DBA | USER_STAT_EXTENSIONS 뷰를 이용하여 이러한 이름을 읽어 올 수 있다.
표현식 통계 : 개요
그림 7.7
컬럼의 표현식이 포함된 조건 절은 쿼리 Optimizer에게 심각한 문제가 된다. function(column)= constant 형태의
조건 절에서 선택도를 계산하는 경우, Optimizer는 1 퍼센트의 고정된 선택도를 가정한다. 그 결과, Optimizer는
준 최적의 실행 계획을 생성 할 수 있기 때문에 이러한 접근법은 올바르지 못하다.
쿼리 Optimizer는 함수가 컬럼의 분포 특성을 보존하는 경우에서, 그러한 조건 절을 좀더 정밀하게 처리 할 수 있도록
확장되었으며, 그 결과, 옵타마이저는 컬럼 통계를 사용 할 수가 있게 되었다. 이러한 함수의 예로는 TO_NUMBER가 있다.
Optimizer는 더욱 향상되어 쿼리 최적화가 동적 샘플링을 사용하여 더 나은 선택도를 계산하는 동안, 내장 함수를 평가
할 수 있게 되었다. 결국, Optimizer는 함수 기반 인덱스를 지원하도록 생성된 가상 컬럼들에 대하여 통계를 수집한다.
그러나, 이러한 솔루션은 특정 함수 또는 함수 기반 인덱스를 생성하는데 사용된 표현식에서만 제한적으로 사용된다.
Oracle Database 11g에서 표현식 통계를 사용하면, 사용자 정의 함수를 포함하고 함수 기반 인덱스의 존재 여부와 상관
없는 좀 더 일반적인 솔루션을 사용 할 수 있다. 그림의 예제에서 보는 것처럼 이 기능은 컬럼의 표현식에 통계를 생성하기
위해 가상 컬럼 인프라를 이용한다.
시스템 통계 수집
시스템 통계는 Optimizer가 시스템의 I/O와 CPU 성능 및 이용률을 고려 할 수 있도록 해준다. 각각의 후보 실행 계획에
대하여 Optimizer는 I/O와 CPU 비용의 예측 값을 계산한다. I/O와 CPU 비용 간의 최적의 비율을 갖는 가장 효율적인 실행
계획을 선택하기 위해서는 시스템의 특성을 인지하는 것이 중요하다. 시스템 CPU와 I/O 특성은 많은 인자들에 따라
달라지고 언제나 일정한 것은 아니다. 시스템 통계 관리 루틴을 이용하면 시스템에 가장 일상적인 작업부하가 걸리는
기간에 통계를 수집할 수 있다. 예를 들어, 데이터베이스 애플리케이션들이 낮 시간에 OLTP 트랜잭션을 처리하고 저녁에
OLAP 리포트를 실행한다고 가정하자.
사용자는 두 상태에 대한 통계를 수집하여 필요 할 때만 OLTP 또는 OLAP 통계를 활성화 할 수 있다. 그 결과,
Optimizer는 가용 시스템 자원 계획에 따라 상대적인 비용을 계산 할 수 있다. 시스템이 시스템 통계를 생성하면, 시스템은
주어진 기간에서의 시스템 활동성을 분석한다. 테이블, 인덱스, 컬럼 통계와 달리, 시스템 통계가 업데이트 되더라도 이미
파싱된 SQL 문장을 무효화시키지 않는다. 모든 신규 SQL 문장은 새로운 통계를 이용하여 파싱된다.
시스템 통계의 수집을 권장하며, DBMS_STATS.GATHER_SYSTEM_STATS 루틴을 이용하여 사용자가 직접 정의한 시간에
시스템 통계를 수집하도록 한다. 또한, DBMS_STATS.SET_SYSTEM_STATS를 사용하여 시스템 통계 값을 설정 할 수도 있다.
시스템 통계를 검증하려면 DBMS_STATS.GET_SYSTEM_STATS를 사용하도록 한다.
GATHER_SYSTEM_STATS 프로시저를 사용 할 때, GATHERING_MODE 파라메터를 지정하여야 한다.
① NOWORKLOAD : 디폴트. 이 모드는 I/O 시스템의 특성을 수집한다. 통계 수집은 수 분이 걸릴 수 있으며, 데이터베이스의
크기에 따라 달라진다. 이 기간 동안, 시스템은 I/O 시스템에 대한 평균 인기 탐색 시간과 전송 속도를 예측한다. 이 모드는
모든 작업 부하에 대하여 적절하다. 데이터베이스와 테이블스페이스를 생성한 후, GATHER_SYSTEM_STATS(‘noworkload’)를
실행하도록 권장한다.
② INTERVAL : 지정된 기간 동안의 시스템 활동성을 수집한다. 이 작업은 수집 할 시간을 지정하는 interval 파라메터와 조합하여
동작한다. 기간은 분으로 지정하며, 딕셔너리 내 에서 특정 시스템의 통계가 생성 또는 변경되거나 테이블이 변경 된 후에
실행하도록 한다. 계획 보다 앞서 통계 수집을 중지하려면 GATHER_SYSTEM_STATS (gathering_mode=>’STOP’)을 사용하면 된다.
③ START | STOP : 지정된 기간 동안의 시스템 활동성을 수집하며, 주어진 기간동안 딕셔너 리 또는 테이블의 통계를 갱신한다.
참고 : Oracle Database 10g Release 2부터 시스템은 시스템 통계의 필수적인 부분들은 데이터베이스 기동 시에 자동적으로 수집한다.
시스템 통계 수집 : 예제
그림 7.8
그림의 예제는 낮에는 OLTP 트랜잭션을 처리하고 저녁에는 리포트를 실행하는 데이터베이스 애플리케이션을 보여준다.
먼저, 시스템 통계를 낮 시간 동안에 수집하여야 한다. 예제에서는 120분 후에 통계 수집을 마쳤으며, 그 결과를
mystats 테이블에 저장하였다.
그 다음, 밤 시간 동안에 시스템 통계를 수집한다. 120분 후에 통계 수집을 종료하였으며, 그 결과를 mystats 테이블에 저장하였다.
일반적으로 그림의 구문이 시스템 통계를 수집하기 위해 사용된다. GATHER_SYSTEM_STATS 프로시저를 지정된 INTERVAL
파라메터와 함께 실행하기 전에 다음과 같은 명령을 이용하여 job 프로세스를 반드시 활성화하여야 한다.
SQL> alter system set job_queue_prcesses = 1;
또한, job을 이용하는 대신에 통계 수집을 직접 수행하려면 다른 인자로 동일한 프로시저를 실행 하여야 한다.
만약, 필요에 따라 수집된 통계를 교체 할 수 있다. job을 제출하여 적절한 통계로 딕셔너리를 업데이트하는 것도 이
프로세스를 이용하여 자동화 할 수 있다. 낮 시간 동안, job은 OLTP 통계를 임포트 할 수 있고, 저녁에는 OLAP 통계를 임포트 할 수 있다.
그림 7.9
이전 예제는 DBMS_STATS.GATHER_SYSTEM_STATS 프로시저의 내부 파라메터를 이용하여 시스템 통계를 수집하는 방법을
보여주었다. 시스템 통계를 직접 수집하려면 위 예제와 같이 해당 프로시저에 다른 파라메터를 사용하면 된다.
먼저, 시스템 통계 수집을 시작하고, 인스턴스에 대표적인 작업 부하를 발생시킨 후, 통계 수집 작업을 종료한다.
위 예제는 시스템 통계를 수집하여 데이터 딕셔너리에 직접 저장한다.
통계 수집의 메커니즘
그림 7.10
오라클 데이터베이스는 통계 수집을 위한 여러 메커니즘을 제공한다. 이러한 메커니즘은 다음 부분에서 좀 더 자세히
살펴본다. 객체에 대해서 자동 통계 수집 기능의 사용을 권장한다.
참고 : 시스템이 통계가 없는 테이블을 만나게 되면, Optimizer에 의해 필요한 통계를 동적으로 수집한다. 그러나, 특정
유형의 테이블의 경우, 동적 샘플링이 수행되지 않는다. 이 경우에 해당하는 것이 원격 테이블과 외부(external) 테이블이다.
그러한 경우에는 동적 샘플링이 비활성화 되고, Optimizer는 위 그림의 디폴트 값을 사용한다.
통계 선호도 : 개요
그림 7.11
자동 통계 수집 기능은 Oracle Database 10g Release 1에서 등장하였으며, Optimizer 통계를 유지 관리해야 할 부담을 감소시켜 주었다.
그러나, 이 기능을 비활성화하고 자신의 스크립트를 대 신 실행해야 하는 경우도 존재하였다. 첫 번째 이유는 객체 수준의 제어 기능이 부족했기 때문이다.
디폴트 통계 수집 옵션이 제대로 동작하지 않은 객체의 일부들을 발견 할 때마다, 해당 통계에 잠금을 설정하고 별도의 옵션을 이용하여
해당 객체들을 별도로 분석하여야 했다. 예를 들어, 적당한 샘플의 크기를 자동으로 결정하는 기능(ESTIMATE_PERCENT=AUTO_SAMPLE_SIZE)은
데이터 분포가 균등하지 않으면 제대로 동작하지 않았다. 이러한 문제를 해결하는 유일한 방법은 자신의 스크립트에서 직접 샘플의 크기를 지정하는 것이었다.
Oracle Database 11g의 통계 선호도 기능은 일부 객체들이 요구하는 설정이 데이터베이스 디폴트 값과 다른 경우에도 Optimizer 통계를 유지하기
위해 자동화 된 통계 수집 기능을 사용 할 수 있도록 유연성을 제공한다. 이 기능은 객체 또는 스키마 수준에서 GATHER_*_STATS 프로시저와
자동화된 Optimizer 통계 수집 작업의 디폴트 동작 특성을 덮어 쓰는 통계 수집 옵션을 지정 할 수 있도록 해준다. 그림과 같이 통계 수집 옵션을
관리하기 위해 DBMS_STATS 패키지를 사용 할 수 있다.
테이블, 스키마, 데이터베이스, 전역 수준에서 이러한 선호도를 설정, 인기, 삭제, 익스포트, 임포트 할 수 있다. 전역 선호도는 선호도를 갖지 않는
테이블에 대하여 사용하며, 데이터베이스 선호도 는 모든 테이블에 선호도를 설정하기 위해 사용한다. 다양한 방식으로 지정된 선호값들은 그림과
같이 바깥 원에서 안쪽 원을 향하여 우선순위를 갖는다.
그림에서 마지막 3가지 옵션은 Oracle Database 11g에서 새롭게 추가된 옵션이다. Release 1 :
① CASCADE는 인덱스 통계도 같이 수집한다. 인덱스 통계 수집은 병렬로 처리 되지 않는다.
② ESTIMATE_PERCENT는 통계를 계산하기 위해 사용된 행들의 예측된 퍼센트이다(Null은 모든 행을 의미함). 유효한 값의 범위는
[0.000001, 100]이며, DBMS_STATS.AUTO_SAMPLE_SIZE 상수를 이용하여 우수한 통계를 위한 적절한 샘플의 크 기를 결정 할 수 있다.
이 값이 디폴트이다.
③ NO_INVALIDATE는 의존 커서들을 무효화하거나 무효화시키지 않는다. TRUE로 설정하면 의존 커서를 무효화시키지 않는다.
FALSE로 설정하면 프로시저는 의존 커서를 즉시 무효 화시킨다. DBMS_STATS.AUTO_INVALIDATE를 사용하여 시스템이 의존 커서를
무효화 시킬 시점을 자동으로 결정하도록 할 수 있다. 이 값이 디폴트이다.
④ PUBLISH는 통계 자료를 딕셔너리에 저장하거나 저장하기 전에 지연 영역(pending area)에 저장할 것인지를 결정한다.
⑤ STALE_PERCENT는 객체가 오래된 통계를 가지고 있음을 판단하는 임계치를 결정하는데 사용된다. 이 값은 마지막 통계 수집 이후에
변경된 행의 개수를 퍼센트로 지정한다. 예제에서는 SH.SALES만 디폴트인 10퍼센트에서 13퍼센트로 변경하였다.
⑥ DEGREE는 통계를 계산 할 때 사용 할 병렬 처리 정도를 결정한다. 디폴트는 Null이며, 이 값의 의미는 CREATE TABLE 또는
ALTER TABLE 문장의 DEGREE 절에 정한 테이블 디 폴트 값을 사용한다는 의미이다. 초기화 파라메터에 기반한 디폴트 값을 지정하려면
DBMS_STATS.DEFAULT_DEGREE 상수를 사용하도록 한다. AUTO_DEGREE 값은 병렬 처리 정도를 자동으로 결정한다. 이 값은 객체의
크기에 따라 1 또는 DEFAULT_DEGREE(시스템 디폴트 값은 CPU의 개수와 초기화 파라메터에 따라 달라진다)가 된다.
⑦ METHOD_OPT는 히스토그램 통계를 수집하는데 사용되는 SQL 문자열이다. 디폴트 값은 FOR ALL COLUMNS SIZE AUTO이다.
⑧ GRANULARITY는 파티션 테이블에 대하여 통계를 수집 할 때, 통계 처리의 단위이다.
⑨ INCREMENTAL은 증분 방식으로 파티션 테이블에 전역 통계를 수집 할 때 사용된다. DBMS_STATS.SET_PARAM 프로시저를
사용하여 위 파라메터의 디폴트 값을 변경 할 수 있다.
직접 통계를 수집해야 하는 시점
자동 통계 수집 메커니즘은 모든 통계를 현재 값으로 유지한다. 새로운 통계를 언제 얼마나 자주 수집해야 하는지 결정하는 것은 매우 중요하다. 디폴트 수집 주기는
매일 저녁이지만 사용자의 필요에 따라 이 주기를 변경 할 수 있다. 사용자의 유지 관리 윈도우의 특성을 변경하여 이러한 작업을 수행 할 수 있다. 그러나, 직접 통계를
수집하는 것이 특수한 상황에서는 필요 할 수도 있 다. 예를 들어, 낮 시간에 대량으로 변경된 테이블의 통계는 너무 오래되었을 수 있다. 그러한 객체는 일반적으로 다음 두 종류이다.
① 낮 시간 동안만 대량으로 변경되는 휘발성 테이블들
② 다음 통계 수집이 시작되기 전에 객체의 전체 크기에서 약 10% 이상의 데이터가 대량으 로 로드 되는 객체들.
외부 테이블의 경우, GATHER_SCHEMA_STATS, GATHER_DATABASE_STATS, 자동 Optimizer 통계 수집 기능에 의해서 통계가 수집되지 않는다.
그러나, GATHER_TABLE_STATS를 사용하여 개별 외 부 테이블에 대해 통계를 수집 할 수 있다. 외부 테이블에 대한 샘플링은 허용되지 않기 때문에
ESTIMATE_PERCENT는 명시적으로 Null로 지정되어야 한다. 외부 테이블의 경우에는 데이터 변경 이 허용되지 않기 때문에 해당 파일의 변경이 발생된
경우에만 외부 테이블을 분석하는 것으로 충분하다. 직접 통계를 수집할 필요가 있는 그 외의 영역은 시스템 통계와 동적 성능 테이블과 같은 고정
객체이다. 이 통계들은 자동으로 수집되지 않는다.
직접 통계 수집
EM과 DBMS_STATS, 패키지를 사용하면 통계를 직접 생성하고 관리 할 수 있다. 또한, DBMS_STATS 패키지를 사용하여
통계를 수집, 변경, 보기, 익스포트, 임포트, 잠금, 삭제 할 수 있다. 또한, 이 패키지를 이용하여 수집된 통계를 식별하거나
이름을 명명 할 수 있다. 인덱스, 테이블, 컬럼, 파티션에 대하여 원하는 수준 즉, 객체, 스키마, 데이터베이스 수준에서
통계를 수집 할 수 있다.
DBMS_STATS는 최적화를 위해 필요한 통계만 수집하며, 그 외의 통계는 수집하지 않는다. 예를 들어, DBMS_STATS에 의해
수집된 테이블 통계는 행의 개수, 데이터를 현재 포함하고 있는 블록의 개수, 평균 행의 길이를 포함하지만 연결된 행,
빈 공간의 평균 크기, 사용하지 않은 데이터 블록의 수는 포함하지 않는다.
참고 : Optimizer 통계를 수집하기 위해서 ANALYZE 문장의 COMPUTE와 ESTIMZTE 구문을 사용 하지 않도록 한다. 이
구문은 역호환성을 위해서만 지원되며, 추후에는 제거될 수도 있다. DBMS_STATS, 패키지는 더욱 정확하고 효율적으로
통계를 수집한다. ANALYZE 문장을 Optimizer 통계 수집과 무관한 다른 용도로 사용 할 수는 있다.
① VALIDATE 또는 LIST CHAINED ROWS 구문을 사용하기 위해
② 프리리스트 블록에 대한 정보를 수집하기 위해
직접 통계 수집 : 인자
Optimizer 통계를 직접 수집하면, 다음과 같은 인자에 특별히 주의하여야 한다.
① 대량의 DML 작업이 발생한 객체를 모니터링하고 필요하면 통계를 수집한다.
② 올바른 샘플 크기를 결정한다.
③ 대용량 객체에 대한 쿼리를 빠르게 실행하기 위해 병렬 처리 정도를 결정한다.
④ 분포가 균등하지 않는 데이터를 가진 컬럼에 히스토그램을 생성해야 할지를 결정한다.
⑤ 객체에 대한 변경이 다른 의존 인덱스를 연쇄적으로 변경시키는지 결정한다.
통계 자료 수집 관리 : 예제
그림 7.12
첫 번째 예제는 DBMS_STATS 패키지를 사용하여 SH 스키마의 CUSTOMERS 테이블에 통계를 수집하는 방법이다. 앞
절에서 설명한 옵션들이 사용되었다.
디폴트 파라메터 설정
모든 DBMS_STATS 프로시저의 파라메터에 디폴트 값을 설정하려면 DBMS_STATS 패키지의 SET_PARAM 프로시저를
사용 할 수 있다. 또한, GET_PARAM 함수를 사용하여 파라메터의 현재 디폴트 값을 얻을 수 있다.
참고 : 수집 할 통계의 단위(granularity)는 테이블이 파티션 된 경우에만 유효하다. 이 파라메터는 어떤 수준에서 통계를
수집해야 할 지를 결정한다. 파티션, 서브 파티션, 테이블이 될 수 있다.
Optimizer 동적 샘플링 : 개요
동적 샘플링은 Optimizer가 더 나은 성능의 실행 계획을 작성 할 수 있도록 좀 더 정확한 선택 도와 카디널리티를
예측하여 서버의 성능을 개선시킨다. 예를 들어, CBO에 의해 사용 될 모든 테이블들에 대하여 통계를 수집하도록
권장하지만, 임시 테이블과 임시 데이터 조작을 위한 작업 테이블에는 통계를 수집하지 않았을지도 모른다. 이런 경우에
단순한 알고리즘을 이용하여 준 최적 실행 계획을 작성 할 수 있도록 값을 제공한다. 다음과 같은 경우에 동적 샘플링을
사용 할 수 있다.
① 수집된 통계가 사용 될 수 없거나 예측에 심각한 오류를 발생시킬 수 있다면 단일 테이블 조건의 선택도를 예측한다.
② 통계가 없거나 너무 오래되어 통계 자료를 신뢰 할 수 없는 테이블 및 인덱스의 카디널리티를 예측한다.
OPTIMIZER_DYNAMIC_SAMPLING 초기화 파라메터를 이용하여 동적 샘플링을 제어한다. 동적 샘플링을 좀더 정밀하게
제어하려면 DYNAMIC_SAMPLING과 DYNAMIC_SAMPLING_EST_CDN 힌트를 사용하면 된다.
참고 : OPTIMIZER_FEATURES_ENABLE 초기화 파라메터는 9.2 이전 값으로 지정하면 동적 샘플링 기능이 비활성화 된다.
Optimizer 동적 샘플링의 동작
성능의 주요 속성은 컴파일 시간이다. 시스템은 컴파일 시에 쿼리가 동적 샘플링으로부터 이득을 얻을 수 있는지 판단한다.
만약, 이득을 얻을 수 있다면, 테이블의 블록에서 랜덤으로 작은 샘플을 얻기 위하여 재귀 SQL 문장이 실행되고, 조건절의
선택도를 예측하기 위해 연관된 단일 테이블에 조건절을 적용한다.
OPTIMIZER_DYNAMIC_SAMPLING 초기화 파라메터의 값에 따라 동적 샘플링 쿼리에 의해 블록의 일부가 읽혀진다.
평상 시에 빠르게 완료되는 쿼리의 경우(수 초 미만), 동적 샘플링을 수행하는데 비용이 소모되는 것을 원하지 않는다.
그러나, 동적 샘플링은 다음과 같은 조건하에서 이득을 얻을 수 있다.
① 동적 샘플링을 사용하여 더 나은 실행 계획을 발견 할 수 있는 경우
② 쿼리의 전체 실행 시간 중에서 샘플링에 소비되는 시간이 매우 작은 경우
③ 쿼리가 자주 실행 되는 경우
참고 : 동적 샘플링은 단일 테이블의 조건 절에 의한 부분 집합에 적용 될 수 있으며, 동적 샘플링 이 수행되지 않은
조건절의 표준 선택도 예측 값과 결합 될 수도 있다.
OPTIMIZER_DYNAMIC_SAMPLING
OPTIMIZER_DYNAMIC_SAMPLING 파라메터를 사용하여 동적 샘플링을 제어 할 수 있다. 이 파라메터에는 0에서 10까지의
값을 설정 할 수 있다. 0은 동적 샘플링이 수행되지 않음을 의미한다.
1(디폴트)은 다음과 같은 조건이 만족 될 때, 모든 분석되지 않는 테이블에 대하여 동적 샘플링을 수행한다.
① 쿼리 내에 최소한 하나의 분석되지 않는 테이블이 존재하는 경우
② 분석되지 않는 테이블이 다른 테이블과 조인되고, 서브 쿼리 또는 머지가 불가능한 뷰에 나타나는 경우
③ 분석되지 않는 테이블이 인덱스를 갖지 않은 경우
④ 분석되지 않는 테이블의 블록 개수가 동적 샘플링에 사용 할 블록의 디폴트 개수 이상인 경우. 디폴트 값은 32이다.
OPTIMIZER_FEATURES_ENABLE이 10.0.0 또는 그 이상인 경우, 디폴트 값은 2이다. 이 경우에 시스템은 모든 분석되지
않는 테이블에 대하여 동적 샘플링을 적용한다. 샘플링 된 블록의 개수는 동 적 샘플링 블록의 디폴트 개수(32)의 2배이다.
파라메터값을 증가시키면 샘플링 될 테이블의 유형과 샘플링에 소비 될 I/O의 양의 관점에서 동적 샘플링을 좀 더 공격적으로 적용한다.
참고 : 이전에 샘플링 작업을 수행한 테이블에 대하여 어떤 행도 입력, 삭제, 변경되지 않은 테이블에 대해서도 동적 샘플링은 반복된다.
통계 잠금
그림 7.13
Oracle Database 10g부터 DBMS_STATS 패키지의 LOCK_TABLE_STATS 프로시저를 사용하여 지정된 테이블의 통계에
잠금을 설정 할 수 있다. 통계가 없는 테이블에 통계를 잠그거나 DELETE_*_STATS 프로시저를 사용하여 해당 통계에
NULL을 지정 할 수 있다. 이렇게 설정하는 이유는 자동 통계 수집이 발생하지 않도록 하여, 통계가 존재하지 않는
휘발성 테이블에 동적 샘플링을 사용하기 위함이다. 또한, 테이블의 통계가 테이블을 아주 잘 표현하는 시점에서 휘발성
테이블에 잠금을 설정 할 수도 있다.
LOCK_SCHEMA_STATS 프로시저를 사용하여 스키마 수준에서 통계에 잠금을 설정 할 수 있다.
{USER | ALL | DBA}_TAB_STATISTICS 뷰의 STATTYPE_LOCKED 컬럼을 쿼리하면 테이블의 통계가 잠겨있는지 확인 할 수 있다.
UNLOCK_TABLE_STATS 프로시저를 사용하여 지정된 테이블의 통계에 설정된 잠금을 해제 할 수도 있다.
FORCE 파라메터를 TRUE로 설정하면 테이블의 통계가 잠겨 있다 하더라도 통계를 덮어 쓸 수 있다. 다음 DBMS_STATS
프로시저에는 FORCE 인자를 제공한다.
DELETE_*_STATS, IMPORT_*_STATS, RESTORE_*_STATS, SET_*_STATS
참고 : 테이블의 통계에 잠금을 설정하면, 모든 의존 통계는 잠긴 것으로 설정된다. 여기에는 테이블 통계, 컬럼 통계, 히스토그램, 연관 인덱스 통계를 포함한다.
'oracle11R2 > SQL Tuning 11g' 카테고리의 다른 글
| 09장. 옵티마이저 힌트 사용 (0) | 2011.06.23 |
|---|---|
| 08장. 바인드 변수의 사용 (0) | 2011.06.23 |
| 06장. 케이스 스터디 : 스타 변환 (0) | 2011.06.23 |
| 05장. 실행 계획의 해석 (0) | 2011.06.23 |
| 04장. 옵티마이저 연산자 (0) | 2011.06.15 |
