비용기반의 오라클 원리 (2009년)
데이터문제 다시 생각하기 0 0 31,954

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


VI. 데이터문제 다시 생각하기

1. 부적절한 데이터 타입


만약 날짜타입의 컬럼을 이용하여 특정 기간의 데이터를 조회하였을 때 통계정보가 있을 경우
  • 옵티마이저는 실 데이터와 근접한 예상 로우를 실행계획에서 보여준다. 하지만 이를 VARCAHR2 타입이나 NUMBER 타입으로 동일한 의미를 부여한 뒤 조회하였을 경우 옵티마이저는 정확한 계산을 할 수 없다.


그 이유는 날짜타입일 경우 2009/12/31 ~ 2010/00/01 사이에 하루만 있다는 것을 알 수 있지만
  • '20091231 ~ 20100001'와 같은 VARCHAR2 타입일 경우 이 사이에 '20091232, 20091233, ...' 같은 값이 있을 것으로 예상하기 때문에 정확한 값을 추측할 수 없다.


그러므로 가급적 사용 용도에 맞는 데이터타입을 설정하여 사용해야 하고 만약 그럴 수 없다면
  • 히스토그램을 생성하여 '20091231 ~ 20100001' 사이에 데이터가 거의 없다는 정보를 옵티마이저에게 제공해야 한다.


아래는 이에 대해 테스트 한 내용니다.

DROP TABLE T1;

CREATE TABLE T1 (D1	DATE,
	             N1	NUMBER(8),
	             V1	VARCHAR2(8))
;

INSERT INTO T1
SELECT D1,
       TO_NUMBER(TO_CHAR(D1, 'YYYYMMDD')),
       TO_CHAR(D1, 'YYYYMMDD')
FROM   (SELECT TO_DATE('31-DEC-1999') + ROWNUM D1
        FROM   ALL_OBJECTS
        WHERE  ROWNUM <= 1827)
;

COMMIT;

BEGIN
	DBMS_STATS.GATHER_TABLE_STATS(OWNNAME			=> USER,
                            	  TABNAME			=> 'T1',
                            	  CASCADE			=> TRUE,
                            	  ESTIMATE_PERCENT	=> NULL,
                            	  METHOD_OPT		=>'FOR ALL COLUMNS SIZE 1');
END;
/

EXPLAIN PLAN FOR
SELECT *
FROM   T1
WHERE  D1 BETWEEN TO_DATE('30-DEC-2002', 'DD-MON-YYYY')
          AND     TO_DATE('05-JAN-2003', 'DD-MON-YYYY')
;

