꿈꾸는 개발자, DBA 커뮤니티 구루비

I. 일반적인 히스토그램

1. 히스토그램 개념

오라클은 히스토그램을 사용하여 데이터 분포가 고르지 않은 환경에서 Selectivity와 Cardinality 계산을 향상시킴
데이터 분포가 고르지 않을 때 히스토그램을 생성하지 않으면 NDV 값을 활용하여 Cardinality를 계산하지만 히스토그램을 생성하면 정확하게 Cardinality 값을 계산할 수 있다.

2. 예제


-- 0. Oracle Version
SELECT *
FROM   V$VERSION
;

BANNER
------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
NLSRTL Version 10.2.0.3.0 - Production
;

-- 1. 샘플 데이터 생성
DROP TABLE XSOFT_T;

CREATE TABLE XSOFT_T AS
SELECT DECODE(LENGTH(LEVEL),  1, 1,
                              2, 2,
                              3, 3) FLAG
FROM   DUAL
CONNECT BY LEVEL <= 999
;

-- 2. 통계정보 생성
-- 히스토그램 없이 통계정보 생성
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(USER,
                                  'XSOFT_T',
                                  METHOD_OPT => 'FOR ALL COLUMNS SIZE 1');
END;
/

-- 3. XSOFT_T 테이블 컬럼 통계정보 확인
-- 통계정보를 생성하지 않으면(FOR ALL COLUMNS SIZE 1) DENSITY 값은 (1 / NUM_DISTINCT) 값이 된다.
SELECT S.TABLE_NAME,
       S.COLUMN_NAME,
       S.NUM_DISTINCT,
       S.NUM_NULLS,
       S.DENSITY,
       S.LOW_VALUE,
       S.HIGH_VALUE,
       S.HISTOGRAM
FROM   USER_TAB_COLS S
WHERE  S.TABLE_NAME = UPPER('XSOFT_T')
;

TABLE_NAME COLUMN_NAM NUM_DISTINCT NUM_NULLS DENSITY LOW_VALUE  HIGH_VALUE HISTOGRAM
---------- ---------- ------------ --------- ------- ---------- ---------- ----------
XSOFT_T    FLAG                  3         0    .333 C102       C104       NONE
;

-- 4. XSOFT_T 테이블 컬럼 히스토그램 확인
-- 통계정보를 생성하지 않은 상태에서 USER_TAB_HISTOGRAMS에 조회되는 값들은
-- 히스토그램 값이 아니라 최소값과 최대값에 대한 정보들 뿐이다.
-- 그러므로 ENDPOINT_NUMBER 값은 0과 1 밖에 없다.
SELECT TABLE_NAME                      AS TABLE_NAME,
       COLUMN_NAME                     AS COLUMN_NAME,
       ENDPOINT_NUMBER                 AS ENDPOINT_NUMBER,
       ROUND(ENDPOINT_VALUE, 5)        AS ENDPOINT_VALUE,
       ROUND(ENDPOINT_ACTUAL_VALUE, 5) AS ENDPOINT_ACTUAL_VALUE
FROM   USER_TAB_HISTOGRAMS
WHERE  TABLE_NAME = UPPER('XSOFT_T')
;

TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE
---------- ---------- --------------- -------------- ---------------------
XSOFT_T    FLAG                     0          1.000
XSOFT_T    FLAG                     1          3.000
;

-- 5. Cardinality 확인
-- 통계정보가 생성되어 있지 않으므로 XSOFT_T 테이블에서 1, 2, 3의 값이 SKEW 되어
-- 있음에도 불구하고 Cardinality(ROWS)는 999(Base Cardinality) / 3(Distincy) = 333 값이 된다.
EXPLAIN PLAN FOR
SELECT *
FROM   XSOFT_T
WHERE  FLAG = 1
UNION ALL
SELECT *
FROM   XSOFT_T
WHERE  FLAG = 2
UNION ALL
SELECT *
FROM   XSOFT_T
WHERE  FLAG = 3
;

@XPLAN

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |   999 |  2997 |     9  (67)| 00:00:01 |
|   1 |  UNION-ALL         |         |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| XSOFT_T |   333 |   999 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| XSOFT_T |   333 |   999 |     3   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL| XSOFT_T |   333 |   999 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

   2 - filter("FLAG"=1)
   3 - filter("FLAG"=2)
   4 - filter("FLAG"=3)
