by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

7. 히스토그램




목차

I. 일반적인 히스토그램

II. 히스토그램 내부 생성 로직

III. 히스토그램과 바인드 변수

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

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

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

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개의 값 사이에서 특정 높이를 지정하여 분포도를 인식하는 방법이다.

II. 히스토그램 내부 생성 로직

  • Oracle이 Frequency, Height-Balance 히스토그램을 생성할 때 어떤 SQL을 수행하여 통계정보에 반영하는지 10046 Trace를 통혜 살펴본다.

1. Frequency Histogram


-- 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. 통계정보 생성(Frequency Histogram 생성)
-- 10046 Trace Enable
SELECT S.SID,
       S.SERIAL#,
       S.AUDSID,
       S.PROCESS CLIENT,
       P.SPID SERVER
FROM   V$PROCESS P,
       V$SESSION S
WHERE  P.ADDR = S.PADDR
AND    S.AUDSID = USERENV('SESSIONID');

ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED;

ALTER SESSION SET SQL_TRACE=TRUE;

SET TERMOUT OFF;
/

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';

-- 통계정보 생성
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(USER,
                                  'XSOFT_T',
                                  METHOD_OPT => 'FOR ALL COLUMNS SIZE SKEWONLY');
END;
/

-- 트레이스 비활성화
ALTER SESSION SET SQL_TRACE=FALSE;

EXIT;

-- 3. 10046 Trace 내용
SELECT SUBSTRB(DUMP(VAL, 16, 0, 32), 1, 120) EP,
       CNT
FROM   (SELECT /*+ NO_PARALLEL(T)
                   NO_PARALLEL_INDEX(T)
                   DBMS_STATS
                   CURSOR_SHARING_EXACT USE_WEAK_NAME_RESL
                   DYNAMIC_SAMPLING(0)
                   NO_MONITORING  */
               FLAG VAL,
               COUNT(*) CNT
        FROM   XSOFT_T T
        WHERE  FLAG IS NOT NULL
        GROUP  BY FLAG)
ORDER  BY VAL
;

-- Output Data
EP                    CNT
-------------------- ----
Typ=2 Len=2: c1,2       9
Typ=2 Len=2: c1,3      90
Typ=2 Len=2: c1,4     900
;

-- 10046 Trace Data
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        4      0.00       0.00          0          4          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.01       0.00          0          4          0           3

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 44     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      3  SORT GROUP BY (cr=4 pr=0 pw=0 time=1095 us)
    999   TABLE ACCESS FULL XSOFT_T (cr=4 pr=0 pw=0 time=83 us)
;

2. Height-Balance Histogram


-- 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. 랜덤 함수 수행
EXECUTE DBMS_RANDOM.SEED(0)

-- 2. 테이블 생성
DROP TABLE T1;

DROP TABLE KILO_ROW;

CREATE TABLE KILO_ROW AS
SELECT ROWNUM ID
FROM   ALL_OBJECTS
WHERE  ROWNUM <= 1000
;

CREATE TABLE T1
AS
WITH KILO_ROW AS (SELECT /*+ MATERIALIZE */
                         ROWNUM
                  FROM   ALL_OBJECTS
                  WHERE  ROWNUM <= 1000)
SELECT  TRUNC(7000 * DBMS_RANDOM.NORMAL)	NORMAL
FROM    KILO_ROW	K1,
	    KILO_ROW	K2
WHERE   ROWNUM <= 1000000
;

-- 3. T1 조회
SELECT MIN(NORMAL), MAX(NORMAL)
FROM   T1
;

MIN(NORMAL) MAX(NORMAL)
----------- -----------
     -32003       34660
;

SELECT COUNT(*)
FROM   (SELECT DISTINCT NORMAL FROM T1)
;

  COUNT(*)
----------
     42117
;

-- 4. 통계정보 생성(Height-Balance Histogram 생성)
-- 10046 Trace Enable
SELECT S.SID,
       S.SERIAL#,
       S.AUDSID,
       S.PROCESS CLIENT,
       P.SPID SERVER
FROM   V$PROCESS P,
       V$SESSION S
WHERE  P.ADDR = S.PADDR
AND    S.AUDSID = USERENV('SESSIONID');

ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED;

ALTER SESSION SET SQL_TRACE=TRUE;

SET TERMOUT OFF;
/

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';

-- 통계정보 생성
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(USER,
                                  'T1',
                                  METHOD_OPT => 'FOR ALL COLUMNS SIZE 10');
END;
/

-- 트레이스 비활성화
ALTER SESSION SET SQL_TRACE=FALSE;

EXIT;

-- 5. 통계정보 확인
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('T1')
;

TABLE_NAME COLUMN_NAM NUM_DISTINCT NUM_NULLS    DENSITY LOW_VALUE  HIGH_VALUE HISTOGRAM
---------- ---------- ------------ --------- ---------- ---------- ---------- ---------------
T1         NORMAL            27797         0 .000054843 3C63194B66 C3035562   HEIGHT BALANCED
;

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('T1')
;

TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE
---------- ---------- --------------- -------------- ---------------------
T1         NORMAL                   0         -27626
T1         NORMAL                   1          -8930
T1         NORMAL                   2          -5901
T1         NORMAL                   3          -3708
T1         NORMAL                   4          -1775
T1         NORMAL                   5             17
T1         NORMAL                   6           1809
T1         NORMAL                   7           3674
T1         NORMAL                   8           5902
T1         NORMAL                   9           9012
T1         NORMAL                  10          28497
;

-- 6. LAG() 함수를 이용하여 Height-Balance Histogram 분포도 확인
SELECT ROWNUM TENTH,
       PREV LOW_VAL,
       CURR HIGH_VAL,
       CURR - PREV WIDTH,
       ROUND(100000 / (CURR - PREV), 2) 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 = 'NORMAL')
WHERE  PREV IS NOT NULL
ORDER  BY CURR
;

     TENTH    LOW_VAL   HIGH_VAL      WIDTH     HEIGHT
---------- ---------- ---------- ---------- ----------
         1     -27626      -8930      18696       5.35
         2      -8930      -5901       3029      33.01
         3      -5901      -3708       2193       45.6
         4      -3708      -1775       1933      51.73
         5      -1775         17       1792       55.8
         6         17       1809       1792       55.8
         7       1809       3674       1865      53.62
         8       3674       5902       2228      44.88
         9       5902       9012       3110      32.15
        10       9012      28497      19485       5.13


-- 7_1. NTILE() 함수를 이용하여 Height-Balance Histogram 분포도 확인
-- NTILE() 함수를 이용하여 10개의 그룹으로 나누고 이들을 100,000으로 나누어 높이를 일정하게 유지함
SELECT TENTH TENTH,
       MIN(NORMAL) LOW_VAL,
       MAX(NORMAL) HIGH_VAL,
       MAX(NORMAL) - MIN(NORMAL) WIDTH,
       ROUND(100000 / (MAX(NORMAL) - MIN(NORMAL)), 2) HEIGHT
FROM   (SELECT NORMAL,
               NTILE(10) OVER(ORDER BY NORMAL) TENTH
        FROM   T1)
GROUP  BY TENTH
ORDER  BY TENTH
;

     TENTH    LOW_VAL   HIGH_VAL      WIDTH     HEIGHT
---------- ---------- ---------- ---------- ----------
         1     -32003      -8966      23037       4.34
         2      -8966      -5883       3083      32.44
         3      -5883      -3659       2224      44.96
         4      -3659      -1761       1898      52.69
         5      -1761         17       1778      56.24
         6         17       1792       1775      56.34
         7       1792       3678       1886      53.02
         8       3678       5897       2219      45.07
         9       5897       8974       3077       32.5
        10       8974      34660      25686       3.89
;

-- 7_2. NTILE() 함수를 이용하여 Height-Balance Histogram 분포도 그래프로 확인
SELECT TENTH,
       LPAD('*', ROUND(100000 / (MAX(NORMAL) - MIN(NORMAL)), 0), '*') HEIGHT
FROM   (SELECT NORMAL,
               NTILE(10) OVER(ORDER BY NORMAL) TENTH
        FROM   T1)
GROUP  BY TENTH
ORDER  BY TENTH
;

TENTH HEIGHT
----- ------------------------------------------------------------
    1 ****
    2 ********************************
    3 *********************************************
    4 *****************************************************
    5 ********************************************************
    6 ********************************************************
    7 *****************************************************
    8 *********************************************
    9 ********************************
   10 ****

-- 8. 10046 Trace 내용(10.2.0.3 기준)
-- 10046 Trace에서 Height-Balance Histogram 값을 구할때 NTILE() 함수를 이용
SELECT MIN(MINBKT),
       MAXBKT,
       SUBSTRB(DUMP(MIN(VAL), 16, 0, 32), 1, 120) MINVAL,
       SUBSTRB(DUMP(MAX(VAL), 16, 0, 32), 1, 120) MAXVAL,
       SUM(REP) SUMREP,
       SUM(REPSQ) SUMREPSQ,
       MAX(REP) MAXREP,
       COUNT(*) BKTNDV,
       SUM(CASE WHEN REP = 1 THEN 1
                ELSE 0
                END) UNQREP
FROM   (SELECT VAL,
               MIN(BKT) MINBKT,
               MAX(BKT) MAXBKT,
               COUNT(VAL) REP,
               COUNT(VAL) * COUNT(VAL) REPSQ
        FROM   (SELECT /*+ NO_PARALLEL(T)
                           NO_PARALLEL_INDEX(T)
                           DBMS_STATS CURSOR_SHARING_EXACT
                           USE_WEAK_NAME_RESL
                           DYNAMIC_SAMPLING(0)
                           NO_MONITORING */
                       NORMAL VAL,
                       NTILE(10) OVER(ORDER BY NORMAL) BKT
                FROM   T1 T
                WHERE  NORMAL IS NOT NULL)
        GROUP  BY VAL)