@XPLAN
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     8 |   184 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     8 |   184 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("D1">=TO_DATE('2002-12-30 00:00:00', 'yyyy-mm-dd
              hh24:mi:ss') AND "D1"<=TO_DATE('2003-01-05 00:00:00', 'yyyy-mm-dd
              hh24:mi:ss'))
;

EXPLAIN PLAN FOR
SELECT	*
FROM	T1
WHERE 	N1 BETWEEN 20021230
           AND     20030105
;

@XPLAN
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   396 |  9108 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   396 |  9108 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("N1">=20021230 AND "N1"<=20030105)
;

EXPLAIN PLAN FOR
SELECT	*
FROM	T1
WHERE 	V1 BETWEEN '20021230'
           AND     '20030105'
;

@XPLAN
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   396 |  9108 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   396 |  9108 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("V1">='20021230' AND "V1"<='20030105')

BEGIN
	DBMS_STATS.GATHER_TABLE_STATS(OWNNAME			=> USER,
                            	  TABNAME			=> 'T1',
                            	  CASCADE			=> TRUE,
                            	  ESTIMATE_PERCENT	=> NULL,
                            	  METHOD_OPT		=>'FOR ALL COLUMNS SIZE 120');
END;
/

SELECT ROWNUM BUCKET,
       PREV LOW_VAL,
       CURR HIGH_VAL,
       CURR - PREV WIDTH,
       ROUND((1827 / 120) / (CURR - PREV), 4) HEIGHT
FROM   (SELECT ENDPOINT_VALUE CURR,
               LAG(ENDPOINT_VALUE, 1) OVER(ORDER BY ENDPOINT_NUMBER) PREV
        FROM   USER_TAB_HISTOGRAMS
        WHERE  TABLE_NAME  = 'T1'
        AND    COLUMN_NAME = 'N1')
WHERE  PREV IS NOT NULL
ORDER  BY CURR
;

BUCKET    LOW_VAL   HIGH_VAL      WIDTH         HEIGHT
------ ---------- ---------- ---------- --------------
     1   20000101   20000116         15          1.015
     2   20000116   20000201         85           .179
.......................................................
    21   20001115   20001201         86           .177
    22   20001201   20001217         16           .952
    23   20001217   20010102       8885           .002
    24   20010102   20010118         16           .952
    25   20010118   20010203         85           .179
.......................................................
    45   20011117   20011202         85           .179
    46   20011202   20011217         15          1.015
    47   20011217   20020101       8884           .002
    48   20020101   20020116         15          1.015
    49   20020116   20020131         15          1.015
.......................................................
    70   20021127   20021212         85           .179
    71   20021212   20021227         15          1.015
    72   20021227   20030111       8884           .002
    73   20030111   20030126         15          1.015
.......................................................
    94   20031122   20031207         85           .179
    95   20031207   20031222         15          1.015
    96   20031222   20040106       8884           .002
    97   20040106   20040121         15          1.015
    98   20040121   20040205         84           .181
.......................................................

EXPLAIN PLAN FOR
SELECT *
FROM   T1
WHERE  D1 BETWEEN TO_DATE('30-DEC-2002', 'DD-MON-YYYY')
          AND     TO_DATE('05-JAN-2003', 'DD-MON-YYYY')
;

@XPLAN
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     6 |   138 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     6 |   138 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("D1">=TO_DATE('2002-12-30 00:00:00', 'yyyy-mm-dd
              hh24:mi:ss') AND "D1"<=TO_DATE('2003-01-05 00:00:00', 'yyyy-mm-dd
              hh24:mi:ss'))

EXPLAIN PLAN FOR
SELECT	*
FROM	T1
WHERE 	N1 BETWEEN 20021230
           AND     20030105
;

@XPLAN
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    15 |   345 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |    15 |   345 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("N1">=20021230 AND "N1"<=20030105)

EXPLAIN PLAN FOR
SELECT	*
FROM	T1
WHERE 	V1 BETWEEN '20021230'
           AND     '20030105'
;

@XPLAN
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    15 |   345 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |    15 |   345 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("V1">='20021230' AND "V1"<='20030105')





2. 위험헌 디폴트 값


-- 1. 테이블 생성
DROP TABLE GENERATOR;

CREATE TABLE GENERATOR AS
SELECT ROWNUM ID
FROM   ALL_OBJECTS
WHERE  ROWNUM <= 2000
;

-- 1-1) 디폴트 값 있는 T1
CREATE TABLE T1
NOLOGGING
PCTFREE 0
AS
SELECT /*+ ORDERED USE_NL(V2) */
       DECODE(MOD(ROWNUM - 1, 1000),
              0,
              TO_DATE('31-DEC-4000'),
              TO_DATE('01-JAN-2000') + TRUNC((ROWNUM - 1) / 100)) DATE_CLOSED
FROM   GENERATOR V1,
       GENERATOR V2
WHERE  ROWNUM <= 1827 * 100
;

-- 1-2) 디폴트 값 없는 T2
CREATE TABLE T2
NOLOGGING
PCTFREE 0
AS
SELECT /*+ ORDERED USE_NL(V2) */
       TO_DATE('01-JAN-2000') + TRUNC((ROWNUM - 1) / 100) DATE_CLOSED
FROM   GENERATOR V1,
       GENERATOR V2
WHERE  ROWNUM <= 1827 * 100
;

-- 2. 데이터 조회
-- 2-1) 디폴트 값 있는 T1
SELECT DATE_CLOSED, COUNT(*)
FROM   (SELECT /*+ ORDERED USE_NL(V2) */
               DECODE(MOD(ROWNUM - 1, 1000),
                      0,
                      TO_DATE('31-DEC-4000'),
                      TO_DATE('01-JAN-2000') + TRUNC((ROWNUM - 1) / 100)) DATE_CLOSED
        FROM   GENERATOR V1,
               GENERATOR V2
        WHERE  ROWNUM <= 1827 * 100)
GROUP BY DATE_CLOSED
ORDER BY 1 DESC
;

DATE_CLOSED    COUNT(*)
------------ ----------
4000-12-31	  183
2004-12-31	  100
2004-12-30	  100
2004-12-29	  100
.......................
2000-01-04	  100
2000-01-03	  100
2000-01-02	  100
2000-01-01	  99
;

-- 2-2) 디폴트 값 없는 T2
SELECT DATE_CLOSED, COUNT(*)
FROM   (SELECT /*+ ORDERED USE_NL(V2) */
               TO_DATE('01-JAN-2000') + TRUNC((ROWNUM - 1) / 100) DATE_CLOSED
        FROM   GENERATOR V1,
               GENERATOR V2
        WHERE  ROWNUM <= 1827 * 100)
