1. HOME
  2. [종료]구루비 DB 스터디
  3. 2013 상반기 DB멘토링 스터디
  4. 히스토그램

히스토그램 (by niceboyous) [2013.01.29]

(1) 히스토그램 유형

  • 유형 : 높이균형(Height-Balanced) 히스토그램,도수분포(Frequency) 히스토그램
  • 히스토그램을 생성하려면 컬럼 통계 수집 시 버킷 개수를 2 이상으로 지정
  • dba_histograns 또는 dba_tab_histograms 뷰를 통해 히스토그램 정보 확인
    -FREQUENCY : 값별로 빈도수를 저장하는 도수분포 히스토그램(값의 수 = 버킷 개수)
    -HEIGHT-BALANCED : 각 버킷의 높이가 동일한 높이균형 히스토그램
    -NONE : 히스토그램을 생성하지 않은 경우
    {CODE:SQL}
    CREATE SEQUENCE SEQ;
    CREATE TABLE MEMBER( MEM_ID NUMBER, AGE NUMBER( 2 ) );
    EXEC DBMS_RANDOM.SEED(0);
    INSERT INTO MEMBER
    SELECT SEQ.NEXTVAL, DBMS_RANDOM.VALUE( 1,19 ) FROM DUAL CONNECT BY LEVEL <= 50;
    INSERT INTO MEMBER
    SELECT SEQ.NEXTVAL, DBMS_RANDOM.VALUE( 20,29 ) FROM DUAL CONNECT BY LEVEL <= 270;
    INSERT INTO MEMBER
    SELECT SEQ.NEXTVAL, DBMS_RANDOM.VALUE( 30,39 ) FROM DUAL CONNECT BY LEVEL <= 330;
    INSERT INTO MEMBER
    SELECT SEQ.NEXTVAL, DBMS_RANDOM.VALUE( 30,39 ) FROM DUAL CONNECT BY LEVEL <= 330;
    INSERT INTO MEMBER
    SELECT SEQ.NEXTVAL, 40 FROM DUAL CONNECT BY LEVEL <= 1000; --> POPULAR VALUE
    INSERT INTO MEMBER
    SELECT SEQ.NEXTVAL, DBMS_RANDOM.VALUE( 41,49 ) FROM DUAL CONNECT BY LEVEL <= 200;
    INSERT INTO MEMBER
    SELECT SEQ.NEXTVAL, DBMS_RANDOM.VALUE( 50,59 ) FROM DUAL CONNECT BY LEVEL <= 100;
    INSERT INTO MEMBER
    SELECT SEQ.NEXTVAL, DBMS_RANDOM.VALUE( 60,99 ) FROM DUAL CONNECT BY LEVEL <= 50;

SQL> SELECT CASE WHEN AGE <= 19 THEN '10'
2 WHEN AGE >=20 AND AGE < 30 THEN '20'
3 WHEN AGE >=30 AND AGE < 40 THEN '30'
4 WHEN AGE >=40 AND AGE < 50 THEN '40'
5 WHEN AGE >=50 AND AGE < 60 THEN '50'
6 WHEN AGE >=60 THEN '60'
7 END AGE_GRP, COUNT(*)
8 FROM MEMBER
9 GROUP BY CASE WHEN AGE <= 19 THEN '10'
10 WHEN AGE >=20 AND AGE < 30 THEN '20'
11 WHEN AGE >=30 AND AGE < 40 THEN '30'
12 WHEN AGE >=40 AND AGE < 50 THEN '40'
13 WHEN AGE >=50 AND AGE < 60 THEN '50'
14 WHEN AGE >=60 THEN '60'
15 END
16 ORDER BY AGE_GRP;

AG COUNT(*)
--

--
10 50
20 270
30 660
40 1000
50 100
60 50
{CODE}

(2) 도수분포 히스토그램

  • 'value-based 히스토그램'
  • 값별로 빈도수(frequency number)를 저장하는 히스토그램
  • 컬럼 값마다 하나의 버킷을 할당(값의 수 = 버킷 개수), 사용자가 요청한 버킷 개수가 컬럼이 가진 값의 수보다 많거나 같을 때 사용, 최대 254개의 버킷만 허용


SQL> SELECT COUNT(*), COUNT( DISTINCT AGE ) FROM MEMBER; --버킷 개수가 79개로 도수분포 히스토그램 사용 가능

  COUNT(*) COUNT(DISTINCTAGE)
---------- ------------------
      2130                 79

SQL>  begin
  2      dbms_stats.gather_table_stats( user, 'MEMBER', method_opt=> 'for all columns size 100' );  --히스토그램 생성
  3   end;
  4   /

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> SELECT NUM_DISTINCT, NUM_BUCKETS, HISTOGRAM
  2        FROM USER_TAB_COL_STATISTICS
  3      WHERE TABLE_NAME = 'MEMBER'
  4         AND COLUMN_NAME = 'AGE'
  5  ;

NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------ ----------- ---------------
          79          79 FREQUENCY
  • 100개의 버킷을 요청했는데 79개만 생성된 것은 실제값의 종류가 79개뿐이기 때문
  • dba/all/user_histogam
    -endpoint_value : 버킷에 할당된 컬럼 값
    -endpoint_number : endpoint_value로 정렬했을 때, 최소 값부터 현재 값까지의 누적수량
    -값별로 빈도수를 미리 계산해 두는 방식이기 때문에 조건절을 만족하는 카디널리티를 쉽고 정확하게 구할 수 있으나 시스템 자원에 한계가 있으므로 값의 수가 많을 때는 높이균형 히스토그램 사용

h3.(3) 높이균형 히스토그램

  • equi-depth 히스토그램
  • endpoint_number : 버킷번호
  • endpoint_value : 버킷이 담당하는 가장 큰 값
  • 버킷 < 컬럼
  • 하나의 버킷이 여러개의 값을 관리함
  • 각 버킷의 높이가 같음
  • 각 버킷은 1 / ( 버킷 개수 ) * 100 %의 데이터 분포를 갖음. ( ex : 2000( 컬럼 가진 값수 ) / 20( 버킷수 ) = 100에 해당하는 노피( 분포 5% ) )
  • 빈도 수가 많은 값(popular value)을 포함할 떄는 두개 이상의 버킷이 할당 됨
    {CODE:SQL}

begin
dbms_stats.gather_table_stats( user, 'MEMBER', method_opt=> 'for all columns size 20' ); --히스토그램 생성
end;
/

SQL> SELECT NUM_DISTINCT, NUM_BUCKETS, HISTOGRAM
2 FROM USER_TAB_COL_STATISTICS
3 WHERE TABLE_NAME = 'MEMBER'
4 AND COLUMN_NAME = 'AGE';

NUM_DISTINCT NUM_BUCKETS HISTOGRAM

---
---
79 20 HEIGHT BALANCED

– ENDPOINT_NUMBER = 1 버킷은 1~22 연령대 구간을, 20은 55~99연령대 구간을 대표한다.
SQL> SELECT ENDPOINT_NUMBER, ENDPOINT_VALUE
2 FROM USER_HISTOGRAMS
3 WHERE TABLE_NAME= 'MEMBER'
4 AND COLUMN_NAME = 'AGE';

ENDPOINT_NUMBER ENDPOINT_VALUE

---
--
0 1 <== 첫 번째 레코드는 버킷이 아니라 '최소값' 을 표현하는 용도
1 22
2 26
3 30
4 31
5 33
6 34
7 36
8 37
9 39
18 40 <== popular value ( 압축 )

ENDPOINT_NUMBER ENDPOINT_VALUE

---
--
19 54
20 99

13 개의 행이 선택되었습니다.

SQL> SELECT '~' || AGE "연령대", ENDPOINT_NUMBER, DIFF
2 , ROUND( 100 * diff / sum( diff) over() ) "RATION(%)"
3 , ROUND( T.NUM_ROWS * DIFF / SUM( DIFF ) OVER()) "인원수(명)"
4 FROM (SELECT TABLE_NAME
5 , ENDPOINT_VALUE AGE, ENDPOINT_NUMBER
6 , ENDPOINT_NUMBER - LAG( ENDPOINT_NUMBER ) OVER (ORDER BY ENDPOINT_VALUE) DIFF
7 , LAG(ENDPOINT_NUMBER) OVER( ORDER BY ENDPOINT_VALUE) PREV
8 FROM USER_HISTOGRAMS
9 WHERE TABLE_NAME = 'MEMBER'
10 AND COLUMN_NAME = 'AGE' ) H, USER_TABLES T
11 WHERE H.ENDPOINT_NUMBER > 0
12 AND T.TABLE_NAME = H.TABLE_NAME
13 ORDER BY 1;

연령대 ENDPOINT_NUMBER DIFF RATION(%) 인원수(명)

-
---
--
--
--
~22 1 1 5 107
~26 2 1 5 107
~30 3 1 5 107
~31 4 1 5 107
~33 5 1 5 107
~34 6 1 5 107
~36 7 1 5 107
~37 8 1 5 107
~39 9 1 5 107
~40 18 9 45 959
~54 19 1 5 107
~99 20 1 5 107

-- 오라클은 popular value( 40 )에 대한 카디널리티를 구할 때만 버킷에 의한 계산식을 사용하고
나머지는 미리 구해놓은 density 값을 이용한다.

12 개의 행이 선택되었습니다.