GROUP  BY MAXBKT
ORDER  BY MAXBKT
;

-- Output Data
MINBKT MAXBKT MINVAL                      MAXVAL                   SUMREP   SUMREPSQ MAXREP BKTNDV UNQREP
------ ------ --------------------------- ------------------------ ------ ---------- ------ ------ ------
     1      1 Typ=2 Len=5: 3c,62,51,62,66 Typ=2 Len=4: 3d,c,22,66   99987    1508195     42  12104   2334
     1      2 Typ=2 Len=4: 3d,c,23,66     Typ=2 Len=4: 3d,2b,11,66  99981    3405721     65   3083      0
     2      3 Typ=2 Len=4: 3d,2b,12,66    Typ=2 Len=4: 3d,41,29,66 100029    4618195     77   2224      0
     3      4 Typ=2 Len=4: 3d,41,2a,66    Typ=2 Len=4: 3d,54,27,66  99968    5366210     79   1898      0
     4      5 Typ=2 Len=4: 3d,54,28,66    Typ=2 Len=2: c1,11       100017    5726127    109   1778      0
     5      6 Typ=2 Len=2: c1,12          Typ=2 Len=3: c2,12,5c    100010    5733940     82   1775      0
     6      7 Typ=2 Len=3: c2,12,5d       Typ=2 Len=3: c2,25,4e     99978    5405886     78   1886      0
     7      8 Typ=2 Len=3: c2,25,4f       Typ=2 Len=3: c2,3b,61    100021    4629505     71   2219      0
     8      9 Typ=2 Len=3: c2,3b,62       Typ=2 Len=3: c2,5a,4a     99999    3406427     64   3077      0
     9     10 Typ=2 Len=3: c2,5a,4b       Typ=2 Len=4: c3,4,2f,3d  100010    1508894     40  12073   2311

-- 10046 Trace Data
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      5.74       5.60          0       1535          0          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      5.74       5.61          0       1535          0          10

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 44     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
     10  SORT GROUP BY (cr=1535 pr=0 pw=0 time=5605496 us)
  42117   VIEW  (cr=1535 pr=0 pw=0 time=5475345 us)
  42117    SORT GROUP BY NOSORT (cr=1535 pr=0 pw=0 time=5391092 us)
1000000     VIEW  (cr=1535 pr=0 pw=0 time=4516804 us)
1000000      WINDOW SORT (cr=1535 pr=0 pw=0 time=3516523 us)
1000000       TABLE ACCESS FULL T1 (cr=1535 pr=0 pw=0 time=90 us)

III. 히스토그램과 바인드 변수

  • 히스토그램이 생성된 컬럼에 바인드 변수로 조건이 들어갈 경우 USER_TAB_HISTOGRAMS의 값을 이용하지
    못하고 NDV값을 이용한다.
  • 하지만 바인드 값을 사용하였다 하더라도 히스토그램이 영향을 아주 못주는 것은 아니다.
    즉, 바인드 변수 Peeking으로 인해 값을 인지할 수 있고 또한 히스토그램을 생성하게 되면 NDV 값이
    영향을 받아 변경되므로 히스토그램과 바인드 변수가 아예 상관이 없는 것은 아니다.