GROUP BY DATE_CLOSED
ORDER BY 1 DESC
;

DATE_CLOSED    COUNT(*)
------------ ----------
2004-12-31	 100
2004-12-30	 100
2004-12-29	 100
.......................
2000-01-03	 100
2000-01-02	 100
2000-01-01	 100
;

-- 3. 통계정보 생성
-- 3-1) 디폴트 값 있는 T1
BEGIN
	DBMS_STATS.GATHER_TABLE_STATS(OWNNAME			=> USER,
                            	  TABNAME			=> 'T1',
                            	  CASCADE			=> TRUE,
                                  ESTIMATE_PERCENT	=> NULL,
                            	  METHOD_OPT		=>'FOR ALL COLUMNS SIZE 1');
END;
/

-- 3-2) 디폴트 값 없는 T2
BEGIN
	DBMS_STATS.GATHER_TABLE_STATS(OWNNAME			=> USER,
                            	  TABNAME			=> 'T2',
                            	  CASCADE			=> TRUE,
                                  ESTIMATE_PERCENT	=> NULL,
                            	  METHOD_OPT		=>'FOR ALL COLUMNS SIZE 1');
END;
/

-- 4. 통계정보 확인
-- 4-1) 디폴트 값 있는 T1
SELECT COLUMN_NAME,
       NUM_DISTINCT,
       DENSITY
FROM   USER_TAB_COLUMNS
WHERE  TABLE_NAME = 'T1'
;

COLUMN_NAME                                                  NUM_DISTINCT    DENSITY
------------------------------------------------------------ ------------ ----------
DATE_CLOSED                                                          1828 .000547046
;

-- 4-2) 디폴트 값 없는 T2
SELECT COLUMN_NAME,
       NUM_DISTINCT,
       DENSITY
FROM   USER_TAB_COLUMNS
WHERE  TABLE_NAME = 'T2'
;

COLUMN_NAME                                                  NUM_DISTINCT    DENSITY
------------------------------------------------------------ ------------ ----------
DATE_CLOSED                                                          1827 .000547345


-- 5. EXPLAIN PLAN
-- 5-1) 디폴트 값 있는 T1
EXPLAIN PLAN FOR
SELECT *
FROM   T1
WHERE  DATE_CLOSED BETWEEN TO_DATE('01-JAN-2003', 'DD-MON-YYYY')
                   AND     TO_DATE('31-DEC-2003', 'DD-MON-YYYY')
;

@XPLAN;
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   291 |  2328 |    89   (5)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T1   |   291 |  2328 |    89   (5)| 00:00:02 |
--------------------------------------------------------------------------

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

   1 - filter("DATE_CLOSED"<=TO_DATE('2003-12-31 00:00:00', 'yyyy-mm-dd
              hh24:mi:ss') AND "DATE_CLOSED">=TO_DATE('2003-01-01 00:00:00',
              'yyyy-mm-dd hh24:mi:ss'))
;

-- 공식
Cardinality = base cardinality * ((required range) / (column high value - column low value) + N / num_distinct)
            = 182700 * ((2003년 12월 31일 - 2003년 1월 1일) / (4000년 12월 31일 - 2000년 1월 1일) + 2 / 1828)
            = 182700 * (364 / 730850 + 0.001094092)
            = 182700 * 0.001592142
            = 290

-- 5-2) 디폴트 값 없는 T2
EXPLAIN PLAN FOR
SELECT *
FROM   T2
WHERE  DATE_CLOSED BETWEEN TO_DATE('01-JAN-2003', 'DD-MON-YYYY')
                   AND     TO_DATE('31-DEC-2003', 'DD-MON-YYYY')
