메뉴 건너뛰기

bysql.net

6. 히스토그램

2011.04.25 17:00

오예스 조회 수:17363

06. 히스토그램

(0) 히스토그램
? 히스토그램의 두 가지 극단적인 견해
- 긍정적인 견해 : Histogram 을 수집하고 나니까 성능이 극적으로 개선되었다. 나는 성능이 느리다는 불평을 들으면 Histogram 을 수집하면 않을까 생각하곤 한다.
-  부정적인 견해 : Histogram 은 골치거리다. Histogram 때문에 여러 가지 성능 문제를 겪었다. 나는 결코 Histogram 을  수집하지 않는다.
- 둘다 맞는 말이다. Histogram 은 약도 되고 독도 될수 있는 속성을 지니고 있다. Oracle이 제공하는 기능들 대부분이 사실 그런 속성을 가지고 있지만 Histogram 은 특히 그렇다. 그렇다면 왜 이런 극단적인 반응들이 나오게 되는지 알아보자.

? Histogram  이란 무엇인가? Wikepedia에서 찾은 정의는 다음과 같다.
   - a histoaram is a graphical diplay of tablulated frequencies
      우리 말로 번역하자면 ‘Histogram 이란 Table 형태의 빈도(개수)를 Graphical 하게 표현한 것’ 이다.
 - Oracle 9i 까지는 기본적으로 Histogram 이 수집되지 않음.
 - Oracle 10g 부터는 Histogram 수집이 Oracle의 판단에 따라 이루어짐.
    DBMS_STATS Package에서 Histogram 수집 방식을 담당하는 METHOD_OPT Parameter의 기본값이 “1”에서 “AUTO” 로 바뀌었기 때문이다. 필연적으로 Histogram  에 대한 지식이 요구.

? 그렇다면 Histogram 은 언제 필요한가?
- Data가 Skew 되어 있을때 Histogram 이 빛을 발한다. 조동욱 오라클 ACE가 세미나에서 “Oracle에서 Data의 Skewness를 반영할 수 있는 유일한 방법이 Histogram 이다” 라고 하자 이런 질문이 따라 았다. “ 그렇다면 Data가 Skew 되었다는 것은 무슨 말이냐?”
Data가 Skew 되었다는 것은 빈도(Frequency. 개수)가 특정 값에 편향되어 있다는 것을 의미한다. Histogram이중요한 이유는  Data가 Skew 되어 있는데 Histogram이 없다면 Cardinality 계산에 있어서 큰 오류를 범하게 될것이기 때문이다..

- Cardinality :  왜 Histogram 의 존재유무가 그렇게도 중요한가? 왜 많은 사람들이 Histogram 을 싫어 하고 또 어떤 사람들은 Histogram 을 반드시 사용해야 한다고 주장하는가? Cardinality 때문이다. Histogram 이 Cardinality 의 계산에 매우 큰 영향을 주기 때문이다. 때로는 이러한 영향이 긍정적이기도 하고 부정적이기도 하다.  Histogram 이 Cardinality 계산에 주는 영향은 Histogram 의 종류에 따라 다르게 나타난다. 즉 Frequency Histogram 인가 Height-Balance Histogram  이냐에 따라 Cardinality 에 미치는 영향이 다르다.

? 기본적인 용어들
- { A는 500 개, B는 300 개} 라면 ,A, B 가 값(value)가 되고 500, 300은 빈도(Frequency) 가된다.
- Bucket(바구니) : 값들이 속하는 범위를 의미한다. 왜 이런 개념이 필요한가? Oracle이 저장 가능한 값의 개수에 한계가 있기 때문이다. Oracle은 최대 254 개의 값만 구분할 수 있다. 따라서 254 개 이상의 값들에 대해 빈도를 저장하려면 이들 값들을 적당한 범이로 나누어야 한다. 즉, 여러 개의 다른 값들을 하나의 Bucket 에 담을 수 있어야 한다. 또는 하나의 값이 빈도가 매우 높은 경우에는 여러개의 Bucket을 차지 할 수도 있다.

(1) 히스토그램 유형
? 높이균형(Height-Balanced) 히스토그램
? 도수분포(Frequency)  히스토그램
? 히스토그램 정보 뷰 : dba_histograms, dba_tab_histograms

- user_tab_col_statistics.histogram
- FREQUENCY : 값별로 빈도수를 저장하는 도수분포 히스토그램(값의 수=버킷 개수)
- Height-Balanced : 각 버킷의 높이가 동일한 높이균형 히스토그램(값의 수 > 버킷 개수)
- NONE : 히스토그램을 생성하지 않은 경우