1. 바인드 변수 Peeking

  • 바인드 변수 Peeking이란 최초의 cursor에 사용된 바인드 변수의 상수값을 인지하고 그 이후 같은 cursor가
    들어올 경우 처음에 인지한 바인드 변수의 상수값을 가지고 해석을 하는것을 말한다.
  • 하지만 히스토그램이 생성된 컬럼에 바인드 변수가 사용되었고 분포도가 낮은 값이 들어올 경우로
    계산을 했다가 분포도가 높은 값이 그 이후에 들어온다면 정확한 값을 인식할 수 없다.
  • 테스트 내용 : 하지만 바인드 Peeking을 활성화해도 별 차이 없음 .;
    
    -- 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 SKEWONLY');
    END;
    /
      
    -- 3. bind peeking 활성화
    -- 1) 바인드 변수 선언
    VAR B1 NUMBER;
    EXEC :B1 := 3;
    
    -- 2) STATISTICS_LEVEL 활성화
    ALTER SESSION SET STATISTICS_LEVEL = ALL ;
    
    -- 3) 분포도가 낮은 값 3으로 먼저 Peeking 
    SELECT /*+ OPT_PARAM('_OPTIM_PEEK_USER_BINDS' 'TRUE') */
           *
    FROM   XSOFT_T
    WHERE  FLAG = :B1
    ;
    
    -- 4) V$SQL에서 SQL_ID 확인 
    SELECT SUBSTR(SQL_TEXT, 1, 30),
           SQL_ID,
           CHILD_NUMBER
    FROM   V$SQL
    WHERE  UPPER(SQL_TEXT) LIKE '%XSOFT_T%'
    ORDER  BY FIRST_LOAD_TIME DESC
    ;
    
    
    SUBSTR(SQL_TEXT,1,30)          SQL_ID        CHILD_NUMBER
    ------------------------------ ------------- ------------
    SELECT SUBSTR(SQL_TEXT, 1, 30) 56bqkx2bgmvfc            0
    SELECT SUBSTR(SQL_TEXT, 1, 30) 46dmyzrsppa3a            0
    SELECT SUBSTR(SQL_TEXT, 1, 30) 46dmyzrsppa3a            1
    SELECT SUBSTR(SQL_TEXT, 1, 20) b3qmfm55u7trs            0
    SELECT SUBSTR(SQL_TEXT, 1, 20) atu0kk85um7v1            0
    SELECT SQL_TEXT,        SQL_FU 5hx6usvks1r75            0
    SELECT SQL_TEXT,        SQL_FU 5hx6usvks1r75            1
    SELECT /*+ OPT_PARAM('_OPTIM_P 35x0sx3dxtt5r            0
    EXPLAIN PLAN FOR SELECT /*+ OP bfa0u88cz3n78            0
    select /*+ no_parallel(t) no_p 8zw3fzkk0gvwc            0
    select substrb(dump(val,16,0,3 4agz0p1vt39fw            0
     select /*+ no_parallel(t) no_ 2gckc3xgdabky            0
    BEGIN     DBMS_STATS.GATHER_TA 4qt4pb05pwh74            0
    SELECT  topology   FROM  SDO_T 997gattrj5nag            0
    */
    
    -- 5) Runtime Plan 확인
    SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR('35x0sx3dxtt5r',NULL,'ADVANCED ALLSTATS COST LAST'));
    
    ----------------------------------------------------------------------------------------------------------------------- 
    | Id  | Operation         | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | 
    ----------------------------------------------------------------------------------------------------------------------- 
    |*  1 |  TABLE ACCESS FULL| XSOFT_T |      1 |    333 |   999 |     3   (0)| 00:00:01 |    900 |00:00:00.01 |      64 | 
    ----------------------------------------------------------------------------------------------------------------------- 
                                                                                                                            
    Query Block Name / Object Alias (identified by operation id):                                                           
    -------------------------------------------------------------                                                           
                                                                                                                            
       1 - SEL$1 / XSOFT_T@SEL$1                                                                                            
                                                                                                                            
    Outline Data                                                                                                            
    -------------                                                                                                           
                                                                                                                            
      /*+                                                                                                                   
          BEGIN_OUTLINE_DATA                                                                                                
          IGNORE_OPTIM_EMBEDDED_HINTS                                                                                       
          OPTIMIZER_FEATURES_ENABLE('10.2.0.3')                                                                             
          OPT_PARAM('_b_tree_bitmap_plans' 'false')                                                                         
          OPT_PARAM('_fast_full_scan_enabled' 'false')                                                                      
          ALL_ROWS                                                                                                          
          OUTLINE_LEAF(@"SEL$1")                                                                                            
          FULL(@"SEL$1" "XSOFT_T"@"SEL$1")                                                                                  
          END_OUTLINE_DATA                                                                                                  
      */                                                                                                                                
    ;
    
    -- 6) 분포도가 높은 값 1 사용
    VAR B1 NUMBER;
    EXEC :B1 := 1;
    
    SELECT /*+ OPT_PARAM('_OPTIM_PEEK_USER_BINDS' 'TRUE') */
           *
    FROM   XSOFT_T
    WHERE  FLAG = :B1
    ;
    
    -- 7) V$SQL에서 SQL_ID 확인 
    SELECT SUBSTR(SQL_TEXT, 1, 30),
           SQL_ID,
           CHILD_NUMBER
    FROM   V$SQL
    WHERE  UPPER(SQL_TEXT) LIKE '%XSOFT_T%'
    ORDER  BY FIRST_LOAD_TIME DESC
    ;
    
    SUBSTR(SQL_TEXT,1,30)          SQL_ID        CHILD_NUMBER
    ------------------------------ ------------- ------------
    SELECT SUBSTR(SQL_TEXT, 1, 30) 56bqkx2bgmvfc            0
    SELECT SUBSTR(SQL_TEXT, 1, 30) 46dmyzrsppa3a            0
    SELECT SUBSTR(SQL_TEXT, 1, 30) 46dmyzrsppa3a            1
    SELECT SUBSTR(SQL_TEXT, 1, 20) b3qmfm55u7trs            0
    SELECT SUBSTR(SQL_TEXT, 1, 20) atu0kk85um7v1            0
    SELECT SQL_TEXT,        SQL_FU 5hx6usvks1r75            0
    SELECT SQL_TEXT,        SQL_FU 5hx6usvks1r75            1
    SELECT /*+ OPT_PARAM('_OPTIM_P 35x0sx3dxtt5r            0
    EXPLAIN PLAN FOR SELECT /*+ OP bfa0u88cz3n78            0
    select /*+ no_parallel(t) no_p 8zw3fzkk0gvwc            0
    select substrb(dump(val,16,0,3 4agz0p1vt39fw            0
     select /*+ no_parallel(t) no_ 2gckc3xgdabky            0
    BEGIN     DBMS_STATS.GATHER_TA 4qt4pb05pwh74            0
    SELECT  topology   FROM  SDO_T 997gattrj5nag            0
    */
    
    -- 8) Runtime Plan 확인
    SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR('35x0sx3dxtt5r',NULL,'ADVANCED ALLSTATS COST LAST'));
    
    -----------------------------------------------------------------------------------------------------------------------
    | Id  | Operation         | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
    -----------------------------------------------------------------------------------------------------------------------
    |*  1 |  TABLE ACCESS FULL| XSOFT_T |      1 |    333 |   999 |     3   (0)| 00:00:01 |      9 |00:00:00.01 |       5 |
    -----------------------------------------------------------------------------------------------------------------------
                                                                                                                           
    Query Block Name / Object Alias (identified by operation id):                                                          
    -------------------------------------------------------------                                                          
                                                                                                                           
       1 - SEL$1 / XSOFT_T@SEL$1                                                                                           
                                                                                                                           
    Outline Data                                                                                                           
    -------------                                                                                                          
                                                                                                                           
      /*+                                                                                                                  
          BEGIN_OUTLINE_DATA                                                                                               
          IGNORE_OPTIM_EMBEDDED_HINTS                                                                                      
          OPTIMIZER_FEATURES_ENABLE('10.2.0.3')                                                                            
          OPT_PARAM('_b_tree_bitmap_plans' 'false')                                                                        
          OPT_PARAM('_fast_full_scan_enabled' 'false')                                                                     
          ALL_ROWS                                                                                                         
          OUTLINE_LEAF(@"SEL$1")                                                                                           
          FULL(@"SEL$1" "XSOFT_T"@"SEL$1")                                                                                 
          END_OUTLINE_DATA                                                                                                 
      */                                                                                                                   
    
    -- 9) STATISTICS_LEVEL 비활성화
    ALTER SESSION SET STATISTICS_LEVEL = TYPICAL ;
    