;

-- 6. 히스토그램 포함 통계정보 재생성
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(USER,
                                  'XSOFT_T',
                                  METHOD_OPT => 'FOR ALL COLUMNS SIZE SKEWONLY');
END;
/

-- 7. XSOFT 테이블 컬럼 통계정보 확인
-- 히스토그램을 생성하여(FOR ALL COLUMNS SIZE SKEWONLY) DENSITY 값은 (1 / NUM_DISTINCT) 값이 안된다.
-- 그리고 HISTOGRAM 컬럼에는 NULL이 아닌 'FREQUENCY' 히스토그램이 생성된다.
SELECT S.TABLE_NAME,
       S.COLUMN_NAME,
       S.NUM_DISTINCT,
       S.NUM_NULLS,
       S.DENSITY,
       S.LOW_VALUE,
       S.HIGH_VALUE,
       S.HISTOGRAM
FROM   USER_TAB_COLS S
WHERE  S.TABLE_NAME = UPPER('XSOFT_T')
;

TABLE_NAME COLUMN_NAM NUM_DISTINCT NUM_NULLS DENSITY LOW_VALUE  HIGH_VALUE HISTOGRAM
---------- ---------- ------------ --------- ------- ---------- ---------- ----------
XSOFT_T    FLAG                  3         0    .001 C102       C104       FREQUENCY

-- 8. XSOFT_T 테이블 컬럼 히스토그램 확인
-- 히스토그램을 생성하였으므로 정확하게 분포도를 확인할 수 있다.
SELECT TABLE_NAME                      AS TABLE_NAME,
       COLUMN_NAME                     AS COLUMN_NAME,
       ENDPOINT_NUMBER                 AS ENDPOINT_NUMBER,
       ROUND(ENDPOINT_VALUE, 5)        AS ENDPOINT_VALUE,
       ROUND(ENDPOINT_ACTUAL_VALUE, 5) AS ENDPOINT_ACTUAL_VALUE
FROM   USER_TAB_HISTOGRAMS
WHERE  TABLE_NAME = UPPER('XSOFT_T')
;

TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE
---------- ---------- --------------- -------------- ---------------------
XSOFT_T    FLAG                     9          1.000
XSOFT_T    FLAG                    99          2.000
XSOFT_T    FLAG                   999          3.000
;

-- 9. Cardinality 재 확인
-- FLAG 컬럼에 히스토그램이 있기 때문에 Cardinality(ROWS) 값이 NDV 값이 아니라
-- 정확하게 예상 로우수를 인식하고 있다.
EXPLAIN PLAN FOR
SELECT *
FROM   XSOFT_T
WHERE  FLAG = 1
UNION ALL
SELECT *
FROM   XSOFT_T
WHERE  FLAG = 2
UNION ALL
SELECT *
FROM   XSOFT_T
WHERE  FLAG = 3
;

@XPLAN

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |   999 |  2997 |     9  (67)| 00:00:01 |
|   1 |  UNION-ALL         |         |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| XSOFT_T |     9 |    27 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| XSOFT_T |    90 |   270 |     3   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL| XSOFT_T |   900 |  2700 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

   2 - filter("FLAG"=1)
   3 - filter("FLAG"=2)
   4 - filter("FLAG"=3)
;

3. 오라클이 히스토그램을 관리하는 방식

도수분포 히스토그램(Frequency Histogram)

특정 컬럼의 값이 분포를 NDV 값 254개 이하에서 정확하게 인식할 수 있는 방법이다.
가령 C1 컬럼의 값이 1(100 rows), 2(1,000 rows) 3(10,000 rows) 있는 상태에서 Frequency로 히스토그램을 생성했다면 각 값에 따른 정확한 값을 인식한다.

높이균형 히스토그램(Height-Balance Histogram)

특정 컬럼의 값이 분포를 NDV 값 254개 이하에서 특정 높이를 지정하여 그 높이 안에서 균형있게 데이터를 배치하는 방법이다.
가령 C1 컬럼의 값이 1(100 rows), 2(1,000 rows) 3(10,000 rows) 있는 상태에서 Height-Balance를 2인 값으로 히스토그램을 생성했다면 2개의 값 사이에서 특정 높이를 지정하여 분포도를 인식하는 방법이다.