(2)도수분포 히스토그램
? 'value-based 히스토그램'으로도 불리며, 값별로 빈도수(frequency number)를 저장하는 히스토그램.
? 최대 254개의 버킷만 허용하므로 값의 수가 254개를 넘는 컬럼에는 사용할수 없음.
? 컬럼값마다 단일 버킷을 할당하므로 100개의 버킷을 요청해도 실제값이 89개면 89개 버킷생성.(정확한 히스토그램을 위해서라면 항상 254개를 요청하는 것이 좋다.)
? dba/all/user_histograms
- endpoint_value : 버킷에 할당된 컬럼 값
- endpoint_number : endpoint_value로 정렬했을때 , 최소값부터 현재 값까지의 누적수량.
? 도수분포 히스토그램은 값별로 미리 계산해 두는 방식이기 때문에 조건절을 만족하는 카디널리티를 쉽고 정확하게 구할수 있음.
      
(3) 높이균형 히스토그램
? 'equi-depth 히스토그램' 으로도 불린며 컬럼이 가진 값의 수보다 적은 버킷을 요청할 때 생성.
? 254이상의 버킷이 생성되면 높이균형 히스토그램으로 생성.
- endpoint_value : 버킷이 담당하는 가장 큰 값
- endpoint_number : 버킷 번호
? popular value에 대한 선택도/ 카디널리티 계산
- 선택도 = (조건절 값의 버킷 개수) / (총 버킷 개수)
- 카디널리티 = 총 로우 수 * 선택도
? non-popular value에 대한 선택도/카디널리티 계산
- 카디널리티 = 총 로우 수 * 선택도 = 총 로우 수 * density
? density
- 해당 컬럼을 '=' 조건으로 검색할 대의 선택도를 미리 구해놓은 값.
- 오라클이 density를 구하는 공식
- 히스토그램이 없을때 : density = 1/num_distinct
- 도수분포 히스토그램일 때 : density = 1/(2* (null을 제외한 총 로우 수))
-  '=' 조건 조회 시 옵티마이저가 어떤 때는 1/num_distinct를 이용하고 어떤 때는 미리 구해 놓은 density 값을 이용.

(4) 바인드 변수 사용시 카디널리티 계산
? '=' 조인일 때
? 히스토그램이 없을 때 : 1/num_distinct 사용
? 도수분포 히스토그램이 없을 때 : 1/num_distinct 사용
? 높이균형 히스토그램일 때 : density 사용
? 범위검색 조건일 때
- 범위검색 조건이 사용되면 옵티마이져는 고정된 규칙으로 선택도를 추정하는데, 운이 좋아 그값이 맞을 수 있지만 십중팔구 틀릴 가능성이 크다.
- 바인드 변수를 사용하면 정확한 컬럼 히스토그램에 근거하지 않고 카디널리티를 구하는 정해진 계산식에 기초해 비용을 계산하므로  최적이 아닌 실행계획을 수립할 가능성이 커짐 OLTP성 쿼리이더라도 값의 종류가 적고 분포가 균일하지 않을 대는 상수 조건을 쓰는 것이 유용할수도 있다.

(5) 결합 선택도
? 히스토그램을 많이 만들어 두어도 두 개 이상 컬럼에 대한 결합 선택도를 구할 때는 정확성이 떨어짐. 특히 조건절 컬럼 간에 상관관계가 있을때 발생.
? 동적 샘플링
- 오라클은 9i부터 동적 샘플링을 통해 결합선택도 문제를 해결할려고 시도.
- 소량의 데이터 샘플링을 통해 where 조건절에 사용된 두 개 이상 컬럼의 결합 분포를 구하는 기능으로서, 동적 샘플링 레벨을 4 이상으로 설정할 때만 작동.
? 다중 컬럼 통계
- 11g에서는 '확장형 통계(extended statistics)' 라고 불리는 기능을 통해 다중 컬럼에 대한 히스토그램도 생성가능.
- 다중 컬럼에 대한 히스토그램 생성.
    var ret varchar2(30)
  exec :ret := dbms_stats.create_extended_stats(user, '사원', '(급여, 상여)');
    print ret;

- 다중 컬럼에 대힌 히스토그램 삭제.
    exec dbms_stats.drop_extended_stats(user, '사원', '(급여, 상여)');

- 확장형 통계의 수집.
 begin
    dbms_stats.gather_table_stats(user, '사원',  method_opt=> 'for all column size skewonly for columns (급여, 상여) size 254');
 end;