2. 커서 공유

  • 오라클은 literal로 인한 Harde Parse 문제를 해결하기 위해 cursor를 공유할 수 있도록 cursor_sharing 파라미터를 제공한다.
    cursor_sharing
    • FORCE
      • SQL내의 리터럴 상수를 시스템이 생성한 바인드 변수로 변경한다.
      • 그리고 이렇게 생성된 공유 가능한 커서가 있는지 확인한다.
      • 대부분, 오라클은 리터럴 값을 :SYS_B_0과 같은 이름의 바인드 변수로 변경한다.
      • Forces statements that may differ in some literals, but are otherwise identical,
        to share a cursor, unless the literals affect the meaning of the statement.
    • SIMILAR
      • 오라클은 먼저 리터럴 상수를 바인드 변수로 변경하고 바인드 변수를 Peeking할지 결정하기 때문에
        별도로 최적화하는 것이 좋다고 생각한 문장에 대해서 single parse call을 최적화 시킬 수 있음
      • Causes statements that may differ in some literals, but are otherwise identical, to share a cursor,
        unless the literals affect either the meaning of the statement or the degree to which the plan is optimized.
    • EXACT
      • 같은 문장에 대해서만 cursor share를 함.
      • Only allows statements with identical text to share the same cursor.
  • 즉, 히스토그램을 생성하고 나서 cursor_sharing 파라미터를 force 또는 simalar로 설정할 경우 리터럴값을 바인드 변경하기 때문에 기존의 실행계획에 영향을 줄 수 있다.
  • 그러므로 이 파라미터를 사용하고자 한다면, 반드시 필요한 것 이상의 히스토그램을 생성하지 말아야 하며, 그렇지 않을 경우 해결할 수 없는 더 많은 성능 문제가 발생한다.

3. 오라클은 언제 히스토그램을 무시하는가?

1) 히스토그램과 조인

  • 오라클이 히스토그램을 활용할때는 확실이 상수일경우에만 인식을 하고 조인으로 인해 바인드변수로 인식될때는 사용을 하지 못한다.
    SELECT T1.V1,
           T2.V1
    FROM   T1,
           T2
    WHERE  T1.N2 = 99
    AND    T1.N1 = T2.N1
    ;
    
    이 SQL에서 T1.N1, T2.N1 컬럼에 히스토그램이 있더라도 실제로 값이 들어오는 곳은 히스토그램이 없는 T1.N2이기 때문에 N1 값은 바인드 변수로 인식되어 히스토그램을 사용하지 못한다.              
    