;

@XPLAN;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 36620 |   286K|    89   (5)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T2   | 36620 |   286K|    89   (5)| 00:00:02 |
--------------------------------------------------------------------------

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

   1 - filter("DATE_CLOSED">=TO_DATE('2003-01-01 00:00:00', 'yyyy-mm-dd
              hh24:mi:ss') AND "DATE_CLOSED"<=TO_DATE('2003-12-31 00:00:00',
              'yyyy-mm-dd hh24:mi:ss'))
;

-- 공식
Cardinality = base cardinality * ((required range) / (column high value - column low value) + N / num_distinct)
            = 182700 * ((2003년 12월 31일 - 2003년 1월 1일) / (2004년 12월 31일 - 2000년 1월 1일) + 2 / 1828)
            = 182700 * (364 / 1826 + 0.001094092)
            = 182700 * 0.200436918
            = 36619.82492
;

-- 6. T1 테이블 히스토그램 생성
BEGIN
	DBMS_STATS.GATHER_TABLE_STATS(OWNNAME			=> USER,
                            	  TABNAME			=> 'T1',
                            	  CASCADE			=> TRUE,
                            	  ESTIMATE_PERCENT	=> NULL,
                            	  METHOD_OPT		=>'FOR ALL COLUMNS SIZE 11');
END;
/

-- 7. 데이터 분포 확인
SELECT ROWNUM BUCKET,
       TO_CHAR(TO_DATE(PREV, 'J'), 'DD-MON-YYYY') LOW_VAL,
       TO_CHAR(TO_DATE(CURR, 'J'), 'DD-MON-YYYY') HIGH_VAL,
       CURR - PREV WIDTH,
       ROUND((182700 / 11) / (CURR - PREV), 4) HEIGHT
FROM   (SELECT ENDPOINT_VALUE CURR,
               LAG(ENDPOINT_VALUE, 1) OVER(ORDER BY ENDPOINT_NUMBER) PREV
        FROM   USER_TAB_HISTOGRAMS
        WHERE  TABLE_NAME = 'T1'
        AND    COLUMN_NAME = 'DATE_CLOSED')
WHERE  PREV IS NOT NULL
ORDER  BY CURR
;

    BUCKET LOW_VAL                            HIGH_VAL                                WIDTH     HEIGHT
---------- ---------------------------------- ---------------------------------- ---------- ----------
         1 01-JAN-2000                        15-JUN-2000                               166   100.0548
         2 15-JUN-2000                        28-NOV-2000                               166   100.0548
         3 28-NOV-2000                        13-MAY-2001                               166   100.0548
         4 13-MAY-2001                        27-OCT-2001                               167    99.4556
         5 27-OCT-2001                        11-APR-2002                               166   100.0548
         6 11-APR-2002                        24-SEP-2002                               166   100.0548
         7 24-SEP-2002                        09-MAR-2003                               166   100.0548
         8 09-MAR-2003                        23-AUG-2003                               167    99.4556
         9 23-AUG-2003                        05-FEB-2004                               166   100.0548
        10 05-FEB-2004                        20-JUL-2004                               166   100.0548
        11 20-JUL-2004                        31-DEC-4000                            729188      .0228

-- 8. T1 테이블 다시 실행계획 검토
EXPLAIN PLAN FOR
SELECT *
FROM   T1
WHERE  DATE_CLOSED BETWEEN TO_DATE('01-JAN-2003', 'DD-MON-YYYY')
                   AND     TO_DATE('31-DEC-2003', 'DD-MON-YYYY')
;

@XPLAN
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 36320 |   283K|    89   (5)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T1   | 36320 |   283K|    89   (5)| 00:00:02 |
--------------------------------------------------------------------------

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

   1 - filter("DATE_CLOSED">=TO_DATE('2003-01-01 00:00:00', 'yyyy-mm-dd
              hh24:mi:ss') AND "DATE_CLOSED"<=TO_DATE('2003-12-31 00:00:00',
              'yyyy-mm-dd hh24:mi:ss'))

"코어 오라클 데이터베이스 스터디모임" 에서 2009년에 "비용기반의 오라클 원리 " 도서를 스터디하면서 정리한 내용 입니다.

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

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

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

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