비용기반의 오라클 원리 (2009년)
일반적인 히스토그램 0 0 32,213

by 구루비스터디 히스토그램 Histogram [2023.09.23]


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개의 값 사이에서 특정 높이를 지정하여 분포도를 인식하는 방법이다.
"코어 오라클 데이터베이스 스터디모임" 에서 2009년에 "비용기반의 오라클 원리 " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/4412

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입