2) 히스토그램과 분산 쿼리

  • 오라클은 DB-Link를 사용할때도 히스토그램이 있더라도 그것을 사용하지 않는다.

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

1. 데이터 생성


-- 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. 테이블 T1 생성
DROP TABLE T1;
DROP TABLE GENERATOR;

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

CREATE TABLE T1 (SKEW NOT NULL,	
	             PADDING)
AS
SELECT /*+ ORDERED USE_NL(V2) */
	   V1.ID,
	   RPAD('X',400)
FROM   GENERATOR	V1,
	   GENERATOR	V2
WHERE  V1.ID <= 80
AND	   V2.ID <= 80
AND	   V2.ID <= V1.ID
ORDER BY  V2.ID,V1.ID
;

CREATE INDEX T1_I1 ON T1(SKEW);

-- 2. 테이블 T1 데이터 조회
SELECT COUNT(*)
FROM   T1
;

  COUNT(*)
----------
      3240

SELECT SKEW,
       COUNT(*)
FROM   T1
GROUP  BY SKEW
ORDER  BY SKEW
;

      SKEW   COUNT(*)   
---------- ----------   
         1          1   
         2          2   
.....................
        79         79   
        80         80   

-- 3. 테이블 T1 Height-Balance Histogram 포함된 통계정보 생성
-- Height-Balance Histogram 생성 시 Rows가 80개인 생태에서 Bucket을 120개 요청했지만 결과는 80개 로우만 생성
BEGIN
	DBMS_STATS.GATHER_TABLE_STATS(USER,
                            	  'T1',
                            	  CASCADE => TRUE,
                            	  ESTIMATE_PERCENT => NULL,
                            	  METHOD_OPT => 'FOR ALL COLUMNS SIZE 120');
END;
/

SELECT ROWNUM,
       ENDPOINT_NUMBER,
       ENDPOINT_VALUE
FROM   USER_TAB_HISTOGRAMS
WHERE  COLUMN_NAME = 'SKEW'
AND    TABLE_NAME  = 'T1'
ORDER  BY ENDPOINT_NUMBER
;

ROWNUM ENDPOINT_NUMBER ENDPOINT_VALUE  
------ --------------- --------------  
     1               1              1  
     2               3              2  
.....................................
    79            3160             79  
    80            3240             80  
;

-- 4. USER_TAB_HISTOGRAMS 조회하여 분포도 확인
SELECT ENDPOINT_VALUE ROW_VALUE,
       CURR_NUM - NVL(PREV_NUM, 0) ROW_COUNT
FROM   (SELECT ENDPOINT_VALUE,
               ENDPOINT_NUMBER CURR_NUM,
               LAG(ENDPOINT_NUMBER, 1) OVER(ORDER BY ENDPOINT_NUMBER) PREV_NUM
        FROM   USER_TAB_HISTOGRAMS
        WHERE  COLUMN_NAME = 'SKEW'
        AND    TABLE_NAME  = 'T1')
ORDER  BY ENDPOINT_VALUE
;

 ROW_VALUE  ROW_COUNT  
---------- ----------  
         1          1  
         2          2  
.....................
        79         79  
        80         80  
;

2. Frequency Histogram을 이용한 테스트

조건절 설명 CBO 사람
SKEW = 40 상수 값 40 40
SKEW = 40.5 존재하기는 않지만 범위에 포함 1 0
SKEW BETWEEN 21 AND 24 범위 내에 포함되는 BETWEEN 조건 90 90
SKEW BETWEEN 20.5 AND 24.5 범위 내에 포함되는 BETWEEN 조건 90 90
SKEW BETWEEN 1 AND 2 시작점을 포함한 BETWEEN 조건 3 3
SKEW BETWEEN 79 AND 80 종료점을 포함한 BETWEEN 조건 159 159
SKEW > 4 AND SKEW < 8 ">" AND "<" 범위 조건 18 18
SKEW = -10 최소값 미만 1 0
SKEW = 100 최대값 미만 1 0
SKEW BETWEEN -5 AND -3 최소값 미만의 범위 조건 1 0
SKEW BETWEEN 92 AND 94 최대값 미만의 범위 조건 1 0
SKEW BETWEEN 79 AND 82 경계 값에 걸치는 범위 조건 159 159
SKEW = :B1 바인드 변수 41 ???
SKEW BETWEEN :B1 AND :B2 바인드 변수의 BETWEEN 조건 8 ???

-- 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. SKEW = 40
EXPLAIN PLAN FOR
SELECT	* 
FROM	T1
WHERE	SKEW = 40
;

