비용기반의 오라클 원리 (2009년)
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
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.