{CODE}

h3.Popular value에 대한 선택도/카디널리티 계산

  • 조건절 값이 두개 이상 버킷을 가진 popular value일 경우 선택도 = (조건절 값의 버킷 개수) / (총 버킷 개수)로 조건절 값의 버킷 개수가 총 버킷 개수(히스토그램 뷰에서 조회되는 가장 큰 endpoint_number)에서 차지하는 비중을 가지고 선택도를 구함
  • 카디널리티 = 총 로우 수 * 선택도 = (총 로우 수 ) * ( 조건절 값의 버킷 개수 ) / ( 총 버킷 개수 ) = 2130 * 9 / 20 = 958.5

SQL> SELECT COUNT(*) FROM MEMBER ;

  COUNT(*)
----------
      2130

SQL> SELECT COUNT(*) FROM MEMBER WHERE AGE = 40;

  COUNT(*)
----------
      1000

SQL> @XPLAN

---------------------------------------------------------------------------------------
| Id  | Operation          | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |      1 |        |      1 |00:00:00.01 |       5 |
|   1 |  SORT AGGREGATE    |        |      1 |      1 |      1 |00:00:00.01 |       5 |
|*  2 |   TABLE ACCESS FULL| MEMBER |      1 |    959 |   1000 |00:00:00.01 |       5 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("AGE"=40)

h3.non-popular value에 대한 선택도/카디널리티 계산
-카디널리티 = 총 로우 수 * 선택도 = 총 로우수 * density



SQL> SELECT 1/NUM_DISTINCT, TO_CHAR( density, 'FM999.999999999999999999999' ) AS density
  2       , round( TO_CHAR( density, 'FM999.999999999999999999999' ) * 2130 ) AS CD
  3    FROM USER_TAB_COL_STATISTICS
  4   WHERE TABLE_NAME = 'MEMBER'
  5     AND COLUMN_NAME = 'AGE'
  6  ;

1/NUM_DISTINCT DENSITY                            CD
-------------- -------------------------- ----------
    .012658228 .0234101956873987                  50


SQL> SELECT COUNT(*) FROM MEMBER WHERE AGE = 39;

  COUNT(*)
----------
        25

---------------------------------------------------------------------------------------
| Id  | Operation          | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |      1 |        |      1 |00:00:00.01 |       5 |
|   1 |  SORT AGGREGATE    |        |      1 |      1 |      1 |00:00:00.01 |       5 |
|*  2 |   TABLE ACCESS FULL| MEMBER |      1 |     15 |     25 |00:00:00.01 |       5 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("AGE"=39)

Density

  • 해당 컬럼을 '='조건으로 검색할 때의 선택도를 미리 구해 놓은 값
  • 히스토그램이 없을때 = 1/num_distinct
  • 높이 군형 히스토그램일 때 = 시그마( 모든 non-popular value 빈도스 ) 제곱 / null을 제외한 총 로우 수 * 시그마( 든 non-popular value 빈도수 )
  • 도수 분포 히스토그램일 때 = 1 / ( 2 * null을 제외한 총 로우 수 )
  • 카디널리티 = 총 로우수 * 선택도 = NUM_ROWS * density

h3.(4) 바인드 변수 사용 시 카디널리티 계산

  • SQL을 최적화하는 시점에 조건절 컬럼의 데이터 분포를 사용하지 못하는 문제점
  • 평균 분포를 가정한 실행계획을 생성

'=' 조건일 때

  • 히스토그램이 없을 때 : 1/num_distinct 사용
  • 도수분포 히스토그램일 때 : 1/num_distinct 사용
  • 높이균형 히스토그램일 때 : density 사용

범위검색 조건일 때

-( 1~4 = 5%, 5~8 = 0.25% )

  • 1 : 번호 > :NO
  • 2 : 번호 < :NO
  • 3 : 번호 >= :NO
  • 4 : 번호 <= :NO
  • 5 : 번호 BETWEEN :NO1 AND :NO2
  • 6 : 번호 >= :NO1 AND 번호 < :NO2
  • 7 : 번호 >= :NO1 AND 번호 < :NO2
  • 8 : 번호 > :NO1 AND 번호 < :NO2

{CODE:SQL}

DROP TABLE T PURGE;

CREATE TABLE T AS
SELECT ROWNUM NO FROM DUAL CONNECT BY LEVEL <= 1000;

begin
dbms_stats.gather_table_stats( user, 'T', method_opt=> 'for all columns size 254' );
end;
/

SQL> SELECT COUNT(*) FROM T WHERE NO <= :NO;

COUNT(*)

--
0

SQL> @XPLAN

---

IdOperationNameStartsE-RowsA-RowsA-Time

---