(6) Histogram을 둘러싼 오해.
? 실행계획의 변화
- Histogram 이 생성되면 동일한 혹은 Bind 변수를 사용하는 SQL 문장들 조차 실행 계획이 바뀔수 있다. 심지어는 아무런 조건(Predicate)이 없는 경우에서조차 실행 계획이 변화가 생길수 있다.  원인은 Bind Peeking, Densty 의 변화, Join cardinality의 변화 때문이다.


? distinct count
- Histogram 을 둘러싼 가장 흔한 오해중 하나가 Disticnt Count에 대한 것이다. 즉 Histogram 은 distinct count가 낮은 column에 대해서만 유용하다는 것이다. 성별(남/여), 직업(공문원/ 전문직/ 군인/ 무직) 지역(서울/부산/대구) 과 같이 몇개 ~ 몇 십개 정도의 값으로 구성되는 경우에만 Histogram 이 의미가있는 것이 아닌가? 하는 것이 일반적으로 우리가 Histogram 에 대해 가지는 선입견이다.
결론부터 말하면, distinct count 와 Histogram  은 전혀 무관하다. Histogram  의 존재 의의는 Data의 Skewness에 의해서만 결정된다.

? index scan vs table full scan
- index가 없는 column 에 대해 Histogram  이 도대체 왜 필요한가?
  Histogram 의 존재 목적은 index scan vs table full scan 을 결정하는 것이 아니라 Cardinality의 정확성을높이는 것이다.

? Histogram 은 전지 전능한가?
- Histogram 과 관련된 최악의 오해는 “Histogram  을 수집했으니 Data가 아무리 Skew 되어도 Cardinality 가 합리적으로 계산될 것이다” 라는 근거없는 낙관론이다.
- Histogram 의 Bucket 내에 존재하지 않는 값(Non Existent Value)에 대한 Cardinality는 부정확 할수 밖에 없다. Frequency Histogram 의 경우에는  “1” 또는 “최소 개수/2”의 값을 사용한다.  어떤 값을 사용하든 부정확할 뿐이다. Height-Balanced Histogram  의 경우에는 Density 를 기반으로 선형적으로 감소하는 값을 사용한다. 역시 정확성을 기대하기는 어렵다.
- Bind 변수를 사용하면서 Bind Peeking 을 비활성화한 경우에도 역시 부정확성을 면할 수 없다. Frequency Histogram 의 경우에는 1/NDV 의 Selectivity를 사용한다. Height-Balanced Hisogram 의 경우에는 Density 를 사용한다
- Height-Balanced Histogram  의 경우에는 특정 값이 Popular Value로 분류 되느냐 Non Popular Value 로 분류되느냐에 따라 Carinality 에서 큰 차이가 날 수 있다.


- Histogram 은 많은 장점을 제공하지만 현실 세계에서 사용하다 보면 많은 한계점을 드러낸다.
이런 한계점을 명확하게 인식하고 사용해야 함정에 빠지는 일이 없을 것이다.


번호 제목 글쓴이 날짜 조회 수
35 1. 옵티마이저 file 휘휘 2011.04.18 6057
34 3. 옵티마이저의 한계 - P 휘휘 2011.04.18 3695
33 2. 옵티마이저 행동에 영향을 미치는 요소 balto 2011.04.18 6158
32 3. 옵티마이저의 한계 휘휘 2011.04.19 6698
31 4. 통계정보 Ⅰ file 토시리 2011.04.25 16002
» 6. 히스토그램 오예스 2011.04.25 17363
29 5. 카디널리티 오라클잭 2011.04.27 12913
28 7. 비용 file balto 2011.05.02 4989
27 8. 통계정보 Ⅱ AskZZang 2011.05.04 5913
26 1. 쿼리 변환이란? 운영자 2011.05.16 6254
25 3. 뷰 Merging 오라클잭 2011.05.17 6080
24 2. 서브쿼리 Unnesting 토시리 2011.05.18 2074
23 5. 조건절 이행 file balto 2011.05.30 5460
22 4. 조건절 Pushing 오예스 2011.05.31 17467
21 6. 조인 제거 AskZZang 2011.06.01 5440
20 7. OR-Expansion AskZZang 2011.06.01 8320
19 12. 기타 쿼리 변환 휘휘 2011.06.06 3118
18 10. 실체화 뷰 쿼리로 재작성 오라클잭 2011.06.08 9949
17 11. 집합 연산을 조인으로 변환 오라클잭 2011.06.08 4954
16 2. 소트를 발생시키는 오퍼레이션 file balto 2011.06.12 4846