@xplan
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    40 | 16160 |    56   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |    40 | 16160 |    56   (0)| 00:00:01 |
--------------------------------------------------------------------------
                                                                          
Predicate Information (identified by operation id):                       
---------------------------------------------------                       
                                                                          
   1 - filter("SKEW"=40)                                                  
;

-- 2. SKEW = 40.5 
EXPLAIN PLAN FOR
SELECT	* 
FROM	T1
WHERE	SKEW = 40.5
;

@xplan
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   404 |    56   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |   404 |    56   (0)| 00:00:01 |
--------------------------------------------------------------------------
                                                                          
Predicate Information (identified by operation id):                       
---------------------------------------------------                       
                                                                          
   1 - filter("SKEW"=40.5)                                                
;

-- 3. SKEW BETWEEN 21 AND 24
EXPLAIN PLAN FOR
SELECT	* 
FROM	T1
WHERE	SKEW BETWEEN 21 AND 24 
;

@xplan
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    90 | 36360 |    56   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |    90 | 36360 |    56   (0)| 00:00:01 |
--------------------------------------------------------------------------
                                                                          
Predicate Information (identified by operation id):                       
---------------------------------------------------                       
                                                                          
   1 - filter("SKEW"<=24 AND "SKEW">=21)                                  
;

-- 4. SKEW BETWEEN 20.5 AND 24.5
EXPLAIN PLAN FOR
SELECT	* 
FROM	T1
WHERE	SKEW BETWEEN 20.5 AND 24.5
;

@xplan
-------------------------------------------------------------------------- 
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
-------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT  |      |    90 | 36360 |    56   (0)| 00:00:01 | 
|*  1 |  TABLE ACCESS FULL| T1   |    90 | 36360 |    56   (0)| 00:00:01 | 
-------------------------------------------------------------------------- 
                                                                           
Predicate Information (identified by operation id):                        
---------------------------------------------------                        
                                                                           
   1 - filter("SKEW"<=24.5 AND "SKEW">=20.5)                               
;

-- 5. SKEW BETWEEN 1 AND 2 
EXPLAIN PLAN FOR
SELECT	* 
FROM	T1
WHERE	SKEW BETWEEN 1 AND 2
;

@xplan
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    90 | 36360 |    56   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |    90 | 36360 |    56   (0)| 00:00:01 |
--------------------------------------------------------------------------
                                                                          
Predicate Information (identified by operation id):                       
---------------------------------------------------                       
                                                                          
   1 - filter("SKEW"<=24.5 AND "SKEW">=20.5)                              
;

-- 6. SKEW BETWEEN 79 AND 80 
EXPLAIN PLAN FOR
SELECT	* 
FROM	T1
WHERE	SKEW BETWEEN 79 AND 80
;

@xplan
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   158 | 63832 |    56   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   158 | 63832 |    56   (0)| 00:00:01 |
--------------------------------------------------------------------------
                                                                          
Predicate Information (identified by operation id):                       
---------------------------------------------------                       
                                                                          
   1 - filter("SKEW">=79 AND "SKEW"<=80)                                  
;

-- 7. SKEW > 4 AND SKEW < 8 
EXPLAIN PLAN FOR
SELECT	* 
FROM	T1
WHERE	SKEW > 4 AND SKEW < 8
;

@xplan
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    18 |  7272 |    56   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |    18 |  7272 |    56   (0)| 00:00:01 |
--------------------------------------------------------------------------
                                                                          
Predicate Information (identified by operation id):                       
---------------------------------------------------                       
                                                                          
   1 - filter("SKEW"<8 AND "SKEW">4)                                      
;

-- 8. SKEW = -10
EXPLAIN PLAN FOR
SELECT	* 
FROM	T1
WHERE	SKEW = -10
;

@xplan
------------------------------------------------------------------------------------- 
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     | 
------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT            |       |     1 |   404 |     2   (0)| 00:00:01 | 
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |   404 |     2   (0)| 00:00:01 | 
|*  2 |   INDEX RANGE SCAN          | T1_I1 |     1 |       |     1   (0)| 00:00:01 | 
------------------------------------------------------------------------------------- 
                                                                                      
Predicate Information (identified by operation id):                                   
---------------------------------------------------                                   
                                                                                      
   2 - access("SKEW"=(-10))    
;
                                                          
-- 9. SKEW= 100 
EXPLAIN PLAN FOR
SELECT	* 
FROM	T1
WHERE	SKEW = 100
;

@xplan
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   404 |    56   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |   404 |    56   (0)| 00:00:01 |
--------------------------------------------------------------------------
                                                                          
Predicate Information (identified by operation id):                       
---------------------------------------------------                       
                                                                          
   1 - filter("SKEW"=100)                                                 
;

-- 10. SKEW BETWEEN -5 AND -3
EXPLAIN PLAN FOR
SELECT	* 
FROM	T1
WHERE	SKEW BETWEEN -5 AND -3
;