0SELECT STATEMENT1100:00:00.01
1SORT AGGREGATE11100:00:00.01
  • 2
TABLE ACCESS FULLT150000:00:00.01

---

SQL> VAR NO1 NUMBER;
SQL> VAR NO2 NUMBER;
SQL> SELECT COUNT(*) FROM T WHERE NO BETWEEN :NO1 AND :NO2;

COUNT(*)

--
0

SQL> @XPLAN

IdOperationNameStartsE-RowsA-RowsA-Time

0SELECT STATEMENT1100:00:00.01
1SORT AGGREGATE11100:00:00.01
  • 2
FILTER1000:00:00.01
  • 3
TABLE ACCESS FULLT03000:00:00.01

-- 상수일경우 거의 정확한 카디널리티 계산
SQL> SELECT COUNT(*) FROM T WHERE NO <= 100;

COUNT(*)

--
100

SQL> @XPLAN

-

IdOperationNameStartsE-RowsA-RowsA-TimeBuffers

-

0SELECT STATEMENT1100:00:00.013
1SORT AGGREGATE11100:00:00.013
  • 2
TABLE ACCESS FULLT19810000:00:00.013

-

Predicate Information (identified by operation id):

---

2 - filter("NO"<=100)

SQL> SELECT COUNT(*) FROM T WHERE NO BETWEEN 500 AND 600;

COUNT(*)

--
101

SQL> @XPLAN

-

IdOperationNameStartsE-RowsA-RowsA-TimeBuffers

-

0SELECT STATEMENT1100:00:00.013
1SORT AGGREGATE11100:00:00.013
  • 2
TABLE ACCESS FULLT19810100:00:00.013

-

Predicate Information (identified by operation id):

---

2 - filter(("NO">=500 AND "NO"<=600))

{CODE}

h3.(5) 결합 선택도

  • 아무리 히스토그램을 많이 만들어 두어도 두개 이상 컬럼에 대한 결합 선택도를 구할 대는 정확성이 떨어짐
  • 9i부터 이 문제를 해결하기 위해 동적 샘플링을 시도함

동적 샘플링

{CODE:SQL}

DROP TABLE T PURGE

CREATE TABLE T AS
SELECT EMPNO , ENAME , SAL , SAL * 0.1 SAL_BO FROM SCOTT.EMP;

begin
dbms_stats.gather_table_stats( user, 'T', method_opt=> 'for columns SAL size 254 SAL_BO SIZE 254' );
end;
/
-- 급여에 일괄적으로 0.1을 곱해 상여를 구했으므로 두 컬럼 간에 상관관계가 매우 높지만 옵티마이저는 모름
-- 히스토그램 버킷 개수를 최대치로 주더라도 단일 컬럼 분포만 저장하기 때문

SQL> SELECT * FROM T
2 WHERE SAL >= 2000
3 AND SAL_BO >= 200;

EMPNO ENAME SAL SAL_BO

--
--
--
--
7566 JONES 2975 297.5
7698 BLAKE 2850 285
7782 CLARK 2450 245
7788 SCOTT 3000 300
7839 KING 5000 500
7902 FORD 3000 300

6 개의 행이 선택되었습니다.

SQL> @XPLAN

IdOperationNameStartsE-RowsA-RowsA-TimeBuffers

0SELECT STATEMENT1600:00:00.013
  • 1
TABLE ACCESS FULLT12600:00:00.013

Predicate Information (identified by operation id):

---

1 - filter(("SAL">=2000 AND "SAL_BO">=200))

-- 옵티마이저는 카디널러티를 2로 예상했지만 실제 수행 결과는 6건

SQL> SELECT /*+ DYNAMIC_SAMPLING( 4 ) */ * FROM T
2 WHERE SAL >= 2000
3 AND SAL_BO >= 200;

EMPNO ENAME SAL SAL_BO

--
--
--
--
7566 JONES 2975 297.5
7698 BLAKE 2850 285
7782 CLARK 2450 245
7788 SCOTT 3000 300
7839 KING 5000 500
7902 FORD 3000 300

6 개의 행이 선택되었습니다.

SQL> @XPLAN

IdOperationNameStartsE-RowsA-RowsA-TimeBuffers

0SELECT STATEMENT1600:00:00.013
  • 1
TABLE ACCESS FULLT16600:00:00.013

Predicate Information (identified by operation id):

---

1 - filter(("SAL">=2000 AND "SAL_BO">=200))

--동적 샘플링 레벨을 4로 설정하고 다시 수행
{CODE}

다중 컬럼 통계

  • 11g
  • 확장형 통계 : 컬럼 그룹 통계생성
  1. HOME
  2. [종료]구루비 DB 스터디
  3. 2013 상반기 DB멘토링 스터디
  4. 히스토그램