@xplan
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   404 |    56   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |   404 |    56   (0)| 00:00:01 |
--------------------------------------------------------------------------
                                                                          
Predicate Information (identified by operation id):                       
---------------------------------------------------                       
                                                                          
   1 - filter("SKEW"<=(-3) AND "SKEW">=(-5))                              
;

-- 11. SKEW BETWEEN 92 AND 94 
EXPLAIN PLAN FOR
SELECT	* 
FROM	T1
WHERE	SKEW BETWEEN 92 AND 94
;

@xplan
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   404 |    56   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |   404 |    56   (0)| 00:00:01 |
--------------------------------------------------------------------------
                                                                          
Predicate Information (identified by operation id):                       
---------------------------------------------------                       
                                                                          
   1 - filter("SKEW">=92 AND "SKEW"<=94)                                  
;

-- 12. SKEW BETWEEN 79 AND 82
EXPLAIN PLAN FOR
SELECT	* 
FROM	T1
WHERE	SKEW BETWEEN 79 AND 82
;

@xplan
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   158 | 63832 |    56   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   158 | 63832 |    56   (0)| 00:00:01 |
--------------------------------------------------------------------------
                                                                          
Predicate Information (identified by operation id):                       
---------------------------------------------------                       
                                                                          
   1 - filter("SKEW">=79 AND "SKEW"<=82)                                  
;

-- 13. SKEW = :B1
EXPLAIN PLAN FOR
SELECT	* 
FROM	T1
WHERE	SKEW = :B1
;

@xplan
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    41 | 16564 |    56   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |    41 | 16564 |    56   (0)| 00:00:01 |
--------------------------------------------------------------------------
                                                                          
Predicate Information (identified by operation id):                       
---------------------------------------------------                       
                                                                          
   1 - filter("SKEW"=TO_NUMBER(:B1))                                      
;

-- 14. SKEW BETWEEN :B1 AND :B2
EXPLAIN PLAN FOR
SELECT	* 
FROM	T1
WHERE	SKEW BETWEEN :B1 AND :B2
;

@xplan
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     8 |  3232 |    56   (0)| 00:00:01 |
|*  1 |  FILTER            |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |     8 |  3232 |    56   (0)| 00:00:01 |
---------------------------------------------------------------------------
                                                                           
Predicate Information (identified by operation id):                        
---------------------------------------------------                        
                                                                           
   1 - filter(TO_NUMBER(:B1)<=TO_NUMBER(:B2))                              
   2 - filter("SKEW">=TO_NUMBER(:B1) AND "SKEW"<=TO_NUMBER(:B2))           
;

일반적으로, 옵티마이저는 카디널리티 계산 과정에서 0이 전달되는 것을 허용하지 않는다.
계산된 카디널리티가 0이 될 때마다, 옵티마이저는 안전하게 1의 카디널리티를 사용한다.
하지만 조건절이 '1 = 0'처럼 논리적으로 모순된 경우에는 0 카디널리티가 허용된다.

3. Frequency Histogram 사용시 고려해야 할 사항

1) 통계정보 수집 시 버킷 개수는 254 이하를 사용해야 값을 놓칠 위험히 적다.
2) 데이터의 중요한 값이 변경되면, 히스토그램을 재생성해 주어야 한다.
3) Frequency Histogram은 바인드 변수를 사용하게 되면 1/NDV 값을 사용한다.
4) 적절한 히스토그램이 존재할 경우, CBO는 범위조건이 LOW/HIGH 값을 벗어날 때 더 잘 처리한다.

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

책을 통해...

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

1. 부적절한 데이터 타입

1) 만약 날짜타입의 컬럼을 이용하여 특정 기간의 데이터를 조회하였을 때 통계정보가 있을 경우
옵티마이저는 실 데이터와 근접한 예상 로우를 실행계획에서 보여준다. 하지만 이를 VARCAHR2
타입이나 NUMBER 타입으로 동일한 의미를 부여한 뒤 조회하였을 경우 옵티마이저는 정확한
계산을 할 수 없다.
2) 그 이유는 날짜타입일 경우 2009/12/31 ~ 2010/00/01 사이에 하루만 있다는 것을 알 수 있지만
'20091231 ~ 20100001'와 같은 VARCHAR2 타입일 경우 이 사이에 '20091232, 20091233, ...'
같은 값이 있을 것으로 예상하기 때문에 정확한 값을 추측할 수 없다.
3) 그러므로 가급적 사용 용도에 맞는 데이터타입을 설정하여 사용해야 하고 만약 그럴 수 없다면
히스토그램을 생성하여 '20091231 ~ 20100001' 사이에 데이터가 거의 없다는 정보를 옵티마이저에게
제공해야 한다.
4) 아래는 이에 대해 테스트 한 내용니다.

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'))                                     

문서에 대하여

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.